Today we will discuss one of the common and very useful feature in excel, how to add or edit existing drop-down list in excel. One of the major advantages of having drop-down list is to improve the efficiency of data entry, while limiting data entry to specific set of items resulting into less error prone data entry.
Follow the below steps and you will see how easily it can be done.
1. Create a list of valid entries for the drop-down list; type the entries in a single column with/without blank cells.
Country List
Note: We can sort the data if required in the order that you want it to appear in the drop-down list.
2. Select the cell where you want the drop-down list(For E.g. : Cell B2).
Select Cell B2
3. On the Data menu, click Validation, and then click the Settings tab. And in the Allow box, click List.
Click DataValidation from MenuBar
Select list for Data validation
4. We can now specify the location of the list of valid entries. We can enter a reference of Current worksheet or different worksheet of same workbook or different workbook to list in the Source box.
5. On the Settings tab, to the right of the Source box, click the highlighted red box button to collapse the dialog box, and then on we can select all of the cells of worksheet that contain the entries for our drop-down list.
Click red highlighted box to enter source
Select list for drop-down
6. Select range of cells as list of entries for drop-down and press Enter. Selected range will appear in the Source Box as shown in the below screenshot.
Check source box for selected range
7. Just Click OK to get the drop down list in our selected cell. But, keep the Ignore Blank and In-cell drop-down option checked in order to get non blank entries in our drop-down list. Refer the above screenshot.
Drop-down list appears for selected range
8. Now if we want to edit our list of countries (i.e. to add Japan to list) and want it to appear in drop down list. We just have to follow the same steps as mentioned above and re-select the range of cells (i.e. E2:E5 change it to E2:E6). And Hurray Japan got add to the drop-down list.
Adding other country to existing list.
Japan got added in the existing list
Using Name Manager, we can also achieve the same task.
1. Go to Formulas and click Name Manager (Ctrl + F3) is shortcut for the same. Click New button and then specify the details as per below screenshot. CountryList item will appear in the Name Manager list.
Using NameManager for creating list
2. We just have to follow the same steps to create drop-down list but only change will be in the source box. Instead of range of cells, we will specify the named range(i.e CountryList in Source Box). Refer the below screenshot and it will clarify all the doubts.
Added NamedRange ‘CountryList’ in the SourceBox
That’s it!!! It is that simple to create drop-down list in excel.
Any queries or clarifications related to this article, please write me at pareshj@snpinfotech.in or visit www.snpinfotech.in
In my next article, we will see how to create dependent drop-down list.
S&P Infotech Blog 