Excel Data Validation Based on Another Cell: How It Works

How-to basics · Updated November 19, 2026

Yes. In Excel for Microsoft 365 on Windows, you can make a dropdown or validation rule depend on another cell by using named ranges with INDIRECT, or by writing a custom validation formula. For a dependent dropdown, name each option list to match its category; with the category in A2 and the dependent dropdown in B2, set Data > Data Validation > Allow: List and use =INDIRECT(SUBSTITUTE($A2," ","_")) as the source.

What dependent data validation means

Data validation controls what users can enter in a cell. A basic list dropdown always offers the same options. A dependent dropdown changes its options according to a value elsewhere in the row: choosing a department might determine which teams appear in the next cell, for example. The dependency can also be a rule rather than a list. A date in one cell could determine the acceptable date range in another, or a number in one cell could set a limit for a second entry.

In Excel, the validation rule is attached to the cell that receives the entry. Its source or formula can refer to other cells, and relative references let the rule adjust when applied to additional rows. That makes it useful for forms, order sheets, surveys, and other workbooks where entries must follow a set of related choices.

Build a dependent dropdown with named ranges

This is a dependable method for a compact category-and-choice setup. Suppose A2 contains a category and B2 should list its related choices. Put each category’s choices in a separate range, then give each range a name matching the category. For example, a list of hot drinks might be named Hot_Drinks. The underscore matters because Excel names cannot contain spaces.

  1. Enter the category choices in a source area, with each category’s dependent choices grouped in their own column or range.
  2. Select the choices for one category. Choose Formulas > Define Name, enter a valid name such as Hot_Drinks, and confirm that the name refers to the selected cells. Repeat for each category.
  3. Select the category-entry cells, such as A2:A100, and use Data > Data Validation. Choose List and set the source to the cells containing the category names.
  4. Select B2, open Data > Data Validation, choose List, and enter the source formula below. Select B2 and fill or copy the validation down the other rows as needed.

For this formula, the category is in A2, the dependent choice is in B2, and the named range for a category replaces spaces with underscores. The formula returns the list whose name matches the category in that row:

=INDIRECT(SUBSTITUTE($A2," ","_"))

The dollar sign fixes the category column but leaves the row relative. When the validation is copied from B2 to B3, Excel can use A3 instead. If your category names already match the range names exactly and contain no spaces, the source can be the simpler =INDIRECT($A2). Category names containing punctuation, starting with a number, or otherwise unsuitable for range names need a different naming scheme and a formula that maps the displayed category to a valid range name.

Use a formula to validate a value

A dropdown is not the only form of dependent validation. A custom rule can accept an entry only when it meets a condition involving another cell. In the same layout, where A2 holds the category, B2 holds the entry, and each category’s allowed values are stored in the named range described above, choose Data > Data Validation, set Allow to Custom, and enter this formula:

=COUNTIF(INDIRECT(SUBSTITUTE($A2," ","_")),B2)>0

The rule is TRUE when B2 appears in the named range selected by A2. For instance, if A2 contains Hot Drinks and B2 contains Tea, the rule accepts Tea only if Tea is in the Hot_Drinks range. The same row-relative reference principle applies when the rule is copied down. Custom validation is useful when users can type a value rather than choose from a dropdown, though a list is usually clearer when the allowed choices should be visible.

Other custom rules can compare cells directly. With a starting value in A2 and an ending value in B2, a rule on B2 could require the ending value to be no earlier than the start. For any custom rule, check which cell is active when you create it and make sure its references match the top-left cell of the selected range. A misplaced absolute or relative reference can make an otherwise sensible rule check the wrong row.

Choose between named ranges and other approaches

Named ranges are often easiest when there are a modest number of categories and the lists change infrequently. They are straightforward to inspect and maintain, and the validation source remains short. Their main drawback is maintenance: adding a category means creating another named range, and renaming a category may require changing its range name or the mapping formula.

If the source choices are stored in a normalized table with category and choice columns, a formula-driven helper area can be more flexible as the data grows. Microsoft 365 includes dynamic-array functions such as FILTER, but data validation’s List source is designed around a range reference or a defined name. In practice, a helper range and a defined name that refers to the relevant results can connect a dynamic formula to the dropdown. This arrangement takes more setup, especially when each row needs a different helper result, so test it in the workbook and Excel version that will be used.

