The solution for how to create a drop down list in Excel is by entering the list manually. This is the situation where you just need a dropdown list in a single cell of Excel and you don’t need to update it frequently.
For example, if you’re sending people an Excel file where you want them to fill out some fields, which contains a simple list of choices, In this case you need to create a drop down list in Excel.
How to create a drop down list in Excel : using simple method
1. First, Select the cell or cells where you want to create a drop down list.
2. Select Data from the menu, and then select Data Validation from the ribbon.
3. This will open a new Data Validation window. Select the dropdown list under Allow and select List which means dropdown list in excel.
4. This option allows you to type the items you want in your list manually. Just type each item into the Source field and separate each item by commas.
5. Select OK. Now you’ll see that the selected cell has a dropdown arrow next to it. When you select this arrow, you’ll see all of the items that you have typed while creating this list.
This is the fastest method to create a dropdown list in Excel. but it’s also hard to maintain or modify the list. Modifying the list needs going back into the validation settings and updating the list fields.
If you create a lot of dropdown lists in Excel with this method, it could take a lot of work and time for modifying these dropdown lists in Excel. This is why this method should be implemented for single cells only , and for lists that don’t expect to change in future.
How to create a drop down list in Excel : Using a range
If you’d like a more flexible solution for the dropdown list, you can pull items for the dropdown list from a range of other cell items in Excel.
Steps to create a drop down list in Excel using a range:
1. First, list all of the items that you want in your dropdown list into any column of cells. You can enter these items on the current spreadsheet, or on any other sheet.
2. Repeat the process above to select the cell where you want your dropdown lists and launch the Data Validation window. Set the Allow field to the list. This time, instead of typing anything into the Source field, select the up arrow icon to the right side of this field. This is the range selection option that allows you to select the fields or items where you want to pull your list items from.
3. You’ll see the Data Validation window collapse so that you can see the entire sheet. Drag the mouse pointer down the entire range of cells that include the list items you want to include in the dropdown list. When you’re done doing this, set the down arrow icon to the right of the selection field. This will expand the Data Validation window once more.
4. You’ll see the range you selected now appears in the Source field of your dropdown list. Just confirm the range and select OK to accept these settings.
5. Now, when you select the dropdown arrow that lies to the right of the cell you’ve configured as a dropdown list, you’ll see all of the items included from the range that you just selected in the previous step.
The good thing about using range to create an excel dropdown list is that you can change any of the items in your list just by modifying the cell that lise in the range. Any change you make there will update every dropdown list you created where you selected this range as the source for your dropdown list.
This method is best when you need to format or modify 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 items are there.
How to add Items in drop down List
After completing the process to create a drop down list in Excel, Items should be added into it to make use of it. we can add, edit/modify, and delete items in drop down list.
In addition to changing items in your range to update your dropdown lists, you can also add new items. You won’t be able to add an item after the end of the selected range, because your range selection is limited by the first and last cell reference value you’ve selected.
Instead, you’ll need to insert a new item into somewhere in the middle of the selected range. Excel will dynamically update your range selection in the data validation settings to include the new item in the range that you’ve increased by one cell.
How to add items in excel drop down list
1. Right-click any cell within your source range, and select Insert from the dropdown menu.
2. In the small selection window, select the Shift cells down option, and select OK. This will shift all cells in the range down by one step, and excel inserts a blank cell where you’ve selected.
3. Type the new dropdown list item that you want to add into the empty cell you’ve just created.
Now, when you select the down arrow icon that is onthe right of the dropdown list cell you created, you’ll see the new item you just added within the range cell.
This is the easy way to add new items to your dropdown list, but it does require a few more steps to create a dropdown list.
Create Drop Down Lists in Excel
Create a dropdown list in Excel is not an easy task, and you have plenty of options to choose from. The method you choose just depends on how many cells that you plan to enable with one dropdown list of items, and how flexible you’d like the dropdown list of items to be. If you plan to change the dropdown list items frequently in future, then definitely use the dynamic approach which will save your time.