Isticmaalka liiska hoos u dhaca ee Excel waxay si weyn u dhimi kartaa wakhtiga ay qaadan karto in xogta lagu geliyo xaashida xaashida^{(enter data into a spreadsheet)} . Mahadsanid, aad bay u fududahay in la sameeyo liiska hoos u dhaca ee Excel .

Waxaa jira dhowr habab oo tan lagu sameeyo, min fudud ilaa kuwa sare. Waxaad ku baran doontaa si kasta oo loo sameeyo maqaalkan.

Ku samee Liiska Hoos^{(Drop Down List)} -u-dhaca ee Excel : Habka^(Method) Fudud

Habka ugu fudud ee liiska hoos u dhaca ee Excel loogu samayn karo waa adiga oo liiska gacanta ku gala. Tani waxay ku habboon tahay xaaladaha aad kaliya ugu baahan tahay liiska hoos u dhaca ee hal unug oo aadan u baahnayn inaad si joogto ah u cusbooneysiiso.

Tusaale ahaan, haddii aad u dirayso dadka fayl meesha aad rabto inay buuxiyaan meelaha qaarkood, oo hal goob uu leeyahay liis fudud oo xulasho ah, habkani waa mid ku habboon.

Si aad u abuurto liiska hoos u dhaca adiga oo isticmaalaya habka fudud:

1. Dooro unugga ama unugyada meesha aad rabto inaad ka abuurto liiskaaga.

2. Ka dooro xogta^(Data) liiska, oo dooro Data Validation ee ribbon.

3. Tani waxay furi doontaa daaqada Xaqiijinta Xogta . ^{(Data Validation)}Dooro liiska hoos-u-dhaca ee Oggolow^(Allow) oo dooro Liiska^(List) .

4. Tani waxay kuu ogolaanaysaa inaad gacanta ku qorto shayga aad ka rabto liiskaaga. Kaliya^(Just) ku qor shay kasta goobta Isha^(Source) iyadoo shay kasta ay ku kala soocan yihiin hakad.

5. Dooro OK . Hadda waxaad arki doontaa in unugga aad dooratay uu leeyahay fallaadho hoos u dhac ah oo ku ag taal. Markaad doorato falaartan, waxaad arki doontaa dhammaan shayada aad qortay ee liiskan ku jira.

Iyadoo tani ay tahay habka ugu dhaqsaha badan ee lagu abuuro liiska hoos u dhaca ee Excel , sidoo kale waa tan ugu adag in la ilaaliyo. Wax ka beddelka liisku wuxuu u baahan yahay dib ugu noqoshada dejinta ansaxinta iyo cusboonaysiinta goobta Isha^(Source) .

Haddii aad abuurto liisaska hoos u dhaca badan ee noocan oo kale ah gudaha Excel , waxay qaadan kartaa shaqo badan oo wax ka beddelka kuwan. Tani waa sababta ay muhiim u tahay in kaliya loo isticmaalo habkan unugyo keli ah, iyo liisaska aanad filaynin inay isbeddelaan.

Ku samee liiska hoos^{(Drop Down List)} u dhaca ee Excel : Isticmaalka Range

Haddii aad rabto xal dabacsanaan badan, waxaad ka soo jiidi kartaa walxaha liiskaaga unugyo kale oo badan^{(a range of other cells)} oo Excel ah.

Si tan loo sameeyo:

1. Marka hore^(First) , ku tax dhammaan shayada aad ka rabto liiskaaga khaan kasta oo unugyo ah. Waxaad kuwan geli kartaa xaashida xaashida ee hadda jirta, ama xaashi kasta oo kale.

2. Ku celi habka kor ku xusan si aad u doorato unugga oo aad bilawdo daaqada Xaqiijinta Xogta . ^{(Data Validation)}Deji goobta Oggolow^(Allow) si aad liis gareeyso^(List) . Markan, intii aad wax ku qori lahayd goobta Isha^(Source) , dooro astaanta fallaadha kor ee dhanka midig ee goobtan. Tani waa xulashada kala duwanaansho ee kuu ogolaanaya inaad doorato goobta aad rabto inaad ka soo jiidato liiskaaga.

3. Waxaad arki doontaa daaqadda Xaqiijinta Xogta^{(Data Validation)} oo burbursan si aad u aragto dhammaan xaashida. Jiirka jiirka hoos u jiid dhammaan unugyada kala duwan ee ay ku jiraan dhammaan shayada liiska aad rabto inaad ku darto. Markaad tan sameyso, dooro astaanta fallaadha hoose ee midig ee goobta xulashada. Tani waxay balaadhin doontaa daaqada Xaqiijinta xogta mar kale.^{(Data Validation)}