For a very small form, separate lists or a simple instruction to choose from a fixed list may be enough. For a complex application with many relationships, a table-driven form or a database-backed workflow may be easier to maintain than a large collection of named ranges. The right choice depends on how often categories change, how many users edit the workbook, and whether the list must expand automatically.

Make the setup easier to maintain

Keep source lists organized on a dedicated worksheet and use clear labels. You can hide that worksheet to reduce clutter, but hiding it is not a security measure: workbook users may still be able to reveal or inspect the data. If categories or choices will be added often, make the source data easy to extend and periodically check that named ranges include the new entries. A named range that stops one row too early will make the dropdown appear incomplete.

Set an informative input message or error alert in the Data Validation dialog. The input message can explain what to select, while a Stop-style error alert can reject a typed value that fails the rule. Decide whether blank entries should be allowed; the Ignore blank setting affects how Excel treats empty cells, but it does not automatically clear a dependent cell when its category changes.

That last point matters in data-entry forms. If a user first chooses one category and selects a related option, then changes the category, Excel does not necessarily erase the old dependent entry. The old value may no longer belong to the new list. Review dependent cells after changing a parent choice, or design the workflow to make that check obvious.

Common problems and how to fix them

  • The dropdown is empty or shows an error: Check that the category text exactly matches the corresponding defined name after the formula’s substitutions. Also check that the named range exists and refers to cells containing the intended choices.
  • A category has spaces: Use a consistent naming conversion, such as replacing spaces with underscores, in both the range names and the source formula. Other characters may also need handling; SUBSTITUTE for spaces alone does not sanitize every possible label.
  • Every row uses the same category: Check whether the row reference was locked. In a rule copied downward, $A2 fixes the column while allowing the row to change; $A$2 would keep pointing at A2.
  • The new option does not appear: Confirm the defined name includes the new source cell. A fixed range does not necessarily grow when data is added beneath it.
  • Pasted data ignores the rule: Data validation is intended to guide entry, not to secure or sanitize a workbook. Pasting, importing, or other editing methods can result in invalid data or remove validation, so inspect important data separately.

When diagnosing a problem, select the cell and reopen Data > Data Validation to inspect its rule. Check a working row and a failing row side by side, including the category text, the exact named-range spelling, and the rule’s references. If the workbook is shared, verify the behavior in the version and platform other users actually use.

Excel for Mac and Excel for the web

The instructions above target Excel for Microsoft 365 on Windows. Excel for Mac also provides data validation and named ranges, though some dialog details and ribbon placement can differ by release. Look for Data Validation on the Data tab and use the formula and named-range logic described here; confirm the result in the Mac version used by the workbook’s editors.

Excel for the web supports many validation tasks, but workbook features and editing dialogs can differ from the desktop application. If the web interface does not let you create or adjust a particular rule as expected, open the workbook in desktop Excel to set it up, then check that the dropdown behaves as intended in the browser. Avoid assuming that a rule created in one environment will be equally easy to maintain in every other environment.

Frequently Asked Questions

Can Excel data validation depend on another cell?
Yes. A validation list can use a formula that refers to another cell, and a Custom rule can test a value against another cell or range. Named ranges combined with INDIRECT are a common method for dependent dropdowns.

Can I make a dropdown change when I select a different category?
Yes. Create a named range for each category’s choices, then use a List validation source that resolves to the range named by the category cell. Excel updates the available dropdown choices when the category changes, but it may leave an old dependent value in place.

Why does INDIRECT return an error in my dropdown?
The text produced by the formula must resolve to a valid defined name or reference. Check spelling, spaces, punctuation, and whether the name exists in the workbook. Also make sure the category cell is not blank when you test the dependent dropdown.

Does data validation stop users from pasting invalid values?
No. Validation is a useful entry control, not a guarantee that every cell remains valid. Pasting or importing data can bypass the normal dropdown interaction, so use separate checks when the correctness of existing entries matters.

Will changing the first dropdown clear the second one?
Not by itself. If the dependent cell already contains a choice from the previous category, Excel does not automatically remove it when the category changes. Users should recheck that cell, or the workbook needs additional automation to clear stale choices.

Can I use spaces in category names?
Displayed category labels can contain spaces, but named ranges cannot. A formula such as the one above replaces spaces with underscores, so a label like Hot Drinks can correspond to a defined name like Hot_Drinks. Other characters may require additional mapping.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.