See also: Data validation overview | Data validation formulas
One of the most useful features of data validation is the ability to create a dropdown list that let users select a value from a predefined list. Dropdown lists make it easy for users to enter only data that meets your requirements.
Dropdown lists are easy to create and use. But once you start to use dropdown menus to your spreadsheets, you'll inevitably run into a challenge: how can you make the values in one dropdown list depend on the values in another? In other words, how can you make a dropdown list dynamic?
Here are some examples:
- a list of cities that depends on the selected country
- a list of flavors that depends on type of ice cream
- a list of models that depends on manufacturer
- a list of foods that depends on category
These kind of lists are called dependent dropdowns, since the list depends on another value. They are created with data validation, using a custom formula based on the INDIRECT function and named ranges. This may sound complicated, but it is actually very simple, and a great example of how INDIRECT can be used.
Read on to see how to create dependent dropdown lists in Excel.
Dependent dropdown example
In the example shown below, column B provides a dropdown menu for food Category, and column C provides options in the chosen category. If the user selects "Fruit", they see a list of fruits, if they select "Nut", they see a list of nuts, and if they select "Vegetable", they see a list of vegetables.
The data validation in column B uses this custom formula:
And the data validation in column C uses this custom formula:
Where the worksheet contains the following named ranges:
category = E4:G4 vegetable = F5:F10 nut = G5:G9
fruit = E5:E11
How this works
The key to this technique is named ranges + the INDIRECT function. INDIRECT accepts text values and tries to evaluate them as cell references. For example, INDIRECT will take the text "A1" and turn it into an actual reference:
Similarly, INDIRECT will convert the text "A1:A10" into the range A1:A10 inside another function:
=SUM[INDIRECT["A1:A10"] =SUM[A1:A10]
At first glance, you might find this construction annoying, or even pointless. Why complicate a nice simple formula with INDIRECT?
Rest assured, there is method to the madness :]
The beauty of INDIRECT is that it lets you use text exactly like a cell reference. This provides two key benefits:
- You can assemble a text reference inside a formula, which is handy for certain kinds of dynamic references.
- You can pick up text values on a worksheet, and use them like a cell reference in a formula.
In the example on this page, we're combining the latter idea with named ranges to build dependent dropdown lists. INDIRECT maps text to a named range, which is then resolved to a valid reference. So, in this example, we're picking up the text values in column B, and using INDIRECT to convert them to cell references by matching existing named ranges, like this:
=INDIRECT[B6] =INDIRECT["nut"] =G5:G9
B6 resolves to the text "nut" which resolves to the range G5:G9.
How to set up dependent dropdown lists
This section describes how to set up the dependent dropdown lists shown in the example.
1. Create the lists you need. In the example, create a list of fruits, nuts, and vegetables in a worksheet.
2. Create named ranges for each list: category = E4:G4, vegetable = F5:F10, nut = G5:G9, and fruit = E5:E11.
Important: the values in E4, F4, and G4 must match the last three named ranges above [vegetable, nut, and fruit]. In other words, you must make sure that the named ranges you created are named to match the values that will appear in the Category dropdown list.
Note: If named ranges are new to you, see this page.
3. Create and test a data validation rule to provide a dropdown list for Category using the following custom formula:
Note: just to be clear, the named range "category" is used for readability and convenience only – using a named range here is not required. Also note data validation with a list works fine with both horizontal and vertical ranges – both will be presented as a vertical dropdown menu.
4. Create a data validation rule for the dependent dropdown list with a custom formula based on the INDIRECT function:
In this formula, INDIRECT simply evaluates values in column B as references, which links them to the named ranges previously defined.
5. Test the dropdown lists to make sure they dynamically respond to values in column B.
Note: the approach we are taking here is not case-sensitive. The named range is called "nut" and the value in B6 is "Nut" but the INDIRECT function correctly resolves to the named range, even though case differs.
Dealing with spaces
Named ranges don't allow spaces, so the usual convention is to use underscore characters instead. So, for example, if you want to create a named range for ice cream, you would use ice_cream. This works fine, but dependent dropdown lists will break if they try to map "ice cream" to "ice_cream". To fix this problem you can use a more robust custom formula for data validation:
=INDIRECT[SUBSTITUTE[A1," ","_"]]
This formula still uses INDIRECT to link the text value in A1 to a named range, but before INDIRECT runs, the SUBSTITUTE function replaces all spaces with underscores. If the text contains no spaces, SUBSTITUTE has no effect.
Practice file
The example file is attached below – check it out to see how it works. If you want to learn the technique yourself, I'd recommend you build a file of your own. The best way to learn is by doing.
Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel for the web Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 for Mac Excel 2016 Excel 2016 for Mac Excel 2013 Excel 2010 Excel 2007 More...Less
After you create a drop-down list, you might want to add more items or delete items. In this article, we'll show you how to do that depending on how the list was created.
If you set up your list source as an Excel table, then all you need to do is add or remove items from the list, and Excel will automatically update any associated drop-downs for you. To add an item, go to the end of the list and type the new item. To remove an item, press Delete. Tip: If the item you want to delete is somewhere in the middle of your list, right-click its cell, click Delete, and then click OK to shift the cells up.
-
Select the worksheet that has the named range for your drop-down list.
-
Do any of the following:
-
To add an item, go to the end of the list and type the new item.
-
To remove an item, press Delete.
Tip: If the item you want to delete is somewhere in the middle of your list, right-click its cell, click Delete, and then click OK to shift the cells up.
-
-
Go to Formulas > Name Manager.
-
In the Name Manager box, click the named range you want to update.
-
Click in the Refers to box, and then on your worksheet select all of the cells that contain the entries for your drop-down list.
-
Click Close, and then click Yes to save your changes.
Tip: If you don't know what a named range is named, you can select the range and look for its name in the Name Box. To locate a named range, see Find named ranges.
-
Select the worksheet that has the data for your drop-down list.
-
Do any of the following:
-
To add an item, go to the end of the list and type the new item.
-
To remove an item, click Delete.
Tip: If the item you want to delete is somewhere in the middle of your list, right-click its cell, click Delete, and then click OK to shift the cells up.
-
-
On the worksheet where you applied the drop-down list, select a cell that has the drop-down list.
-
Go to Data > Data Validation.
-
On the Settings tab, click in the Source box, and then on the worksheet that has the entries for your drop-down list, select all of the cells containing those entries. You'll see the list range in the Source box change as you select.
-
To update all cells that have the same drop-down list applied, check the Apply these changes to all other cells with the same settings box.
-
On the worksheet where you applied the drop-down list, select a cell that has the drop-down list.
-
Go to Data > Data Validation.
-
On the Settings tab, click in the Source box, and then change your list items as needed. Each item should be separated by a comma, with no spaces in between like this: Yes,No,Maybe.
-
To update all cells that have the same drop-down list applied, check the Apply these changes to all other cells with the same settings box.
After you update a drop-down list, make sure it works the way you want. For example, check to see if the cell is wide enough to show your updated entries.
If the list of entries for your drop-down list is on another worksheet and you want to prevent users from seeing it or making changes, consider hiding and protecting that worksheet. For more information about how to protect a worksheet, see Lock cells to protect them.
If you want to delete your drop-down list, see Remove a drop-down list.
To see a video about how to work with drop-down lists, see Create and manage drop-down lists.
If you set up your list source as an Excel table, then all you need to do is add or remove items from the list, and Excel will automatically update any associated drop-downs for you. To add an item, go to the end of the list and type the new item. To remove an item, press Delete. Tip: If the item you want to delete is somewhere in the middle of your list, right-click its cell, click Delete, and then click OK to shift the cells up.
-
Select the worksheet that has the named range for your drop-down list.
-
Do any of the following:
-
To add an item, go to the end of the list and type the new item.
-
To remove an item, press Delete.
Tip: If the item you want to delete is somewhere in the middle of your list, right-click its cell, click Delete, and then click OK to shift the cells up.
-
-
Go to Formulas > Name Manager.
-
In the Name Manager box, click the named range you want to update.
-
Click in the Refers to box, and then on your worksheet select all of the cells that contain the entries for your drop-down list.
-
Click Close, and then click Yes to save your changes.
Tip: If you don't know what a named range is named, you can select the range and look for its name in the Name Box. To locate a named range, see Find named ranges.
-
Select the worksheet that has the data for your drop-down list.
-
Do any of the following:
-
To add an item, go to the end of the list and type the new item.
-
To remove an item, click Delete.
Tip: If the item you want to delete is somewhere in the middle of your list, right-click its cell, click Delete, and then click OK to shift the cells up.
-
-
On the worksheet where you applied the drop-down list, select a cell that has the drop-down list.
-
Go to Data > Data Validation.
-
On the Settings tab, click in the Source box, and then on the worksheet that has the entries for your drop-down list, Select cell contents in Excel containing those entries. You'll see the list range in the Source box change as you select.
-
To update all cells that have the same drop-down list applied, check the Apply these changes to all other cells with the same settings box.
-
On the worksheet where you applied the drop-down list, select a cell that has the drop-down list.
-
Go to Data > Data Validation.
-
On the Settings tab, click in the Source box, and then change your list items as needed. Each item should be separated by a comma, with no spaces in between like this: Yes,No,Maybe.
-
To update all cells that have the same drop-down list applied, check the Apply these changes to all other cells with the same settings box.
After you update a drop-down list, make sure it works the way you want. For example, check to see how to Change the column width and row height to show your updated entries.
If the list of entries for your drop-down list is on another worksheet and you want to prevent users from seeing it or making changes, consider hiding and protecting that worksheet. For more information about how to protect a worksheet, see Lock cells to protect them.
If you want to delete your drop-down list, see Remove a drop-down list.
To see a video about how to work with drop-down lists, see Create and manage drop-down lists.
In Excel for the web, you can only edit a drop-down list where the source data has been entered manually.
-
Select the cells that have the drop-down list.
-
Go to Data > Data Validation.
-
On the Settings tab, click in the Source box. Then do one of the following:
-
If the Source box contains drop-down entries separated by commas, then type new entries or remove ones you don't need. When you're done, each entry should be separated by a comma, with no spaces. For example: Fruits,Vegetables,Meat,Deli.
-
If the Source box contains a reference to a range of cells [for example, =$A$2:$A$5], click Cancel, and then add or remove entries from those cells. In this example, you'd add or remove entries in cells A2 through A5. If the list of entries ends up being longer or shorter than the original range, go back to the Settings tab and delete what's in the Source box. Then click and drag to select the new range containing the entries.
-
If the Source box contains a named range, like Departments, then you need to change the range itself using a desktop version of Excel.
-
After you update a drop-down list, make sure it works the way you want. For example, check to see if the cell is wide enough to show your updated entries. If you want to delete your drop-down list, see Remove a drop-down list.
You can always ask an expert in the Excel Tech Community or get support in the Answers community.
Create a drop-down list
Apply Data Validation to cells
Video: Create and manage drop-down lists