4. Waxaad arki doontaa kala duwanaanshaha aad dooratay oo hadda ka muuqda goobta Isha^(Source) . Kaliya^(Just) dooro OK si aad u aqbasho goobahan.

5. Hadda, marka aad doorato fallaadha hoos u dhaca ee midigta ee unugga aad u habaysay liiska hoos-u-dhaca, waxaad arki doontaa dhammaan walxaha lagu soo daray inta aad hadda dooratay.

Waxa ugu fiican ee habkan ku saabsan waa inaad bedeli karto mid ka mid ah shayada liiskaas ku jira oo kaliya adigoo wax ka beddelaya unug kasta oo ka mid ah tirada. Isbeddel kasta oo aad halkaas ku sameyso ayaa cusbooneysiin doona liis kasta oo hoos u dhac kasta oo aad abuurtay halka aad u dooratay xadkan inuu yahay isha.

Habkani waxa uu ku fiican yahay marka aad rabto in aad qaabaynayso unugyo badan oo^{(format a lot of cells)} hoos u dhaca adiga oo isticmaalaya liis isku mid ah. Waxa aad ka heli kartaa waxa ku jira hal nooc oo koontaroolaya shayada liisaskaas oo dhan, dhib malaha inta ay leegtahay.

Ku Darida Shayada Liiskaaga

Waxa intaa dheer in aad beddesho walxaha kala duwan si aad u cusboonaysiiso liisaskaada, waxa kale oo aad ku dari kartaa shay cusub. Ma awoodi doontid inaad shay ku darto dhammaadka kala duwanaanta, sababtoo ah xulashada kala duwanaantaada waxay xaddidan tahay unugyada ugu horreeya iyo kan ugu dambeeya ee aad dooratay.

Taa beddelkeeda, waxaad u baahan doontaa inaad geliso gelid cusub meel dhexda u dhaxaysa. Excel ayaa si firfircooni leh u cusboonaysiin doonta xulashada kala duwanaantaada ee dejinta xogta si loogu daro tirada cusub ee aad ku kordhisay hal unug.

Si tan loo sameeyo:

1. Midig ku dhufo unug kasta oo ku yaala xadka ishaada, oo ka dooro Gelida^(Insert) liiska hoos u dhaca.

2. Daaqadda yar ee xulashada, dooro Shift cells down , oo dooro OK . Tani waxay hoos u rogi doontaa dhammaan unugyada kala duwan ee u dhexeeya, iyadoo gelinaysa unug maran meesha aad dooratay.

3. Ku qor shayga cusub ee aad rabto inaad ku darto unugga maran ee aad hadda abuurtay.

Hadda, marka aad doorato summada fallaadha hoose^{(down arrow icon)} ee midigta ee unugga liiska hoos u dhaca ee aad abuurtay, waxaad arki doontaa shayga cusub ee aad hadda ku dartay tirada.

Tani waa hab sahlan oo lagu dari karo shay cusub liiskaaga hoos u dhaca, laakiin waxay u baahan tahay dhawr tillaabo oo dheeri ah. Xaqiiqdi meelna uma fududa sida in lagu daro shay cusub dhamaadka xadkaaga.

Haddii aad jeclaan lahayd inaad taas samayso, kaliya waxaad u baahan doontaa inaad bedesho sida aad u habaysay ansaxinta xogta^{(data validation)} ilaa xadkaaga. Waxaad ku baran kartaa sida tan loo sameeyo qaybta xigta.

Si firfircoon ugu- darka walxaha^(Items) liiskaaga

Si aad u hesho hab ku habboon oo aad alaabta ugu darto liiskaaga hoos u dhaca kaliya adoo ku qoraya shayga cusub dhamaadka xadkaaga habaysan, waxaad u baahan doontaa inaad isticmaasho shaqada ^(function)OFFSET .

Iyada oo la doortay unugga liiska hoos u dhaca, ka dooro Xogta^(Data) liiska iyo Xaqiijinta Xogta^{(Data Validation)} ee ribbon.

Daaqadda Xaqiijinta Xogta^{(Data Validation)} , u beddel Isha kuwa soo socda:

=OFFSET($E$1,0,0,COUNTA($E:$E),1)

