Excel drop-down lists are similar to ordering food in a restaurant. When you go to a restaurant, you can’t just order anything you want and assume that the restaurant will have all of the ingredients to prepare your meal. Most of the time you order from a menu.
An Excel drop-down list presents spreadsheet users with a list of options to choose from. This makes data entry faster and more accurate. Spelling mistakes are virtually eliminated, and choices can be limited to available options in the list.
This article covers the 3 steps involved in using Excel drop-down lists.
Step 1: Create a List of Items
The first thing you’ll need to do is create a list of items that will be presented to spreadsheet users in the drop-down list. You can create the list in the same worksheet where the drop-down list will be used, or you can create the list on a separate worksheet.
In this example, I created a list of items on the Sheet1 tab. Travel destinations are listed in cells F1:F5.
Step 2: Create a Drop-Down List
The second thing you’ll need to do is create a drop-down list that displays items from the list you created in step 1.
Normally, you can type whatever you want into any cell in your worksheet. With drop-down lists, you leverage a data validation technique that only permits items from a list.
Step #2a – Select the cell(s) where your drop-down list will be displayed. In the screenshot, I highlighted cells A1:A5.
Step #2b – Click the Data Validation button in the Data > Data Tools group on the Ribbon.
Step #2c – Select List from the Allow: box on the settings tab, in the Data Validation dialog box.
Step #2d – Click in the Source: box, drag to select cells F1:F5, then click OK.
More: Relative and Absolute Cell References
Step #2e – Items in the drop-down list are displayed when you click the down-arrow in any of the cells in A1:A5.
Copy Drop-Down Lists
Are you ready for some good news? You can copy drop-down lists!
In the preceding steps, I created a drop-down list in cells A1:A5. If I want to utilize the drop-down list in additional cells, I can simply copy one of these cells to any number of additional cells.
In this screenshot, I copied cell A5 by selecting it and pressing CTRL + C, then I highlighted cells A6:A10 and pasted the contents of cell A5 (including the drop-down list) by pressing CTRL + V.
All of the cells in A1:A10 now include the drop-down list.
Allow Other Entries in Drop-Down Lists
By default, users are restricted to entries in the drop-down list. If I type Toronto in cell A1 in the spreadsheet I created, I’ll get an error message.
To allow other entries in cells where a drop-down list has been defined, display the Data Validation dialog box, select the Error Alert tab, and click to clear the Show error alert after invalid data is entered checkbox.
Users will still be able to select items from the drop-down list, which will ensure a high level of accuracy for those options, but they can also enter destinations that weren’t specified in the source data.
Add/Remove Items from a Drop-Down List
You can add and remove items from your drop-down list as needed. Let’s look at two examples.
To add Toronto to the list, right-click Tahiti in cell F4 and select Insert… from the shortcut menu. In the Insert dialog box, verify that Shift cells down is selected, then click OK. Type Toronto in cell F4.
To remove London from the list, right-click London in cell F6 and select Delete… from the shortcut menu. In the Delete dialog box, verify that Shift cells up is selected, then click OK.
Enter Options Directly
If all you need is a simple drop-down list – one that displays Yes/No, True/False, or something similar – you can enter those options into the Data Validation dialog box directly.
Hide/Unhide Lists
The source data for a drop-down list can be hidden. Hiding the list of items can reduce visual distractions in your spreadsheet.
To hide the list of cities for our drop-down list, right-click the heading for column F and select Hide from the shortcut menu. Drop-down lists work whether the source data is hidden or not.
To unhide the list of cities, drag to select headings for columns E:G, right-click anywhere in the selected area, and select Unhide.
Step 3: Remove a Drop-Down List
To remove a drop-down list, turn Data Validation off.
Step #3a – Drag to select cells containing the drop-down lists you’d like to remove.
Step #3b – Click the Data Validation button in the Data > Data Tools group on the Ribbon.
Step #3c – Select Any value from the Allow: box on the settings tab, in the Data Validation dialog box then click OK.
Microsoft Excel is a registered trademark of Microsoft Corporation. XL Basics contains articles and educational content related to Microsoft Excel but is not affiliated with Microsoft in any way.
