Dropdown lists limit the number of entries for an input field. They help prevent typos and misspellings. And Excel supports surprisingly versatile dropdown list features. Thus, if you want to use Excel for forms or data collection, make it easier for your users to select items with dropdown lists.
Use a dropdown list in a cell when there are specific options available for that cell. For example, you can create dropdown lists containing options like Male and Female, Yes and No, or any other custom list of options.
We’ll show you how to add dropdown lists to your Excel spreadsheets.
How to Create a Dropdown List in Excel
Creating a dropdown list is easy in Excel, but the process is not obvious. Here is a summary of the steps to create a custom dropdown list in Excel:
- Create a Named Range: This step is necessary to create the list we’ll be using in your dropdown list.
- Add Data Validation: This is the not-so-obvious step where you’ll prime the cell for featuring a dropdown list.
- Add Named Range to Data Validation: Finally, you’ll put it all together and define the source of the dropdown list (i.e. your previously Named Range).
- Set Input Message for Data Validation: This step is optional. It lets you add a popup message to guide your spreadsheet users.
I love excel enough that even simple data validation + conditional formatting delights me
— x – lily (@ungilded) October 25, 2017
Excel isn’t easy, it’s powerful. This means you have tons of options, as you’ll see shortly. Now let’s look at the steps to making an Excel dropdown list in more detail.
1. Create a Named Range
One way to add a list of items to a dropdown list using Data Validation is to add your list to a worksheet and name the range of cells containing the list. You can add the list to either the same worksheet where you’re going to add the dropdown list or a different worksheet. Using a named range of cells in a dropdown list makes it easier to maintain.
We’re going to create a dropdown list containing some different types of food on Sheet2 as an example. Enter each item in a separate cell either in one column or one row. Select the items, enter a name for the range of selected cells in the Name Box, and press Enter.
2. Add Data Validation
Go to the worksheet where you want to add your dropdown list. Click the Data tab and then click Data Validation in the Data Tools section.
Make sure the Settings tab is active on the Data Validation dialog box.
3. Add Named Range to Data Validation
Then, select List from the Allow dropdown list. We’re going to use the name from the named range of cells we defined to fill the dropdown list. Enter the following text in the Source box.
=FoodReplace “Food” with whatever name you gave your cell range. Click OK.
The Ignore blank checkbox is checked by default. This allows you to select the cell and then deselect the cell without selecting an item. If you want to require an option to be selected from the dropdown list, uncheck the Ignore blank box.
Tom’s Tips For Excel: Use Data Validation instead of comment for selected cell: Alt+D+L > Input Message tab > type msg > Ck Show input msg…
— Tom Urtis/Atlas (@TomUrtis) January 22, 2017
4. Set Input Message for Data Validation
If you want a popup message to display when the cell containing the dropdown list is selected, click the Input Message tab. Check the Show input message when the cell is selected box and fill out the Title and Input message boxes. You can also use the Error Alert tab to add a message that displays when invalid input is entered in the dropdown list (e.g. if someone types into the cell rather than selecting an option). Check the Show Error alert after invalid data is entered box. Select a Style and fill out the Title and Error message boxes.
Click OK.
When you select a cell containing a dropdown list, the down arrow for selecting an option displays to the right of the cell. The down arrow button only displays when the cell is selected. We’ll show you a way to display a down arrow next to a dropdown list all the time later in this article.
If the dropdown list contains more than eight items, you’ll see a scroll bar on the dropdown list when you click the arrow.
Advanced Dropdown List Options
Now that you have a basic dropdown list, let’s dive into Excel’s advanced settings for editing and customizing your list.
Edit or Delete a Named Range
If you need to edit or delete a named range, you must use the Name Manager. Click the Formulas tab and then click Name Manager in the Defined Names section.
To change the cell range for a name on the Name Manager dialog box, select the Name in the list and then click the cell range button at the bottom of the dialog box. Then, select the cell range and click the cell range button again on the compact version of the Name Manager dialog box, the same way we described in the section above.
Next, click the green checkmark to save the new cell range for the selected Name.
You can also change a Name by selecting it in the list, clicking Edit, editing the name on the Edit Name dialog box, and clicking OK. You can also change the cell range on the Edit Name dialog box.
To delete a name, select the Name in the list and click Delete.
Create a Dependent Dropdown List
A dependent dropdown list is one in which the options change based on the selection in another dropdown list.
For example, when we select Pizza in the dropdown list we created in the How to Create a Dropdown List section above, a second dependent dropdown list contains the different kinds of pizza. If you select Chinese, the options in the second dependent dropdown list contain different types of Chinese dishes.
Just learned how to create a dependent dropdown list in excel, e.g., cell1-state, cell2-cities in that state. GUYS! It’s a life-changer.
— Life at Deloitte (@lifeatdeloitte) February 4, 2014
Before continuing, go back to the How to Create a Dropdown List section and create the main Favorite Food dropdown list, if you haven’t already done so.
Now, we’re going to create three more lists and name them. Enter a list for each of the options in your main dropdown list. Select one of the other lists, enter a name for that list in the Name Box and press Enter. Repeat for each list.
The names of the other lists must match the options in the main dropdown list. For example, one of our other three lists contains types of cookies and is named Cookies, as shown below. The other two lists in the red box on the image below are named Pizza and Chinese.
Before creating the dependent dropdown list, you must select an item in the main dropdown list. It doesn’t matter which option you select. Then, select the cell where you want to add the dependent dropdown list.
Click the Data tab and then click Data Validation in the Data Tools section. Select List in the Allow dropdown list.
Enter the following text in the Source box. Replace “$ B$ 2” with the reference to the cell containing your main dropdown list. Keep the dollar signs in the cell reference. That indicates an absolute reference to the cell that will not change even if you copy or move the formula that references that cell.
=INDIRECT($ B$ 2)The INDIRECT function returns the reference specified by a text string, in this case, the text from the option selected in the main dropdown list in cell B2. For example, if you select Chinese from the main dropdown list, =INDIRECT($ B$ 2) returns the Chinese reference. As a result, the second dropdown list contains the Chinese items.
Click OK.
The list in the Favorite Dish dropdown list in the example below changes depending on what’s selected in the Favorite Food dropdown list.
Copy and Paste a Dropdown List
If you need to duplicate a dropdown list with data validation to other cells, copy and paste the cell using Ctrl + C and Ctrl + V. This copies the dropdown list and the formatting.
If you just want to copy the dropdown list with the data validation but NOT the formatting, select the cell and copy it normally using Ctrl + C. Then, go to the Home tab and click Paste in the Clipboard section. Select Paste Special.
On the Paste Special dialog box, select Validation in the Paste section. Then, click OK.
This will only copy the dropdown list and not the formatting in the original cell.
Note: Be careful when you are working with dropdown lists in Excel. When you copy a cell that does not contain a dropdown list over a cell that contains a dropdown list, the dropdown list is lost. Excel does not alert you or prompt you for confirmation of the action. However, you can undo the action using Ctrl + Z.
Select All Cells Containing Dropdown Lists
Because the down arrow button does not display on a dropdown list unless that cell is selected, it’s hard to know which cells contain dropdown lists. You could apply different formatting to the cells with dropdown lists. However, you need to find all the dropdown lists first, if you haven’t already formatted them. There is a way to select all cells containing dropdown lists, allowing you to see where they are.
First, select a cell containing a dropdown list. Go to the Home tab and click Find & Select in the Editing section. Then, select Go To Special.
On the Go To Special dialog box, select Data Validation. The All option below Data Validation selects all cells that have any data validation rule applied to it. The Same option only selects cells with dropdown lists using the same type of data validation rule as the one in the selected cell.
We’ll accept the default selection of All because our dropdown lists have slightly different rules. One uses a named range to get its value and the other uses the INDIRECT function.
Click OK.
Our two dropdown lists are selected.
Now you can format these cells to differentiate them from other cells, so you know where all the dropdown lists are. If you would rather not apply different formatting to dropdown lists, the next section shows you another way to make them always visible.
Make the Dropdown List Arrow Always Visible
The down arrow button on a dropdown list goes away when the cell is not selected. We’re going to get around this problem by adding a permanent down arrow button to the right of the dropdown lists.
We took a screenshot of the down arrow button on a dropdown list in Excel. We’re going to insert that image in the cell to the right of the dropdown list, so you see a down arrow button even when the dropdown list is not selected. When selecting the dropdown list, the down arrow button for the list displays over the image we inserted.
To begin, download the drop-down-arrow.png file (right-click on that link and select Save link as). Then, select the cell to the right of a dropdown list and go to the Insert tab.
Then, click Illustrations and select Pictures.
On the Insert Picture dialog box, navigate to where you saved the drop-down-arrow.png file and select the file. Then, click Insert.
The image is inserted on the left side of the cell, looking like it’s connected to the dropdown list in the cell to the left. Now, you know where that dropdown list is, and you can select that cell and the real down arrow button displays over the fake one.
Remove a Dropdown List From a Cell
If you decide you want to remove the dropdown list from a cell, select the cell, and open the Data Validation dialog box as described earlier, in the How to Create a Dropdown List section (go to the Data tab and click Data Validation in the Data Tools section). Click the Clear All button, which is available no matter which tab is currently selected.
The options on the Data Validation dialog box are reset to their defaults. Click OK.
The dropdown list is removed and the cell is restored to its default format. If there was an option selected when you removed the dropdown list, the cell is populated with the value of that option.
If you don’t want any of the values to be preserved when you delete the dropdown list, you can copy a blank cell and pasted it over the cell containing the dropdown list. The dropdown list is removed and the cell becomes a blank cell.
Make Dropdown Lists Work for You
Make full use of dropdown lists for data entry in Excel to be more productive and make your life easier. There are many other form controls available in the Controls section of the Developer tab you can experiment with in your worksheets.
How do you make use of dropdown lists in Excel? Share your thoughts and ideas with us in the comments below. Also, have you mastered Excel charts yet?
Read the full article: How to Create a Dropdown List in Microsoft Excel