Beddel $E$1 iyo $E:$E ee qaacidooyinka^{(the formula)} si aad u isticmaasho xarafka tiirka meesha aad gelisey liiska alaabtaada. Dooro OK^{(Select OK)} si aad u xaqiijiso qaabkan cusub ee Xaqiijinta Xogta^{(Data Validation)} .

Waa kan sida qaacidadadu u shaqeyso:

Doodaha 0 ee ku jira shaqada OFFSET waxay u sheegaan inaan lagu dabaqin wax ka dhimis ah tiirarka ama safafka.
Soosaarka shaqada COUNTA ayaa u sheegaysa shaqada OFFSET dhererka kala duwanaanshaha.
Shaqada COUNTA waxay tirisaa tirada unugyada aan madhnayn tiirka ka kooban tiradaada.

Hadda, mar kasta oo aad qiime cusub ku darto tiirkaas, cabbirka dhererka shaqada OFFSET wuxuu kordhiyaa hal, iyo shaqada OFFSET waxay soo celisaa tirada oo dhan, oo la cusboonaysiiyay si loogu daro gelitaankaaga cusub.

Si aad tan u aragto ficil ahaan, kaliya ku dar geliso cusub oo kala duwan ee alaabtaada. Dooro fallaadha hoos u dhaca ee midig ee unuggaaga hoos u dhaca, oo waxaad arki doontaa gelistaada cusub oo ka muuqda liiska hoos u dhaca.

Maskaxda ku hay in laga yaabo inaad u baahato inaad isticmaashid bar-rog-rogid ee midigta haddii liiskaagu dheer yahay.

Ka saarida liiska hoos u dhaca ee Excel

Ugu dambeyntii, haddii aad waligaa rabto inaad ka saarto ikhtiyaarka hoos u dhaca mid ka mid ah unugyada meesha aad tan ku dhejisay, kaliya dooro unuggaas oo fur daaqadda Xaqiijinta Xogta mar kale.^{(Data Validation)}

U beddel hoos- u- dhigga Oggolow ^(Allow)qiime kasta^{(Any value)} oo dooro OK .

Markaad beddesho goobtan, waxaad arki doontaa in unugu uu ku soo noqon doono sidii caadiga ahayd.

Abuuritaanka Liisaska Hoos u dhaca ee Excel

Abuuritaanka liiska hoos u dhaca ee Excel waa sahlan tahay, waxaadna haysataa fursado badan oo aad ka dooran karto. Habka aad dooranayso waxa ay ku xidhan tahay inta unug ee aad qorshaynayso in aad awood u siiso hal liis oo shay ah, iyo sida aad u jeceshay in liiska shaygu noqdo. Haddii aad qorsheyneyso inaad beddesho shayada liiska si joogto ah, ka dibna hubaal isticmaal habka firfircoon.

How to Create a Drop Down List in Excel

Using a dropdown list in Excel can grеatly reduce the time іt may take to enter data into a spreadsheet. Thankfully, it’s very easy to create a dropdown list in Excel.

There are a few methods to do this, from simple to advanced. You’ll learn every way to do it in this article.

Create a Drop Down List in Excel: Simple Method

The easiest way to create a dropdown list in Excel is by entering the list manually. This is ideal for situations where you only need a dropdown list in a single cell and you don’t need to update it frequently.

For example, if you’re sending people a file where you want them to fill out some fields, and one field has a simple list of choices, this method is ideal.

To create a drop down list using the simple method:

1. Select the cell or cells where you want to create your list.

2. Select Data from the menu, and select Data Validation on the ribbon.

3. This will open a Data Validation window. Select the dropdown list under Allow and select List.

4. This allows you to type the items you want in your list manually. Just type each item into the Source field with each item separated by commas.

5. Select OK. Now you’ll see that the cell you selected has a dropdown arrow next to it. When you select this arrow, you’ll see all of the items you typed out included in this list.

While this is the fastest method to create a dropdown list in Excel, it’s also the hardest to maintain. Modifying the list requires going back into the validation settings and updating the Source field.

If you create a lot of this kind of dropdown lists in Excel, it could take a lot of work modifying these. This is why it’s important to only use this method for single cells, and for lists that you don’t expect to change.

Create a Drop Down List in Excel: Using a Range

If you’d like a more flexible solution, you can pull items for your list from a range of other cells in Excel.

To do this:

1. First, list all of the items you want in your list into any column of cells. You can enter these on the current spreadsheet, or on any other sheet.

