Create Multiple Dependent Drop-Down Lists in Excel (on Every Row)

Learn how you can create MULTIPLE dependent data validation lists in Excel on the SAME sheet using a single formula. This formula also excludes blank cells on the bottom of the data validation list.

👩‍🏫 Step-by-Step Tutorial:
Start with Basic Drop-Downs: Learn how to create a basic drop-down list for divisions.
Tackle the Challenge: Dive into creating a dependent drop-down list for apps based on the division selected, using the OFFSET formula.
Formula Magic: Discover how to dynamically change the drop-down contents using OFFSET and MATCH functions.
Avoid Empty Choices: Learn to dynamically adjust the number of choices in your drop-down to avoid empty selections.
Apply to Multiple Rows: Easily copy and paste the validation to multiple rows for a consistent, user-friendly experience.

🔧 Key Techniques Covered:
OFFSET Function: Understand the OFFSET function for dynamic range selection.
MATCH Function: Use MATCH to locate and select the correct column based on division.
Dynamic Counting: Implement COUNTA with OFFSET for dynamic list sizing.

In our example the selection of a division will drive the available choices for an app in an Excel data validation list. We'll use the Excel OFFSET function and the MATCH function for the dependent drop down selection. This way you can have as many categories as you like.

The challenge we have is that the number of choices inside the dependent drop down may vary depending on the division. I'll show you 2 solutions for this:

Fixed number of Choices in the dependent drop-down
If you do not mind empty spaces (blank cells) at the bottom of the drop down you can fix the height argument in the OFFSET function to a specific (maximum) number of choices.

Dynamic number of Choices in Drop-Down
If we want to restrict the number of visible cell values inside the dependent drop-down we can expand the formula to get rid of the blank cells on the bottom. This way we can eliminate empty spaces in the drop down selection. We will use another OFFSET formula inside a COUNTA formula to dynamically count the number of available choices.

00:00 Dependent Drop-Down Lists in Excel on EVERY Row
01:20 Creating the First Drop Down
01:58 Creating the Dependent Drop Down List
05:55 Fixed Number of Choices in the Dependent Drop-Down
07:15 Dynamic number of Choices in Drop-Down
10:21 Copy Down Data Validation

🚩Let’s connect on social:

Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!

#excel

[jimlimberdavis]

I'm always learning something new with these videos! Absolutely love LG!!!
I typically don't use the formulas presented, but I do incorporate small aspects of the formulas used; in this case, Counta.

Paying attention to the 'moving' parts of each formula can be a huge benefit in learning practical application and growth. I love the instruction given here! LOVE IT!

[nijr]

Amazing! Exactly what I was looking for to help with a project at work! So very well explained, very calm and soft voice to help understand complex tasks, very well informed and explains each step thorough. One of the best Excel tutorials I have ever seen. Will be looking through this channel for other tips! Thank you!

[djunabug]

Perfect!! Thank you so much Leila! One thing I just learned the hard way is that COUNTA counts not only cells filled with contents but also with formulas, as well as null values ( "" ), after copy-pasting values to escape the formulas. The latter had me stumped awhile, with lots of extra blanks. A good "Clear All" beneath each of my subcategory lists got me sorted, and now I have beautiful drop-downs. Thanks again!!

[jancatalinnicoara]

Excellent explanation Leila. MULTIPLE dependent data validation function should be implemented in Excel as a embedded function (combined by simple one's). Very nice to watch your tutorials. :) Great job.

[davidg.]

Thank you very much Leila for sharing all your great knowledge. I am actually quite surprised you don't use more (and emphasize) the use of Tables or at least named ranges since they are dynamic and avoid having to use COUNTA(). Furthermore, tables keep formatting and validation rules so no need to add unnecessary rules to empty cells.

[fastgalix]

Hey,
Apologies if this was mentioned before elsewhere but you can make this somewhat more dynamic, e.g., in the case you want to add a division.

The key is to format your division as a table, name it, e.g., TabDivision, and use indirect in the validation list:

Validation for the "Select Division" column:
Validation for the first row (B5) in the "Select APP" column: =OFFSET(INDIRECT("TabDivision[[#Headers], [" & A5 & "]]"), 1, 0, & A5 & "]")))

That way you can easily add a division by adding a column to the table and the number of APP is changed automatically by the COUNTA...

There are 2 limitations:
- The APP in each division is a dense list, i.e., no null value except at the end.
- The name of the Table cannot be changed easily as excel will not propagate the change of name in INDIRECT

Cheers

[waltertrimble]

Hi Leila, thanks for having uploaded such a useful tutorial. You explain things in a crisp and clear manner. The combination of the formulas you showed works out like voodo magic! Please keep up the good work.

[nbjamspice]

Thank you so much for this tutorial. Your explanation and step-by-step instructions are so easy to follow. I really appreciate your tutorials and look forward to learning new ways to help me do my job more efficiently. It's amazing that there are so many formulas that exist for the laborious and tedious steps we do manually.

[AlvinRyellPrada]

This is total genius!
I literally followed step by step how you do it and i got it!
I love how you really explained stuff. Making complex thing simple. Thank you

[erickbourdin]

Wow amazing mastery of the offset function ! And so clearly explained.
Thank you Lady

[dwbechtel]

Leila, I watched several of your videos on Drop downs and finally solved my problem with your techniques. Much appreciated. I will review your courses and find more to learn from you.
Thank you. Dave B

[abdurrehmantariq]

Extraordinary! Exactly what I was looking for, very calm and soft voice to help understand complex tasks, very well informed and explains each step thorough. One of the best Excel tutorials I have ever seen.

[GenerationGap]

very cool. I'm still working through your essentials course while in lockdown. Stay safe

[Nyla_emotes]

For anyone having trouble with pasting the data validation and getting in other rows only the 1st option in the drop down list: after every MATCH COMMAND (there are 2 in the formula) do not lock the cell! So do not write in the formula MATCH($E$2...but leave that unlocked as MATCH(E2 ... and then this will work fine!

[PaulBryanWalkerPBW]

Thank you so much, I am creating a simple address database for our Christmas cards and I want to use an Excel sheet as a database of CITY/STATE/COUNTY. This lesson is exactly what I needed.

[GameAGuy]

You are our Excel National Treasure. Apreciate you so much. Sometimes these formulas are so mentally overwhelming.

[RaiRai]

Hey Leila, I'm a fresh grad mechanical engineer and currently interning in oil and gas company, and my task is to create a database for my engineering department by compiling their previous and current projects, as well as gathering and combining documentations using Excel. Just wanted to thank you for your awesome video for the searchable drop down list (I also improvise it by assigning my macro in that data validation list and it will filter out all the unnecessary data), and eventually later in the future I would need to use this feature for the database. Your contents are truly great, and it allows me to think deeply on how to make my database even better using your video and other sources as references.

Hope you have a wonderful day!

[bayarahmed]

Thank you Leila ! As always, perfect !

[davewebster]

I needed this solution for the project I’m working on. Awesome as always Leila! Thank you

[rodlourenco]

Excellent explanation. Easy to follow and a life saver for me. Thank you!