Excel Data Validation — Create Dependent Lists

Download the Sample File

More Data Validation Tutorials

You can limit the choices in an Excel data validation list, by using named ranges and the INDIRECT function, to create dependent data validation lists. In this example, if Fruit is selected as the Category, only Fruit appears in the Item drop-down list.

Another method is to use the OFFSET function, to extract items from a sorted list, as described here:Dependent Dropdowns from a Sorted List

Create Named Lists

Start by creating Named Lists, which will be the choices in the dependent data validation drop down lists. In this example, the first list will be named Produce.
It contains the Produce categories — Fruit and Vegetable.

1. Create the first Named List

1. In an empty area of the workbook, type the entries you want to see in the drop-down lists. These should be one-word entries, to match the dependent list names that will be created. If you need to use multiple word entries, see:
   Using Two Word Items
2. Select the cells in the list (but not the heading).
3. Click in the Name box, to the left of the formula bar
4. Type a one-word name for the list, e.g. Produce.
5. Press the Enter key.

2. Create the supporting Named Lists

1. Type the entries you want to see in the Excel data validation drop-down list for one of the Produce categories.
2. Select the cells in the list.
3. Click in the Name box, to the left of the formula bar
4. Type a one-word name for the list, e.g. Fruit. This name must be exactly the same as the matching entry in the Produce list.
5. Press the Enter key.
   
6. Create another list with the items for the next category — Vegetable in this example

Apply the Excel Data Validation

The cells in the Category column will allow a List.
The cells in the Item column will use the INDIRECT function to select a list.
1. Apply the Excel Data Validation

1. Select the cells in which you want to apply Excel data validation using the Category List
2. From the Data menu, choose Validation.

   

3. From the Allow drop-down list, choose List
4. In the Source box, type an equal sign and the list name, for example: =Produce
5. Click OK.

2. Create the Dependent Data Validation

 

1. Select the cells in which you want to apply dependent data validation using the Fruit or Vegetable List, dependent on which Category has been selected
2. From the Data menu, choose Validation.
3. From the Allow drop-down list, choose List
4. In the Source box, type an equal sign and INDIRECT function, referring to the first data cell in the Category column: =INDIRECT(A2)
5. Click OK.

Note: If cell A2 is empty, you’ll see the message shown below. Click Yes to continue.

Test the Dependent Data Validation

1. Click on a cell in the Category column, and select either Fruit or Vegetable, from the Produce drop down list.
2. Press the Tab key on your keyboard, to move to the Item column, in the same row.
3. In the Item column, click the drop down arrow, and select an item. The drop down list shows either Fruit or Vegetable items, depending which has been selected in the Category column

NOTE: If a Category has not been selected, the Item drop down in that row will not work.