In excel, if you want user to enter a value in a cell only from a set of pre defined list, drop-down list is the best way to do so. A drop down restricts a user to type his own value. Instead, it allows entering value by selecting from a pre defined list.
You just need to follow these simple steps to create drop down list in excel.
Consider that you have a list of countries in CountryList sheet.
1 – Selects the cell where you want to place drop down list. (Let’s say cell B1 in Enter sheet)
2 – On Data tab, in the Data Tools group, click Data Validation.
This will open the “Data Validation” dialog box.
3 – Select List as the Validation criteria in the setting tab of Data Validation dialogue box.
Now the Source box will appear.
4 – Click in the Source box, enter the cell range of CountryList sheet as =’CountryList ‘!$A$2:$A$6. It will insert the drop down list in cell B1 of Enter sheet.
='CountryList '!$A$2:$A$6
If you want the same drop down list in other cell, simply copy the cell with drop down list and paste it to whichever cell you want.
Adding or Removing Items from Drop Down List: Follow these steps to add or remove items from drop down list without opening Data Validation dialog box and changing cell reference.
1 – To add new item in drop down list, select the item in the cell of CountryList sheet and then click Insert button on Cells group of Home tab.
It will insert a new cell above the selected cell. Enter the name of item. It will reflect in drop down list.
To remove an item from drop down list, select the item and click the Delete button on Cells group of Home tab. It will remove the item from the drop down list.
Creating Dynamic Drop Down List: Dynamic drop down list gets updated automatically when you add or remove any item in list. All the steps are same as mentioned above. Only difference is that you need to enter entire range of column in Source box.
='CountryList '!A:A
Keep adding more country names, all will reflect in drop down list.
Creating Drop Down List with Fixed Items: When items to be displayed in drop down list are fixed, you need to type items directly in Source box.
