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:
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 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.
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.
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.
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..!!
Leave a Reply