Dependent Dropdown List in Excel: Set Up Each Column

How-to basics · Updated November 19, 2026

Yes. In Excel for Microsoft 365, you can create a dependent dropdown in one column that changes according to the selection in another column. A reliable setup uses Data Validation lists and named ranges; for example, choosing “Drinks” in column A can make the matching drink choices appear in column B on that same row.

What a dependent dropdown does

A regular dropdown offers the same list wherever it appears. A dependent dropdown uses an earlier choice to determine which options are available next. This is useful in forms and trackers where choices naturally fall into levels: department and employee, product type and product, or country and region.

The word “column” can mean two different things here. One column may contain the parent choices and another the dependent choices, with the relationship working separately on every row. Or a person may want one dropdown column whose choices change based on a selection somewhere else. The method below addresses the common row-by-row case: select a category in A2, then choose a matching item in B2; repeat in A3 and B3, and so on.

Excel does not automatically infer which lists belong together. You provide the options and define the relationship. Once set up, the dropdowns help keep entries consistent and reduce spelling variations that can make sorting, filtering, and summaries harder.

Plan the worksheet layout

First, place the entry area in one part of the worksheet and the source lists in another. For this example, column A will hold categories and column B will hold the associated choice. Put the category list and its matching sublists in columns G through K:

  • G2:G4 contains Fruits, Drinks, and Office Supplies.
  • I2:I4 contains fruit choices such as Apple, Pear, and Orange.
  • J2:J4 contains drink choices such as Water, Tea, and Coffee.
  • K2:K4 contains office choices such as Pens, Paper, and Folders.

Put a heading above each source list if helpful, but keep the actual items in a clean, contiguous range. Avoid blank cells within a list, since they can appear as empty choices. The example uses three items per group only to make the layout easy to see; each list can contain more or fewer entries.

There is no requirement to keep source lists on the same sheet as the form. You can put them on a separate worksheet to make the entry area tidier. If the source lists are on another sheet, named ranges are especially useful because they give the validation rule a clear reference without relying on a direct cross-sheet range.

Create named ranges for the lists

A named range gives a group of cells a readable name that Excel can use in formulas and validation rules. Create one for the parent categories and one for each sublist. In Microsoft 365 for Windows, select Formulas > Define Name to make a name for the selected cells. Set the scope to Workbook so the name is available throughout the workbook.

  1. Select G2:G4, choose Formulas > Define Name, and name the range Categories.
  2. Select I2:I4 and define the name Fruits.
  3. Select J2:J4 and define the name Drinks.
  4. Select K2:K4 and define the name Office_Supplies.

Names cannot contain spaces, so the name for Office Supplies uses an underscore. In this example, each category name matches its dependent range name, except that spaces in the category are replaced by underscores. Names also need to follow Excel’s naming rules; avoid punctuation and names that look like cell references. If your real labels include other punctuation, use simpler names and a deliberate mapping rather than assuming spaces are the only difference.

When defining each name, check the “Refers to” address. For example, the Fruits name should refer to the cells containing Apple, Pear, and Orange—not to its heading or to the category list. A wrong range reference is a common reason a dropdown shows the wrong items.

Add the category dropdown

To add the parent dropdown, select A2:A100, or the entry rows you actually expect to use. In Excel for Microsoft 365 on Windows, choose Data > Data Validation. On the Settings tab, set Allow to List and enter the named range as the source. Assuming the workbook-level range containing the category choices is named Categories, enter =Categories in the Source box. Leave “In-cell dropdown” selected, then choose OK.

Test a cell in column A. It should offer Fruits, Drinks, and Office Supplies. If it does not, confirm that the name exists in Formulas > Name Manager and that its “Refers to” address points to the intended cells. The Data Validation dialog is also available in Excel for Mac, although ribbon placement can vary slightly by release. Excel for the web can work with many existing validation lists, but the desktop app is a practical choice for creating and checking named-range setups.

Make the dependent dropdown work on every row

Now apply a list validation rule to the dependent cells. Select B2:B100, then choose Data > Data Validation and set Allow to List. Assuming each row’s category is in column A, the first dependent dropdown is on row 2, and the named ranges follow the category labels (with spaces converted to underscores), enter =INDIRECT(SUBSTITUTE($A2," ","_")) in the Source box. Select OK.

The dollar sign fixes the parent column at A, while leaving the row number relative. That matters when the same rule is applied down column B: B2 looks at A2, B3 looks at A3, and B4 looks at A4. If you are setting up a different pair of columns, change the A reference to the parent-choice column and use the first row of your selected range. For instance, if the parent choices begin in D5, the reference should point to D5 rather than A2.

Try selecting Drinks in A2, then open the dropdown in B2; it should offer Water, Tea, and Coffee. Select Office Supplies in A3 and open B3; it should offer Pens, Paper, and Folders. This row-specific behavior is what makes the setup useful in a data-entry column rather than just for one isolated pair of cells.

Choose a parent category before opening its dependent dropdown. The dependent rule uses the text in the parent cell to find a named range. If the parent is blank, or if its text does not correspond to a name, Excel cannot find the intended list.

How the rule connects a choice to a list

