How To Make Dependent Dropdown Lists In Excel

Quick Links

• Overview
• Validation Formulas
• Dependent Dropdown Lists

What is a dropdown list?

Dropdown lists allow users to select a value from a predefined list. This makes it easy for users to enter only data that meets requirements. Dropdown lists are implemented as a special kind of data validation. The screen below shows a simple example. In column E, the choices are Complete, Pending, or Cancelled, and these values are pulled automatically from the range G5:G7:

Dropdown lists are easy to create and use. But once you start to use dropdown menus in 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:

=category

And the data validation in column C uses this custom formula:

=INDIRECT(B5)

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:

=INDIRECT("A1")
=A1

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 a 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:

1. You can assemble a text reference inside a formula, which is handy for certain kinds of dynamic references .
2. 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 column headings 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 match the values in the Category dropdown list.

3. Create and test a data validation rule to provide a dropdown list for Category using the following custom formula:

=category

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:

=INDIRECT(B5)

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.

Quick Links

• Overview
• Validation Formulas
• Dependent Dropdown Lists

Introduction

Data validation is a feature in Excel used to control what a user can enter into a cell. For example, you could use data validation to make sure a value is a number between 1 and 6, make sure a date occurs in the next 30 days, or make sure a text entry is less than 25 characters. When a user enters an invalid value, data validation can display a message telling the user what is allowed, as shown below:

Data validation can also stop invalid user input. For example, if a product code fails validation, you can display a message like this:

In addition, data validation can be used to present the user with a predefined choice in a drop-down menu:

This is a nice way to show a user what options are available and valid.

Data validation controls

Data validation is implemented via rules defined in Excel’s user interface on the Data tab of the ribbon.

Important limitation

It is important to understand that data validation can be easily defeated. If a user copies data from a cell without validation to a cell with data validation, the validation is destroyed (or replaced). Data validation is a good way to let users know what is allowed or expected, but it is not a foolproof way to guarantee input.

Defining data validation rules

Data validation is defined in a window with 3 tabs: Settings, Input Message, and Error Alert:

The settings tab is where you enter validation criteria. There are a number of built-in validation rules with various options, or you can select Custom, and use your own formula to validate input, as seen below:

The Input Message tab defines a message to display when a cell with validation rules is selected. This Input Message is completely optional. If no input message is set, no message appears when a user selects a cell with data validation applied. The input message has no effect on what the user can enter — it simply displays a message to let the user know what is allowed or expected.

The Error Alert Tab controls how validation is enforced. For example, when the Style is set to “Stop”, invalid data triggers a window with a message, and the input is not allowed.

The user sees a message like this:

When style is set to Information or Warning, a different icon is displayed with a custom message, but the user can ignore the message and enter values that don’t pass validation. The table below summarizes behavior for each error alert option.

Data validation options

When a data validation rule is created, there are eight options available to validate user input:

Any Value - no validation is performed. Note: if data validation was previously applied with a set Input Message, the message will still display when the cell is selected, even when Any Value is selected.

Whole Number - only whole numbers are allowed. Once the whole number option is selected, other options become available to further limit input. For example, you can require a whole number between 1 and 10.

Decimal - works like the whole number option, but allows decimal values. For example, with the Decimal option configured to allow values between 0 and 3, values like 0.5, 1.5, 2, and 2.75 are all allowed.

List - only values from a predefined list are allowed. The values are presented to the user as a drop-down menu control. Allowed values can be hardcoded directly into the Settings tab or specified as a range on the worksheet.

Date - only dates are allowed. For example, you can require a date between January 1, 2018, and December 31, 2021, or a date after June 1, 2018.

Time - only times are allowed. For example, you can require a time between 9:00 AM and 5:00 PM, or only allow times after 12:00 PM.

Text length - validates input based on the number of characters or digits. For example, you could require code that contains 5 digits.

Custom - validates user input using a custom formula. In other words, you can write your own formula to validate input. Custom formulas greatly extend the options for data validation. For example, you could use a formula to ensure a value is uppercase, a value contains “xyz”, or a date is a weekday in the next 45 days.

Ignore blank - tells Excel not to validate cells that contain no value. In practice, this setting seems to affect only the command “circle invalid data”. When enabled, blank cells are not circled even if they fail validation.

