The EASIEST Excel multiple level drop down list you've ever seen! It’s multi row too!

⏬ Click to see more: plus ⚠️ important note:

Easiest Multi Level Excel Drop Down List

A newer more robust solution here Updated (now more robust)

!!BUT!! if you want row after row of dependent lists it needs a different approach.

I've seen a lot of solutions out there that attempt to solve this, I've even done a crazy one a few months back, but this is by far the easiest technique I've come across.

⚠️ To prevent any value being typed in the dependent validation boxes you MUST UNTICK "Ignore Blanks". This in my view is a bug and will hopefully be fixed one day.

00:00 Intro
01:06 The Setup
01:50 Formula 1
=UNIQUE(tblOptions[List1])

02:53 Formula 2
=TOROW(
UNIQUE(
FILTER( tblOptions[List2], tblOptions[List1]=G6, "Empty List")))

04:50 Formula 3
=XLOOKUP(AB5, Level1Choice, Level2Result )#excel

06:14 Apply formulas to Data Validation
08:04 The Second Level List
10:36 Conditional Formatting
=COUNTIFS( XLOOKUP( I7, Level2Choice, Level3Result )#, J7)=0

You can download my workbook from the bottom of my blog post here:

----------------------------------

Thanks to Celia Alves for the inspiration. Check out Celia's video here:

----------------------------------

Thanks to Erik Oehm for trying to inspire me with Lambdas ( I'll get there one day! 😁 )
Here's Erik's elegant Lambda based solution (m

----------------------------------

Did you know I've written a book "Power BI for the Excel Analyst"?

Connect with me

[reanalytics]

I watched the video, got excited with the solution, and forgot to comment, like and share.
I only came back to do just that.
Dependent validation inside excel tables had been a nightmare before I watched this video. ❤❤❤❤❤❤❤

[maxrodz]

After a dozen videos and hours of trying different tips on dependent drop-down lists, FINALLY got one solution that worked. THANK YOU! THANK YOU!

[VINAMRABHAVSAR]

Best Video On Multiple Dependent Dropdown I have ever seen. I was finding the solution for 2 days and finally I got one. Thank you very much!!!

[CeliaAlvesSolveExcel]

Brilliant indeed, Wyn! You simplified a lot and it is just beautiful to see.
Thank you for the shout-out! 💫

[luisgiraldes]

Jeeesus! I was looking for this for... ages! Congratulations for this great data dynamic validation and thank you for the great tutorial.

[rodolfoflores]

This guy is a genious. A little bit scary, but a genious.

[thegers]

I feel as though this should be one of the first lessons you are taught when learning excel. Until you have had years of experience with Excel, you don't realise the power and efficiency of good data validation to reduce errors in manual input. Thank you very much for this!

[saffamike]

I'm building a complex data entry template to support month-end bulk upload routines and have been banging my head against the wall for days trying to solve my problem with data validation. Your video addressed all my issues. Thanks!!

[Bhavik_Khatri]

Bloody awesome! I used indirect formulas, but your new method is the winner.

[rsudeshv]

Thank you so much for doing this, I found you video after spending several hours trying to find a solution. This is precisely what I was looking for.

[marasanchez]

Sir you literally saved my life!! been struggling for about two days with my lists and this video finally helped me figure it out! thank you thank

[ebrusalic]

I wish you made this video 1 year ago when I created a massive formula for a multiple dependent data validation drop down that I spent many hours and still use to date. This is incredibly beautiful. I never thought you could use # in this way.

[EricHartwigExcelConsulting]

This is really cool! I can think of so many ways to use this formula combination!
Thank you Wyn for creating/sharing this and Celia for introducing this formula combination!
Can not wait to put it to use!

[sheripresloid]

After spending hours looking for what I needed, you showed up! Subscribed! This is perfect for my very detailed drop downs with many options based on the previous drop down. Because of my massive amount of combinations, you made my life so much easier! Thank you for sharing. Can't wait to look at your other videos! :)

[petercompton]

This is brilliant - I tried it a couple of months ago and could not get it to work (bear of little brain syndrome strikes again) but it now does. Amazing. Big thanks to you and Celia

[mat.frazer]

I don't leave comments on videos but when I do... I completely gutted my solution! Thank you!

[iSchmidty]

Thank you so much for your help! I will be using this today at work, and maybe next week in my RPG project. I am foreseeing needing longer rows and worry about them spilling over top of each other, so I think for my needs I will put each column pair on its own sheet. Eg. Level1Choice/Level2Result on one sheet, and Level2Choice/Level3Result on another etc. until all the columns are where they need to be! Luckily I don't have more than 2 columns for my work one, but the RPG is going to get dicey! XD

[IvanCortinas_ES]

The laboratory at full capacity. It is certainly an amazing result. Thanks Wyn!

[DoskaSims]

To be honest, I'm so so jealous of you havin Office 365. Creating this solution for office 2019 truly creates a pain in my life :D

[Locomaid]

The set up of data is key. It is an „old school“ data table, not multirelational, and maybe not sexy. But … I find that many users don’t know how to correctly set up a simple table or database. Thanks for showing that it is still a relevant skill!