Part of the free Module 6: Data Validation · Lesson 11 of 14 · Full Excel course
Updated on 5 September 2026 · INDIRECT method works in Excel 365, 2021, 2019 and 2016; the FILTER method needs Excel 365 or Excel 2021.
A dependent drop-down list in Excel is a second list whose options change according to what was picked in the first list. Choose a Country in column A and the City list in column B shows only that country’s cities. You build it with Data Validation and either the INDIRECT function with named ranges (every Excel version) or the FILTER function (Excel 365 and 2021).
What a dependent drop-down list is and when to use it
Dependent lists, also called cascading or conditional drop-downs, stop users from picking combinations that do not exist, such as the city Manchester under the country India. They are standard in order forms (Category then Product), HR sheets (Department then Employee), and trackers (Region then Branch). The first list is a normal List rule from the drop-down lesson. The second list needs a source that changes with the first choice, and that is what the two methods below provide.
| Method | Excel versions | Setup effort | Best for |
|---|---|---|---|
| INDIRECT with named ranges | 2007 to 365 | One named range per parent item | Shared files, a fixed set of categories |
| FILTER with a spill range | 365 and 2021 | One formula, no names | Two-column lookup tables that change often |
Prepare the source data
Both methods start with a lookup sheet. Create a sheet named Lists and enter the parent items as column headings with their child items below each heading.
| A: India | B: UK | C: USA |
|---|---|---|
| Delhi | London | New York |
| Mumbai | Manchester | Chicago |
| Bengaluru | Leeds | Houston |
| Chennai | Boston |
Parent names must be legal range names for the INDIRECT method: no spaces, no leading numbers, no names that look like cell references such as Q1. If a real category has a space, use an underscore in both the heading and the name (New_Zealand) or switch to the FILTER method, which has no naming rules.
Method 1: INDIRECT with named ranges (all versions)
- On the Lists sheet select the headings and the items below them, for example A1:C5.
- Go to Formulas > Defined Names > Create from Selection, tick only Top row and click OK. Excel creates the names India, UK and USA, each covering the cells under its heading.
- Open Formulas > Name Manager to check them. Blank cells under a short list are included; trim each Refers to range or leave them and tick Ignore blank in the next step.
- On the entry sheet select the Country cells, for example A2:A50. Go to Data > Data Tools > Data Validation, set Allow to List and set Source to
=Lists!$A$1:$C$1. Click OK. - Select the City cells B2:B50 with B2 active. Open Data Validation, choose List and type the source
=INDIRECT(A2). Excel may warn that the source evaluates to an error because A2 is empty; click Yes to keep it. - Pick India in A2 and open the B2 arrow. Only Delhi, Mumbai, Bengaluru and Chennai appear.
INDIRECT(A2) turns the text India into a reference to the named range called India. Because A2 is a relative reference, row 3 reads A3, row 4 reads A4 and so on.
Method 2: FILTER with a two-column table (Excel 365 and 2021)
Keep the lookup data in two columns instead of a block: Country in column A and City in column B on the Lists sheet, one pair per row. This shape is easier to maintain and allows spaces in names.
- In an empty helper cell, for example Lists!E2, enter
=FILTER(Lists!$B$2:$B$100,Lists!$A$2:$A$100=Sheet1!A2,""). The result spills down to list every city for the country in Sheet1!A2. - Select the City cells on the entry sheet and open Data Validation. Choose List and set Source to
=Lists!$E$2#. The hash sign means the whole spill range. - Click OK and test. The drop-down now follows whatever is in A2.
This version serves one active row, which suits a form with a single entry line. For a long entry table where every row needs its own dependent list, use OFFSET or INDIRECT, or write the FILTER inside a LET so the helper column holds one formula per row. A simpler per-row approach is one helper column per row, hidden, each with its own FILTER formula and validation source pointing at that row’s spill.
Three levels: Country, State, City
With INDIRECT, add a third rule whose source is =INDIRECT(B2), where B2 holds the state. Every state then needs its own named range of cities. The pattern repeats for as many levels as the data supports; the only cost is the number of names to maintain. To avoid a stale value when the parent changes, add the custom rule =COUNTIF(INDIRECT(A2),B2)>0 on the City column, or use the VBA Worksheet_Change event to clear B2 when A2 changes.
Worked example: an order form
An order sheet needs Category in A and Product in B. The Lists sheet holds three categories.
| Laptops | Phones | Accessories |
|---|---|---|
| Ultrabook 14 | Model X | Mouse |
| Workstation 17 | Model Y | Keyboard |
| Chromebook | Model Z Lite | USB-C Hub |
- Select Lists!A1:C4, run Create from Selection with Top row. Names Laptops, Phones and Accessories exist.
- Category list on Orders!A2:A100: Source
=Lists!$A$1:$C$1. - Product list on Orders!B2:B100 with B2 active: Source
=INDIRECT(A2).
Result: pick Phones in A5 and B5 offers Model X, Model Y and Model Z Lite. Change A5 to Laptops and B5 still shows Model X until you reopen the arrow, which is why the stale-value check above is worth adding on real forms.
Tips and common mistakes
- Names must match the parent text exactly. A trailing space in the heading or the drop-down item breaks INDIRECT. Use TRIM on the source list.
- Spaces in names are not allowed. Use
=INDIRECT(SUBSTITUTE(A2," ","_"))so the user sees New Zealand while the name is New_Zealand. - Select from the top cell. The source
=INDIRECT(A2)is written for B2; if B10 was active the reference is off by eight rows. - Tick Ignore blank. Blank cells inside a named range then show as empty items rather than blocking the list.
- Convert lists to an Excel Table for growth. A named range set to a Table column, such as
=Table1[India], expands when rows are added. - FILTER is 365 and 2021 only. In older versions the formula returns #NAME? and the list is empty. Use INDIRECT for files shared with mixed versions.
- Clear the child when the parent changes. Validation does not remove an existing value, so add the COUNTIF custom rule or a short macro.
Errors and how to fix them
| Symptom | Cause | Fix |
|---|---|---|
| “The Source currently evaluates to an error” when saving the rule | Parent cell is empty at setup | Click Yes; the list works once a parent is chosen |
| Child list is empty | No named range matches the parent text, or a spelling or space mismatch | Check Name Manager; add TRIM or SUBSTITUTE in the INDIRECT |
| Child list shows old country’s cities | Value entered before the parent changed | Add =COUNTIF(INDIRECT(A2),B2)>0 as a custom rule or clear on change with VBA |
| #NAME? in the helper cell | FILTER not available in this Excel version | Use the INDIRECT method |
Source box rejects =Lists!$E$2# |
Excel 2019 or earlier does not understand the spill operator | Use INDIRECT or OFFSET with COUNTA |
Practice exercise
- Build the Country and City lists from the first table using named ranges and INDIRECT. Test all three countries.
- Add a third level: split India into two states with their own city lists and make a State column between Country and City.
- Rebuild the same two-level list with the FILTER method on a two-column lookup table (Excel 365 or 2021).
- Add the custom rule that flags a City no longer belonging to the chosen Country, then change a country and try to leave the old city in place.
- Add a new city to one country’s list and confirm the drop-down picks it up without editing the rule.
Key takeaways
- A dependent drop-down list changes its options based on the value in another cell.
- INDIRECT plus named ranges works in every Excel version; each parent item needs a name that matches it exactly.
- FILTER with a spill reference (
=E2#) needs no names and suits Excel 365 and 2021. - Write the child rule for the top cell with a relative parent reference so it copies down correctly.
- Validation does not clear an old child value when the parent changes; add a check rule if that matters.
Related lessons
- Data Validation course hub
- Create a drop-down list with Data Validation
- Dynamic drop-down list from an Excel Table or UNIQUE
- Searchable drop-down list
- INDIRECT function and SORT, FILTER and UNIQUE dynamic array functions
- Microsoft Support: INDIRECT function
Frequently asked questions
How do I make a drop-down list depend on another cell in Excel?
Create a normal List rule for the first cell. For the second cell, set Allow to List and use a source that changes with the first value: =INDIRECT(A2) where A2 holds a name that matches a named range, or =Lists!$E$2# pointing at a FILTER formula in Excel 365. The second list then shows only items related to the first choice.
Can I create a dependent drop-down list without INDIRECT?
Yes. In Excel 365 or 2021 put =FILTER(childColumn,parentColumn=A2,””) in a helper cell and use its spill range as the list source. In older versions, use OFFSET with MATCH and COUNTA to point at the right column of a lookup block. Both avoid named ranges entirely.
Why does my dependent drop-down show nothing?
The parent text does not match a range name exactly, usually because of a space, a typo or a name Excel refused to create. Open Name Manager and compare the names with the items in the first list. If the parent cell is still blank, the child list is empty by design until a parent is chosen.
How do I clear the second drop-down when the first one changes?
Data Validation cannot clear cells. Add the custom rule =COUNTIF(INDIRECT(A2),B2)>0 to the child column so a stale value is flagged with Circle Invalid Data, or use a short Worksheet_Change macro that clears B when A changes. Many forms simply instruct the user to reselect.
Do dependent drop-downs work when the list is on another sheet?
Yes. Named ranges are workbook-wide, so INDIRECT finds them from any sheet. For direct references such as =Lists!$E$2#, Excel 2010 and later accept another sheet in the Source box. Only Excel 2007 and earlier require a name for cross-sheet list sources.
Want the finished version? Ready-made Excel dashboards, trackers and VBA systems with dependent drop-downs built in are available at NextGenTemplates.com.