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.
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.
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.
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.
2. In the Formulas tab, Defined Names group, select Create from Selection.
3. Check Top Row and click OK.
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.
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.
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.
The fun part: try it out! Select a Dessert type in column A, and look at the customized options that appear.
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.
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.
Remove Blanks
You might remember this trick from a previous Byte, which covers how to Remove 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.
2. In the Home tab, find the Find and Select dropdown and select Go To Special
3. Select Blanks and click OK.
4. Right click over the selected blanks and click Delete. 
5. Select Shift cells up and click OK.
That takes care of the rogue blanks in the dropdowns in Column B. Much better!
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.
- Select Cells A1 and B1
- Go to Data tab, Data Validation.
- Select Clear All in the lower left, and click OK.
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.
Thoughts?
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
