Excel: Create Cascading Drop-Down Lists for Better Data Validation and Quality Control

When using Microsoft Excel to track data, one challenge is maintaining data quality when multiple people collaborate on the same workbook. Excel’s data validation feature helps by limiting data input to pre-defined options in a list. This works well for individual cells, but if one column depends on the value of another, you’ll need a more advanced solution. This guide explains how to create cascading drop-down lists in Excel using data validation and named ranges.

Instructions

Step 1: Define the Reference Data

We’ll assume you are collecting data with organization and department attributes. In this example, an organization is the parent of one or more departments. Set up your reference data on a separate worksheet, such as REFERENCES, as follows:

• Column A (REF_ORGANIZATIONS) lists valid organizations for the organization drop-down list. In the sample data, we have three organizations named Organization 1, Organization 2, and Organization 3.
• Column B (REF_ORGANIZATION_TO_DEPARTMENT_MAPPINGS) lists the names of three ranges that map departments to an organization.
• Column D (REF_ORGANIZATION_1_DEPARTMENTS) lists valid departments for Organization 1.
• Column E (REF_ORGANIZATION_2_DEPARTMENTS) lists valid departments for Organization 2.
• Column F (REF_ORGANIZATION_3_DEPARTMENTS) lists valid departments for Organization 3.

Step 2: Define the Named Ranges

Now, define the named ranges referring to these reference lists.

1. From the Formulas ribbon, click Name Manager.
2. Click the New... button.
3. The New Name dialog box is displayed.
4. In the Name field, enter the REF_ name specified in Step 1.
5. In the Refers to field, enter or select the cell range containing the associated reference data.
6. Click the OK button.
7. Repeat steps for each named range.

NOTE: The key range, REF_ORGANIZATION_TO_DEPARTMENT_MAPPINGS, links departments to an organization. This named range must include the values in both Column A and Column B in its Refers to field. Also, ensure the values in Column B match the department named ranges. This link will be used in a VLOOKUP and INDIRECT formula in a subsequent step.

Step 3: Define the Organization Data Validation (Parent Column)

On the data collection worksheet, such as DATA, set up data validation for the organization column (Column A). This will display a drop-down list of valid organizations.

1. Highlight the organization column (Column A).
2. From the Data ribbon, click Data Validation.
3. The Data Validation dialog box is displayed.
4. In the Allow dropdown, select List.
5. In the Source field, enter =REF_ORGANIZATIONS.
6. Enable the Ignore blank checkbox.
7. Enable the In-cell dropdown checkbox.
8. Click the OK button.

Step 4: Define the Department Data Validation (Child Column)

On the data collection worksheet, in this example DATA, create the data validation rule for the department column (Column B) using the following formula. This formula uses VLOOKUP to find the correct department range based on the selected organization and uses INDIRECT to reference that range.

1. Highlight the department column (Column B).
2. From the Data ribbon, click Data Validation.
3. The Data Validation dialog box is displayed.
4. In the Allow dropdown, select List.
5. In the Source field, enter the formula.
6. Enable the Ignore blank checkbox.
7. Enable the In-cell dropdown checkbox.
8. Click the OK button.
9. You might see the error message The Source currently evaluates to an error. Do you want to continue? — click the Yes button to proceed.

=INDIRECT(VLOOKUP(A2,REF_ORGANIZATION_TO_DEPARTMENT_MAPPINGS,2,FALSE))

Step 5: Test the Cascading Drop-down Lists

After setting up data validation, test the cascading drop-down lists. Selecting an organization will filter the department list to only show relevant options for that organization.

Step 6: Macro to Validate Parent/Dependent Relationship

To prevent errors, such as selecting a department that no longer matches the chosen organization, you can add a macro to update the department column when the organization selection changes.

Save the file as a macro-enabled workbook and add the following code to the worksheet. The macro will clear the department entry if it is no longer valid. Adjust the code based on your specific columns.

1. From the Developer ribbon, click Visual Basic.
2. The Microsoft Visual Basic for Applications window is displayed.
3. Select the data collection worksheet, in this case DATA.
4. Add the following code.
5. Close the window and return to Excel.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo exitHandler

    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With

    If Target.Column = 1 Then
        If Target.Validation.Type = xlValidateList Then
            If Not Target.Offset(0, 1).Validation.Value Then
                Target.Offset(0, 1).ClearContents
            End If
        End If
    End If

exitHandler:
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With
End Sub

Results

After selecting an organization from the drop-down list, the department list shows only departments linked to that organization. Choosing a different organization triggers the macro, clearing the invalid department selection.

Summary

By setting up cascading drop-down lists in Excel, you can maintain accurate and consistent data entry in multi-user workbooks. This process uses data validation, named ranges, and formulas like VLOOKUP and INDIRECT to create dynamic, dependent selections. With the addition of a macro, you can further ensure data integrity by automatically correcting any mismatches between parent and child values.