Apply these changes to other cells with the same settings - this setting will update validation applied to other cells when it matches the (original) validation of the cell(s) being edited.

Note: You can also manually select all cells with data validation applied using Go To + Special, as explained below.

Simple drop-down menu

You can provide a dropdown menu of options by hardcoding values into the settings box or selecting a range on the worksheet. For example, to restrict entries to the actions “BUY”, “HOLD”, or “SELL”, you can enter these values separated with commas as seen below:

When applied to a cell in the worksheet, the dropdown menu works like this:

Another way to supply values to a dropdown menu is to use a worksheet reference. For example, with sizes (i.e., small, medium, etc.) in the range F3:F6, you can supply this range directly inside the data validation settings window:

Note that the range is entered as an absolute address to prevent it from changing as the data validation is applied to other cells.

Tip: Click the small arrow icon at the far right of the source field to make a selection directly on the worksheet so you don’t have to enter the range manually.

You can also use named ranges to specify values. For example, with the named range called “sizes” for F3:F7, you can enter the name directly in the window, starting with an equal sign:

Named ranges are automatically absolute, so they won’t change as the data validation is applied to different cells. If named ranges are new to you, this page has a good overview and a number of related tips .

You can also create dependent dropdown lists with a custom formula.

Tip - if you use an Excel Table for dropdown values, Excel will expand or contract the table automatically when dropdown values are added or removed. In other words, Excel will automatically keep the dropdown in sync with values in the table as values are changed, added, or removed. If you’re new to Excel Tables, you can see a demo in this video on Table shortcuts.

Data validation with a custom formula

Data validation formulas must be logical formulas that return TRUE when input is valid and FALSE when input is invalid. For example, to require numeric input in cell A1, you could use the ISNUMBER function in a formula like this:

=ISNUMBER(A1)

If a user enters a value like 10 in A1, ISNUMBER returns TRUE and data validation succeeds. If they enter a value like “apple” in A1, ISNUMBER returns FALSE and data validation fails. To enable data validation with a formula, select “Custom” in the settings tab, then enter a formula in the formula bar beginning with an equal sign (=) as usual.

Troubleshooting formulas

Excel ignores data validation formulas that return errors. If a formula isn’t working, and you can’t figure out why, set up dummy formulas to make sure the formula is performing as you expect. Dummy formulas are simply data validation formulas entered directly on the worksheet so that you can see what they return easily. The screen below shows an example:

Once you get the dummy formula working like you want, simply copy and paste it into the data validation formula area.

If this dummy formula idea is confusing to you, watch this video , which shows how to use dummy formulas to perfect conditional formatting formulas. The concept is exactly the same.

Data validation formula examples

The possibilities for data validation custom formulas are virtually unlimited. To allow only 5-character values that begin with “z” you could use:

=AND(LEFT(A1)="z",LEN(A1)=5)

This formula returns TRUE only when a code is 5 digits long and starts with “z”. To allow only a date within 30 days of today:

=AND(A1>TODAY(),A1<=(TODAY()+30))

To allow only unique values:

=COUNTIF(range,A1)<2

To allow only an email address

=ISNUMBER(FIND("@",A1)

Click for more formula examples and detailed explanations

Data validation to circle invalid entries

Once data validation is applied, you can ask Excel to circle previously entered invalid values. On the Data tab of the ribbon, click Data Validation and select “Circle Invalid Data”:

For example, the screen below shows values circled that fail validation with this custom formula:

=AND(LEFT(A1)="z",LEN(A1)=5)

Find cells with data validation

To find cells with data validation applied, you can use the Go To > Special dialog. Type the keyboard shortcut Control + G, then click the Special button. When the Dialog appears, select “Data Validation”:

Copy data validation from one cell to another

To copy validation from one cell to other cells. Copy the cell(s) normally that contain the data validation you want, then use Paste Special + Validation. Once the dialog appears, type “n” to select validation, or click validation with the mouse.

Note: You can use the keyboard shortcut Control + Alt + V to invoke Paste Special without the mouse.

Clear all data validation

To clear all data validation from a range of cells, make the selection, then click the Data Validation button on the Data tab of the ribbon. Then click the “Clear All” button:

To clear all data validation from a worksheet, select the entire worksheet, then follow the same steps above.