Cascading or dependent drop-down lists using Data Validation tool in Excel


If you are thinking of bringing interactivity to the reports in Excel, you may have come across the need for having a simple drop-down list or a more complex cascading drop-down list (i.e., a drop-down list whose values are dependent on the value already selected in another drop-down).

Example Scenario: For example, say, you have a list of categories and sub-categories of some food items as below:

Cascading drop-down lists of categories and sub-categories example in Excel.
Cascading or Dependent Drop-Down Lists in Excel

Challenge: From the above example, when a category is selected, say, “Fresh Fruits”, corresponding list of fresh fruits (i.e., “Apples”, “Oranges”, “Mangoes” and “Bananas”) should only be made available in the sub-categories drop-down list for further selection. Similarly, when “Vegetables” is selected in “Categories” drop-down, only corresponding list of vegetables (i.e., “Spinach”, “Potatoes” and “Tomatoes”) should be made available in “Sub-Categories” drop-down list. The same continues for the rest of the categories too, like below:

Cascading drop-down lists of categories and sub-categories example using Data Validation tool in Excel.

Cascading or Dependent Drop-Down Lists output using Data Validation

Solution: One way of solving this challenge is by using “Named Ranges”, built-in function named “INDIRECT” and “Data Validation Tool” in Excel.

Step:1. Extract the list of unique categories and each of the lists of unique sub-categories into a sheet (preferably a separate sheet from the one that is expected to have the drop-downs).

Step: 2. Define names for each of the lists created in step:1: Name the list of categories as “Categories”. Name the lists of sub-categories with their corresponding value from the list of categories. i.e., name range having the list of vegetables as “Vegetables”, range having the list of “Fresh Fruits” as “FreshFruits” (note that the space between the words is removed as spaces are not allowed in the names of named ranges). Continue the same for the rest of the lists of sub-categories.

Naming ranges of lists for to be populated in cascading drop down lists in Excel
Naming ranges of lists for to be populated in cascading drop down lists in Excel

Step:3. Building the drop-down list for Categories: Select the cell in the desired sheet where you need the “Categories” drop-down. Then,
invoke “Data Validation” dialogue from “Data Tools” group in “Data” ribbon tab. In the “Settings” tab of the dialogue, select “List” under “Validation criteria” drop-down to refer to a list of values that are to be shown in the desired drop-down list; then type “=Categories” under “Source” to show the list of values in the range of cells that have been given the name “Categories” in step:1.

Independent Drop Down in Cascading Drop Down Lists in Excel
Independent Drop Down in Cascading Drop Down Lists in Excel

Step:4. Building the cascading drop-down list for each of the Sub-Categories: Select the cell offset by one or two cells below the categories drop-down in the same sheet where “Categories” drop-down list is built. Then, invoke “Data Validation” dialogue from “Data Tools” group in “Data” ribbon tab. in “Settings” tab of the dialogue, select “List” under “Validation criteria” drop-down to refer to a list of values that are to be shown in the drop-down list;

Now to show only the sub-categories that fall under a selected category, you just need mention the name of the range that has only the said sub-categories. i.e., you can just specify the value selected in the “Categories” list as is, as you have already ensured to name the ranges related to the sub-categories with the corresponding values from “Categories” drop-down in Step:2. This can be done by simply referencing the cell that receives the value when a category is selected.

However, such cell reference only returns textual value. In order let Excel treat such textual value to be a named range, you need to enclose it in “INDIRECT” function.

Also remember that we removed any embedded spaces in the Category values when they are used as names of the ranges containing the corresponding list of sub-categories. In this example, name for range containing the sub-categories belonging to “Fresh Fruits” category has been given after removing the embedded space as “FreshFruits”. Similarly, “Dry Fruits” turned to become “DryFruits”. This means, you need to remove any such spaces in the “Category” values before treating them as named ranges using “SUBSTITUTE” function.

Dependent Drop Down in Cascading Drop Down Lists in Excel
Dependent Drop Down in Cascading Drop Down Lists in Excel

So, if the cell that receives the selected “Category” in your sheet is “K15” then type “=INDIRECT(SUBSTITUTE($K$15, ” “, “”)) under “Source” in “Settings” tab of the “Data Validation” dialogue invoked for “Sub-Categories” drop-down.

Now just click “Ok” in “Data Validation” dialogue and see the magic work..!!

Happy learning..!!

Your valuable comments are highly honoured..!!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Up ↑