Excel: Dynamic Dependent Dropdown Lists

In Advanced Excel training, you learned about creating dropdown lists using Data Validation. That was pretty handy on its own… but what if you would like to create a dynamic dependent dropdown list?

For example, below I would like to select a Dessert Type in Column A, and have only those specific desserts appear in my secondary list in Column B.

Two sets of dropdown lists

This can be an incredibly useful tool; imagine lists of cities by state, or students by class, and so on. Lets see how we can set up this slightly more complicated Data Validation.

Exercise File

Interested in following along? Use the exercise file below.


Primary List

When you attended Excel: Advanced Formulas with me, you learned all about Data Validation. The first part of this setup will feel just like what we did in training.

1. Select all of Column A.

2. In the Data tab, select Data Validation.

Data tab, data validation

3. In the popup:

      • Under Allow, select List. 
      • Click into the Source box
      • Select the cells with the dessert types: E1 through J1. Click OK.

All the cells in column A now have a dropdown list of dessert types.

Desert Type dropdown

Dependent Dropdowns

So far, this is nothing new… but remember that your goal was to create a dependent dropdown list: i.e. when someone selects “Cake” as a Dessert Type,  you would like only the cake options to appear, and so on.

Named Ranges

1.  Select the entire range that contains the lists: E1 through J12.

    • Notice how Cake has more options than any other type: we will address that later, but for now, cast as wide a net as possible to include all the desserts.

Lists selected, as described

2. In the Formulas tab, Defined Names group, select Create from Selection.

Formulas tab, create from selection

3. Check Top Row and click OK.

Top row selected

This is going to create named lists based on that top row. Which could have a variety of uses… one will be what we will do next.

Apply Data Validation

For Column B, similar to the first step, you want to again apply some Data Validation, but this time, the options you want to appear need to be based off of what users select in Column A.

1. Select all of Column B.

2. Revisit Data Validation: go to the Data tab and select Data Validation.

Data tab, data validation

3. Change Allow dropdown to a List, just like in the first Data Validation

4. For Source, instead of selecting cells, insert the formula =INDIRECT(A1).

    • This is going to reference cell B1 to look at A1, and through the magic of Relative References (also discussed in Advanced Formulas training), each subsequent row will reference the cell to the left of it to access those named lists you established in the “Named Ranges” step.

Data Validation screen, as describe in text

5. If you receive an error message that the Source “evaluates to an error,” click Yes.

    • Don’t worry, this error is not an issue; the formula is currently evaluating blank cells in Column A and doesn’t know what they reference.

Error message

The fun part: try it out!  Select a Dessert type in column A, and look at the customized options that appear.

Secondary dropdown list working in Excel file

We still have work to do, though. Remember how we have lists of different lengths? This is especially apparent when you pick a shorter listed dessert type, like Confections.

This leads to some trailing blank spaces in those named range lists. We will get rid of these blank spaces in the next step.

Another secondary dropdown, showing blanks

By the way, if it looks like you can’t see all your options in that secondary list, there is a slider bar on the right… At this stage, you might have to scroll up on some of the shorter lists. Again, we will fix that shortly.

Dropdown list slider

Remove Blanks

You might remember this trick from a previous Byte, which covers how to Remove Blank Rows in a Document:

Excel: Remove All Blank Rows in a Document

In this case, we are going to remove a few blank spaces with the goal of cleaning up those dependent dropdown lists that contain blanks.

1. Select the range of cells surrounding your lists.

Lists selected, as described

2. In the Home tab, find the Find and Select dropdown and select Go To Special

Find and Select dropdown, go to special highlighted

3. Select Blanks and click OK.Blanks radio button selected

4. Right click over the selected blanks and click Delete. Right click menu, delete highlighted

5. Select Shift cells up and click OK.

Shift cells up selected

That takes care of the rogue blanks in the dropdowns in Column B. Much better!

Secondary list displayed, blanks no longer appear

Cleaning up the Header Row

This is optional, but if it bothers you that your header (in this case cells A1 and B1) both contain unnecessary dropdowns with Data Validation, you can remove those as a last step.

  1. Select Cells A1 and B1
  2. Go to Data tab, Data Validation.
  3. Select Clear All in the lower left, and click OK.

Data Validation Screen

One More Thought

In real life, you would likely set this up with your source lists on a separate, hidden sheet. That would look much cleaner than having all the lists in view, like we did here. But for the simplification of this example, I included them all on one sheet here.


What do you think, will you use dependent dropdown lists in your workbooks? I would love to hear from you!

Congratulations, Power Users!

Congratulations to our newest Power Users! For the full gallery, and more information about the WSU Microsoft Office Power User Program, please visit: wichita.edu/poweruser

    • Caroline Beach