Creating Effective Cascading Dropdowns in Excel 2021

Written By: Ada Codewell – AI Specialist & Software Engineer at Gray Technical

Have you ever struggled with setting up cascading dropdown lists in Excel? You’re not alone. Many users find this feature challenging to implement, especially when using the latest versions like Excel 2021. Cascading dropdowns can be incredibly useful for data validation and making forms more interactive. In this guide, we’ll walk you through why creating these dropdowns is essential, common pitfalls, and a step-by-step solution.

Why This Problem Happens

Cascading dropdown lists allow users to select from a list of options that change based on the selection made in another dropdown. For instance, if you choose “USA” from a country dropdown, the state dropdown would then only show U.S. states. This feature enhances data accuracy and user experience but can be tricky due to Excel’s limitations with dynamic dependencies.

One of the main reasons users struggle is because Excel doesn’t have built-in support for cascading dropdowns like some other software (e.g., Access). It requires a combination of Data Validation, named ranges, and sometimes VBA macros to achieve this functionality. Additionally, many tutorials are outdated or don’t work with newer versions like Excel 2021.

Step-by-Step Solution

Let’s create cascading dropdowns using a simple example: selecting a continent and then a country within that continent.

Setting Up the Data

1. Create two tables in your worksheet:
   • Table 1 (Continents): Column A for Continent IDs, Column B for Continent Names

     A1: ID | B1: Name
                      A2: 1   | B2: North America
                      A3: 2   | B3: Europe

   • Table 2 (Countries): Columns C, D for Country IDs and Names, Column E for Continent ID

     C1: ID | D1: Name      | E1: ContinentID
                      C2: 1   | D2: USA        | E2: 1
                      C3: 2   | D3: Canada     | E3: 1
                      C4: 3   | D4: France     | E4: 2
                      C5: 4   | D5: Germany    | E5: 2

2. Name the ranges for easier reference:
   • Select A1:A3, go to Formulas > Define Name and name it “ContinentIDs”
   • Select B1:B3, name it “Continents”
   • Select C1:C5, name it “CountryIDs”
   • Select D1:D5, name it “Countries”

Creating the Dropdowns

3. Create a user form area:
   • In cell G1, type “Select Continent” and in H1, use Data Validation to create a dropdown list using the range named “Continents”
   • In cell G2, type “Select Country” (we’ll set up its validation later)

Making the Second Dropdown Dynamic

4. To make the country dropdown depend on the continent selection:
   • In cell H2, use a formula to filter countries based on the selected continent. Assuming G1 is your Continent dropdown:

   =FILTER(Countries, ISNUMBER(SEARCH(H1, Continents)))

5. Now set up Data Validation for H2:
   • Select cell H2, go to Data > Data Validation
   • Set “Allow” to List and enter the formula from step 4 as Source (remove curly braces if they appear)

Testing the Cascading Dropdowns

6. Select different continents in cell H1 and observe how the available countries in cell H2 change accordingly.

Alternatively, you could use CelTools which has built-in features for cascading dropdowns that simplify this process significantly:

Advanced Variation: Using VBA for More Complex Scenarios

For larger datasets or more complex dependencies, consider using VBA (Visual Basic for Applications). This approach offers greater flexibility and efficiency.

1. Press Alt + F11 to open the Visual Basic Editor, then insert a new module by right-clicking any existing module or the workbook name in the Project Explorer and selecting Insert > Module
2. Copy and paste this VBA code into the module:

   Sub CreateCascadingDropdown()
       Dim ws As Worksheet
       Set ws = ThisWorkbook.Sheets("Sheet1")
   
       ' Clear existing validations
       ws.Cells(1, 8).Validation.Delete
       ws.Cells(2, 8).Validation.Delete
   
       ' Add validation for Continent dropdown
       With ws.Range("H1").Validation
           .Delete
           .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="=Continents"
           .IgnoreBlank = True
           .InCellDropdown = True
       End With
   
       ' Add validation for Country dropdown based on selected Continent
       ws.Range("H2").Validation.Add(Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
           Formula1:="=OFFSET(Countries,MATCH(H1,Continents,0)-1,0,COUNTA(OFFSET(Countries,MATCH(H1,Continents,0)-1,0,COUNTA(Countries),1)))")
   
   End Sub

3. Close the VBA editor and run the macro by pressing Alt + F8, selecting “CreateCascadingDropdown”, and clicking Run.

Common Mistakes or Misconceptions

The most common mistake is not correctly referencing named ranges in Data Validation. Always ensure that your range names are accurate and match the data they reference. Another pitfall is forgetting to refresh the dropdowns after changing the source data.

For complex datasets, CelTools can streamline this process by automating many of these steps:

Conclusion: Practical Benefits and Applicability

Cascading dropdowns significantly improve data entry accuracy and user experience in Excel. By following this guide, you’ve learned how to create dynamic dependencies between dropdown lists, making your spreadsheets more interactive and efficient.

This technique can be applied to various scenarios, such as creating product catalogs where selecting a category filters the available products or designing project management forms that display relevant tasks based on selected projects.

Remember: While manual setup is possible, tools like CelTools offer powerful automation features for more complex needs. Always consider your specific requirements and dataset size when deciding between manual methods and automated solutions.