Excel Dropdown List Software Really Helps

The above linked tutorial describes four different approaches, each including a crazy number of steps, a bunch of different formulas, and a handful of limitations relating to multi-word entries, blank cells, etc.

The good news is that those methods were designed for pre-dynamic versions of Excel.

The introduction of dynamic arrays in Excel 365 has changed everything With new dynamic array functions, creating a multiple dependent drop-down list is a matter of minutes, if not seconds.

In pre-dynamic Excel, you will have to do it the long old-fashioned way as described in Creating a dependent drop down in Excel 2019, 2016 and earlier.

An additional complication is that the fruit names are not grouped but scattered across the column.

The goal is to put the unique fruit names in the first drop-down and depending on the users selection show the relevant exporters in the second drop-down.

This can be done by using the UNIQUE function in its simplest form - supply the fruit list for the first argument ( array ) and omit the remaining optional arguments as their defaults work just fine for us.

For this, type the hash tag right after the cell reference, like this: G3.

This can be done with the help of another dynamic array function called FILTER.

But this time, reference the spill range returned by the FILTER function: H3.

Or you can include a few blank cells in your formulas as demonstrated in this example.

But what if you need a multi-level hierarchy, i.e.

Is that possible Yes, you can set up any number of dependent lists (a reasonable number, of course:).

The only difference is the spill range reference you enter in the Source box.

So, you select E3, click Data Validation, and supply this reference: E8.

But you want only unique names in your dropdown list, right To leave out all duplicate occurrences, wrap the UNIQUE function around your FILTER formula, and enter this updated formula in F8.

The first criterion checks the entire fruit list against the value selected in the 1 st dropdown (A3:A15E3) while the second criterion tests the list of exporters against the selection in the 2 nd dropdown (B3:B15F3).

Will the dropdown list update automatically If your original data is formatted as Excel table, then yes, a dynamic drop down list discussed in the previous examples will expand automatically without any effort on your side because Excel tables are expandable by their nature.

The Data Validation rules do not require any adjustments at all.

Additionally, we embed the FILTER function into UNIQUE to extract unique values without blanks.

Unlike the traditional methods, this approach works perfectly for single and multi-word entries and takes care of any blank cells.

Thank you for reading and hope to see you on our blog next week.

Compose your response just once, save it as a template and reuse whenever you want.

I dont know how to thank you enough for your Excel add-ins Jennifer Morningstar Anybody who experiences it, is bound to love it Kumar Nepa AbleBits suite has really helped me when I was in a crunch Nelda Fink I have enjoyed every bit of it and time am using it Christian Onyekachi Nwosu Its the best 100 weve ever spent Mike Cavanagh I love the program, and I cant imagine using Excel without it Robert Madsen One word WOW Dave Brown Love the products David Johnston It is like having an expert at my shoulder helping me Linda Shakespeare Your software really helps make my job easier Jeannie C.

Excel Dropdown List Software Really Helps

Excel For Mac Dropdown List