Others

How to Create Dependent Data Validation Drop-Down Lists in Excel

Maira Mehtab Sep 21, 2026 871 views

Question details

The user needs to set up a secondary drop-down list that dynamically updates its available options based on the value selected in a primary drop-down list.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Creating an interactive data entry form where choices are categorized, such as selecting a clothing size in one cell and filtering corresponding available colors in the next.
Observed behavior: The user wants the second drop-down list to exclusively display data items associated with the category selected in the first cell, rather than displaying all possible options.

Before you start

Organize your source data into distinct columns before beginning. Ensure that the headers for your secondary options exactly match the items you plan to list in your primary drop-down menu.

Solution 1Recommended

Use Named Ranges and the INDIRECT Function

This is the most reliable method for creating linked data validation lists. By naming your secondary data groups to match your primary selections, the INDIRECT function can dynamically retrieve the correct list.

The INDIRECT function turns text into a valid cell reference. When combined with Named Ranges, it allows the secondary Data Validation list to look at the first cell's text and pull the range that shares that exact name.

Create the primary drop-down list

Select the cell where you want your first drop-down. Go to the Data tab, click Data Validation, choose 'List' under the Allow criteria, and select the range containing your main categories (e.g., Small, Medium, Large).

Define names for the secondary data

Highlight the list of items for your first sub-category (e.g., the specific colors for 'Small'). Go to the Formulas tab, click Define Name, and type the exact name of the primary category (e.g., Small) into the Name box. Repeat this process for all other sub-categories.

Apply validation to the secondary cell

Select the cell intended for your dependent drop-down list. Go back to Data > Data Validation, and select 'List' from the Allow drop-down menu.

Enter the INDIRECT formula

In the Source box, type =INDIRECT(A1), making sure to replace 'A1' with the actual cell reference of your primary drop-down list. Click OK to apply the rule.

Handling Spaces in Named Ranges: Named ranges cannot contain spaces or special characters like hyphens (e.g., 'X-Large' or 'Dark Blue'). Replace these with underscores when naming your ranges (e.g., 'X_Large'). Then, modify your validation formula to: =INDIRECT(SUBSTITUTE(A1, "-", "_")).

WPS Spreadsheet

Create Dynamic Drop-Down Lists Easily in WPS Spreadsheet

WPS Spreadsheet provides a highly compatible and intuitive interface for building dynamic forms and applying advanced data validation rules, allowing you to create dependent drop-down menus seamlessly.

1. Organize your data: Open your document in WPS Spreadsheet and type out your main categories and their corresponding sub-category lists in separate columns.
2. Define your named ranges: Select each sub-category list, navigate to the Formulas tab, click Name Manager, and assign a name that exactly matches the corresponding main category.
3. Set up the primary list: Click your target cell, navigate to Data > Validation, choose List, and select your main categories as the source.
4. Set up the dependent list: Select the adjacent cell, go to Data > Validation, choose List, and input the formula =INDIRECT(Cell_Reference) pointing to your first drop-down.

Fully compatible with Microsoft Excel (.xlsx) formats and complex data validation rules.Easily manage all your data groups using the built-in Name Manager.Lightweight, fast, and features a familiar tabbed interface for efficient data entry.Completely free to use for everyday office and spreadsheet tasks.

Frequently Asked Questions

Why does Excel show an error when I enter the INDIRECT formula in Data Validation?

This typically occurs if the primary drop-down cell is empty at the time you are creating the secondary rule. Excel cannot evaluate the INDIRECT reference if there is no text. You can safely click 'Yes' to continue, and it will work once a selection is made in the first cell.

Can I link a third drop-down list to the second one?

Yes, the process is identical. You create named ranges based on the items in the second drop-down list, and then use the INDIRECT formula in the third drop-down's Data Validation source, referencing the cell of the second drop-down.

How do I fix the dependent drop-down list not updating when I change the first selection?

By default, the second cell retains its previous value even if the first cell changes. To clear it automatically, you would need to use a VBA macro using the Worksheet_Change event to clear the contents of the secondary cell whenever the primary cell is modified.

Excel & Data Problems