Amaze your boss with dynamic cascading dropdowns in Excel

To improve the quality of data and reduce the likelihood of errors dropdowns in Excel can be a good choice. Making these dynamic, cascading, and automatically reset takes them to another level and will amaze your boss (hopefully!).

The short video below shows how they work. Note that when you select a product from the first dropdown the list of options in the second dropdown also changes. The number of options also differs by product. Finally, if a new product is selected the options dropdown resets. Complicated to explain, but much easier to use.

The major steps in setting up Dynamic Dropdowns in Excel

First, the majority of the dropdown setup does not involve any Excel VBA. The only VBA I use is to reset the options list to 'Select Option' or 'Select Product First' when the user selects a new product.

Setting up the dropdowns themselves involves using the Data Validation functionality, a Table of Information, some named ranges, and clever use of INDEX and MATCH functions.

The table of information looks like this:

Dynamic Dropdowns in Excel populated by Information stored in an Excel Table

and the named ranges and their contents look like:

Dropdowns in Excel using Named Ranges

Finally, the VBA to automatically reset the options when a new product is selected looks like this:

Dynamic Dropdowns in Excel Reset using VBA

The resulting dropdowns and how they work are demonstrated in the short video above. This simple example demonstrates that you can have significant control over user actions. Dynamic dropdowns in Excel also make things easy for users. Finally using dropdowns, especially when dynamic, can greatly increase quality. Quality of data and the quality of your Excel Workbooks.

If you're after step-by-step instructions on how to set something like this up, take a look at this great post by Svetlana Cheusheva on ablebits.com.

If you'd like a copy of my simple example just let me know in the comments below or contact me.  Enjoy.

4 thoughts on “Amaze your boss with dynamic cascading dropdowns in Excel”

Comments are closed.