The validation source combines two functions. SUBSTITUTE changes spaces in the parent text to underscores, so Office Supplies becomes Office_Supplies. INDIRECT then interprets the resulting text as a reference to the named range. When A2 contains Fruits, the reference resolves to the range named Fruits; when A2 contains Drinks, it resolves to Drinks.

This is a compact method, but it depends on consistent naming. A category called “Cold Drinks” would require a named range called Cold_Drinks. Labels such as “R&D” or “Home / Garden” will not be repaired by replacing spaces alone. For categories with punctuation, you can choose safe names that do not contain punctuation and build a more explicit lookup or helper-list design, rather than making the category text double as the range name.

INDIRECT is a volatile function, meaning Excel may recalculate it more often than a typical reference when the workbook changes. For a modest data-entry sheet this is usually manageable. In very large workbooks with many dependent validation cells, keep the design as simple as practical and consider whether a helper-list or form-based approach is more suitable.

Keep the lists accurate as the workbook changes

Named ranges created for fixed cell addresses do not necessarily expand when you add items below those addresses. If you add more fruits beyond I4, for example, edit the Fruits name in Formulas > Name Manager so its reference includes the new entries. Also update the parent Categories name if you add a category. Otherwise, the new options may not appear in the dropdown.

For a workbook that changes regularly, make a simple maintenance routine part of the design:

  • Keep each source list in one continuous block and exclude its heading from the named range.
  • When adding a category, create its sublist, define the matching named range, and include the category in Categories.
  • When adding entries to a sublist, confirm the named range covers the added cells.
  • Test one row for each category after changing the source data.

You can hide a worksheet that contains source lists to keep the working area uncluttered, but hiding it does not protect the workbook from accidental changes. If other people will edit the file, explain where the source lists live and which names must be updated. Worksheet protection can help prevent unintended edits, but it should be tested with the intended entry workflow.

Common problems and practical fixes

  • The list is empty or produces an error: Check that the parent cell is filled and that its text matches a named range. Check spelling, underscores, and the name’s scope in Name Manager.
  • Every row shows the same options: The validation formula may use an absolute row reference, such as $A$2. For a rule applied down from row 2, the row should remain relative as in $A2.
  • The dropdown includes a blank choice: Remove blank cells from the named range or adjust the range to include only populated choices.
  • A changed category leaves an old item behind: Data Validation controls what can be selected; it does not automatically erase a value that was already entered. If A2 changes from Fruits to Drinks, check B2 and clear it if the old fruit is no longer valid.
  • New list items do not appear: Expand the relevant named range. A source range that still ends at row 4 will not include an item added in row 5.
  • The rule is missing from newly added rows: Apply or copy the validation rule to the new cells and verify the relative row reference. Do not assume that extending a formatted table will always extend every validation setting as intended.

When copying validation, inspect the destination rule in Data > Data Validation. A copied cell can carry an unexpected reference if it was originally created from a different starting row. Testing both the first and a later row is a quick way to catch a rule that points every cell at the same parent.

Alternatives and choosing a method

Named ranges with INDIRECT are a good fit when the number of categories is manageable and the labels can be matched cleanly to safe range names. They are easy to understand once defined, and the same row-relative validation rule can serve many entry rows. The trade-off is maintenance: every category needs a named range, and range names need to remain synchronized with labels.

Another approach is to generate the relevant options in helper cells with formulas, then point Data Validation at those results. Microsoft 365 has dynamic-array functions that can build flexible lists, but arranging a separate helper result for each entry row and referencing spilled results in validation adds complexity. It is worth considering when lists change frequently or when category names cannot be mapped neatly to range names, but test the workbook in the Excel versions your audience uses.

If users only need to choose from one combined list, a single standard dropdown is simpler and easier to maintain. If the workflow has several dependent levels, such as department, team, and employee, the same principle can be extended, but every level needs its own well-defined source and careful handling when an earlier choice changes. For a highly interactive form or rules that must automatically clear invalid downstream values, a form-based or scripted solution may be more appropriate than Data Validation alone.

Frequently Asked Questions

Can one dependent dropdown formula work down an entire Excel column?
Yes. Apply the validation rule to a suitable range, such as B2:B100, and use a relative row reference to the parent cell, such as $A2. Each row then checks the parent choice on its own row. Extend or copy the validation when you add more entry rows, and verify the reference in the new cells.

Can the category lists be on another worksheet?
Yes. Keep the source lists on another sheet and define workbook-level names that refer to those ranges. Use those names in the validation rules. This keeps the entry sheet clean while making the source lists easier to maintain.

Does changing the parent choice clear the dependent cell?
No. A validation dropdown limits new selections, but it does not automatically remove an existing value if that value no longer belongs to the newly selected category. The user must clear or replace it, or you need a separate automation approach.

Why does Office Supplies need an underscore in the named range?
Excel range names cannot contain spaces. The example’s SUBSTITUTE part converts the space in the category label to an underscore so the text can match the Office_Supplies range name. If labels contain other unsupported characters, use a different naming or mapping scheme.

Can I create dependent dropdowns in Excel for Mac?
Yes. Excel for Mac supports Data Validation and named ranges, and the same general setup applies. Ribbon layout may differ by release, so look for Data Validation under the Data tab and use Name Manager or the formula tools to define and inspect names.

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.