2. Repeat the process above to select the cell and launch the Data Validation window. Set the Allow field to List. This time, instead of typing anything into the Source field, select the up arrow icon to the right of this field. This is the range selection option that lets you select the field where you want to pull your list items from.

3. You’ll see the Data Validation window collapse so you can see the entire sheet. Drag the mouse pointer down the entire range of cells that include all of the list items you want to include. When you’re done doing this, select the down arrow icon to the right of the selection field. This will expand the Data Validation window again.

4. You’ll see the range you selected now appears in the Source field. Just select OK to accept these settings.

5. Now, when you select the dropdown arrow to the right of the cell you’ve configured as a dropdown list, you’ll see all of the items included from the range you just selected.

The nice thing about this approach is that you can change any of the items in that list just by modifying any cell in the range. Any change you make there will update every dropdown list you created where you selected this range as the source.

This method is best when you want to format a lot of cells into a dropdown using the same list items. You can have the contents of a single range control the items for all of those lists, and it doesn’t matter how many there are.

Adding Items to Your List

In addition to changing items in your range to update your lists, you can also add new items. You won’t be able to add an item to the end of the range, because your range selection is limited by the first and last cells you’ve selected.

Instead, you’ll need to insert a new entry somewhere in the middle of the range. Excel will dynamically update your range selection in the data validation settings to include the new range that you’ve increased by one cell.

To do this:

1. Right-click any cell in your source range, and select Insert from the dropdown menu.

2. In the small selection window, select Shift cells down, and select OK. This will shift all cells in the range down by one, inserting a blank cell where you’ve selected.

3. Type the new item you want to add into the empty cell you’ve just created.

Now, when you select the down arrow icon to the right of the dropdown list cell you created, you’ll see the new item you just added in the range.

This is an easy way to add new items to your dropdown list, but it does require a few extra steps. It certainly isn’t anywhere as simple as just adding a new item to the end of your range.

If you would like to do that, you’ll just need to change how you’ve configured the data validation to your range. You can learn how to do this in the next section.

Dynamically Adding Items to Your List

For a more convenient way to add items to your dropdown list just by typing the new item at the end of your configured range, you’ll need to use the OFFSET function.

With the dropdown list cell selected, select Data from the menu and Data Validation from the ribbon.

In the Data Validation window, change the Source to the following:

=OFFSET($E$1,0,0,COUNTA($E:$E),1)

Change $E$1 and $E:$E in the formula to use the letter of the column where you’ve entered your list of items. Select OK to confirm this new Data Validation configuration.

Here’s how the formula works:

The 0 arguments in the OFFSET function tell it not to apply any offset to columns or rows.
The output of the COUNTA function tells the OFFSET function the height of the range.
The COUNTA function counts the number of cells that aren’t empty in the column that contains your range.

Now, whenever you add a new value to that column, the height parameter of the OFFSET function increases by one, and the OFFSET function returns the entire range, updated to include your new entry.

To see this in action, just add a new entry to your range of items. Select the dropdown arrow to the right of your dropdown cell, and you’ll see your new entry appear in the dropdown list.

Keep in mind that you may need to use the scroll bar on the right if your list of items is long.

Removing a Drop Down List in Excel

Finally, if you ever want to remove the dropdown option from any of the cells where you’ve set this up, just select that cell and open the Data Validation window again.

Change the Allow dropdown to Any value and select OK.

Once you change this setting, you’ll see that the cell will turn back to normal.

Creating Drop Down Lists in Excel

Creating a dropdown list in Excel is easy, and you have plenty of options to choose from. The method you choose just depends on how many cells you plan to enable with one list of items, and how flexible you’d like the list of items to be. If you plan to change the list items frequently, then definitely use the dynamic approach.

Rebecca Bennet

About the author

Waxaan ahay injineer software ah oo leh in ka badan 10 sano oo waayo-aragnimo ah naqshadaynta, dhisida, iyo ilaalinta codsiyada ku salaysan Windows. Waxaan sidoo kale ahay khabiir aad u yaqaan habaynta erayada, xaashida faafinta, iyo bandhigyada. Waxaan qori karaa sharraxaad cad oo kooban oo kood ah, waxaan u sharixi karaa fikrado kakan horumariyeyaasha cusub, oo aan xalliyo arrimaha duulista macaamiisha.

Sida Loo Sameeyo Liiska Hoos-u-dhaca ee Excel

Ku samee Liiska Hoos^{(Drop Down List)} -u-dhaca ee Excel : Habka^(Method) Fudud