How to Create a Drop Down List in Excel
Drop down lists or menus in Excel provide a list of values from which users can pick to populate a cell. This is especially handy when users are doing data entry in an Excel spreadsheet and you want to make it easier for them to pick specific values, as well as limit their potential inputs to a few different options. Users will only be able to choose values from the drop down list and can not enter their own values. But how do you create your own drop down list in Excel?
Choose Values for Your Drop Down List
The first step is to create a list of values which you want to be options in your drop down list. These values can be somewhere out of the way, like on a second tab in your workbook, or they can be entered directly in the dialog box (more on this below).
Chose Where you Want the Drop Down List to Appear
Click the cell(s) where you want the drop down list menu to appear. These cells will only accept values from the list you specified in step one. When the user clicks on one of these cells, a downward triangle will appear, indicating that the cell has a drop down list of potential options.
Click Validate Data
Navigate to the Data tab in the Excel ribbon and click Validate Data - it's a small icon with a green check mark and a red "not allowed" sign.
A "Validate Data" dialog box will appear. Remember it- this is where all the important steps happen!
In the dialog box choose "List" from the "Allow" menu. Click the "Source" field of the dialog box and select the cells which contain your list values. You can type out the range of cells with a colon, or click directly on the cells in your workbook that you want to include and the source field will update to include the specified range. Then click OK or hit enter.
You've Created a Drop Down List!
And that's it; you've just created your first drop down list in Excel! The cells you highlighted before clicking Validate Data will now contain your drop down list. Users will be able to quickly pick values from the list while doing data entry in Excel, and you can sleep easy at night knowing that users will only be able to input values from a pre-approved list.
But what happens if you want to give users the ability to input custom values, or display a pop up message when users click your list? Read on to learn about tricks and customizations for your drop down list in Excel.
Directly Enter Options for a Drop Down List
If you don't want to maintain a separate list of possible values somewhere else in your workbook, you can type drop down list values directly into the dialog box, rather than cell references to your list values.
In the example above, notice how we don't provide cell references for the drop down list items, but rather the values themselves. You can type the values directly into the dialog box, separated by commas, so that you don't have to maintain a list of values elsewhere. The drawback here is that to edit your list of values, you need to open the dialog box again rather than just update your specified cells.
Add a Pop Up Message to Your Drop Down List
Excel has countless options to customize your spreadsheet, and a pop up message is one of them. If you want a pop up message to appear when a user clicks the drop down list, such as "Pick a state from the list!" then click the Input Message tab in the Data Validation dialog box. Check the box that says "Show input message when cell is selected" and then type your message into the box below. The user will be greeted with your message when they click the drop down list. You can use this feature to provide important instructions, or just to add a nice, personalized touch to your Excel spreadsheets.
Allowing Custom Values in a Drop Down List in Excel
A drop down list in Excel is part of the data validation functionality, which allows certain values to be entered in a cell, but not others. By default, a cell with a drop down list will allow a user to enter only values from the list - if the user types any other values into the cell they will receive an error message.
In certain cases, you may want to allow the user to add custom values in addition to the list items provided. If this is the case, navigate to the "Error Alert" tab of the Data Validation dialog box, and un-check the box that says "Show error alert after invalid data is entered." Now the user is able to manually type in a value that wasn't an option in the drop down list.
Say you want to caution the user against adding a custom value, but allow them to add a custom value if they really want to. You can leave the "Show error alert after invalid data is entered" box checked, but switch Style from Stop to Information or Warning. Then you can type a message the user will see if they enter a value that is not an option on the list. The user can then choose whether to leave the value in the cell or remove it.
How Do I Edit or Remove a Drop Down List in Excel?
If you decide that you want to update or remove your drop down list, simply select the cell(s) where the drop down list exists, and click the Data Validation button again. This will bring up that same dialog box with information about your list. You can edit the information about your drop down list or pick new cells to reference for list options.
If you want to remove your list from your worksheet altogether, simply click Clear All in the dialog box and your drop down list will disappear.
Learn More about Excel
Excel Exercises has tons of information and practice exercises to help you learn and practice working with new concepts in Excel. Try the Excel Wine Dashboard Project for some hands-on practice with drop-down lists in Excel.