Dependent Drop-Down List in Excel (City, then Area)

By Ravindra Bagale, Excel & Data Analytics trainer LinkedIn Published: 6 October 2026 Last updated: 6 October 2026

Ruhi's delivery form has two drop-downs: City and Area. When she picks Nashik she wants to see only College Road, Panchavati and Satpur, not all eighteen areas of six cities. That second list depends on the first, so it is called a dependent drop-down.

You give each list of areas a name that matches its city (Pune, Nashik and so on). The Area drop-down then uses =INDIRECT(A2), which turns the city text in A2 into the range with that name.

Download the practice file (dependent-drop-down-list.xlsx)

Excel file, 7 KB. It has the sample data used below. All numbers in this workbook are fictional teaching data, not real company figures.

Quick answer

On a Lists sheet, put each city's areas in its own column with the city as the header.
Select the lists with headers and click Formulas > Create from Selection > Top row > OK.
Name the city list Cities and make the City drop-down: Data > Data Validation > List, Source =Cities.
Select the Area cell and add Data Validation > List with Source =INDIRECT(SUBSTITUTE(A2," ","_")).
Pick a city, then open the Area drop-down.

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

Step 1: build the lists

Pune	Nashik	Nagpur	Sambhaji_Nagar
Kothrud	College Road	Dharampeth	CIDCO
Baner	Panchavati	Sitabuldi	Nirala Bazar
Hadapsar	Satpur	Manish Nagar	Garkheda

A name cannot contain a space, so the header for Sambhaji Nagar is Sambhaji_Nagar. The SUBSTITUTE in the formula changes the space to _ when it looks up the name.

Step 2: create the names

Select the headers and the areas below them (for example B1:G4 on the Lists sheet).
Click Formulas > Create from Selection.
Tick only Top row and click OK.
Open Formulas > Name Manager (Ctrl + F3) to check: Pune refers to the Pune areas.
Select the six city names in column A, type Cities in the Name Box (left of the formula bar) and press Enter.

Check a name with a formula: =COUNTA(Pune) gives 3 and =INDEX(Nashik,2) gives Panchavati.

Step 3: the two drop-downs

Select A2:A20 on the Entry sheet.
Click Data > Data Validation, choose Allow: List, type =Cities in Source, click OK.
Select B2:B20.
Click Data > Data Validation > List and type =INDIRECT(SUBSTITUTE(A2," ","_")). If Excel warns that the source is an error, click Yes; A2 is just empty right now.
Pick Nashik in A2 and open B2. You see only the three Nashik areas.

Good to know

If you change the city after choosing an area, the old area stays. Add a check column: =IF(COUNTIF(INDIRECT(SUBSTITUTE(A2," ","_")),B2)=0,"Check area","OK").
To add an area, insert it inside the named range (or turn each list into a table) so the name grows.
INDIRECT does not work with closed workbooks, so keep the lists in the same file.

Ravindra Bagale's Tip

Keep the Lists sheet in the same workbook and hide it when the form is ready (right-click the tab > Hide). Users see clean drop-downs and cannot break the lists by mistake.

Practice task

Open the Entry sheet and pick Pune in A2.
Open the drop-down in B2. Only Kothrud, Baner and Hadapsar appear.
Pick Sambhaji Nagar in A3 and check B3 shows CIDCO, Nirala Bazar and Garkheda.
Open Formulas > Name Manager and find the Sambhaji_Nagar name.
Check the test formulas on the Lists sheet (I2 and I3).

Learn it properly

Course lessons:

Frequently asked questions

Why does INDIRECT show a #REF! error?

The text in the city cell does not match a defined name exactly, often because of a space. Use SUBSTITUTE to turn spaces into underscores.

Can the source lists be on another sheet?

Yes. Named ranges work from any sheet in the same workbook.

Does the area clear when I change the city?

No. Data Validation only checks while typing. Add a check column or a small macro to clear it.

Is there a way without INDIRECT?

In Microsoft 365 you can keep a two-column City/Area table and use FILTER in a helper area as the list source. INDIRECT is still the simplest for beginners.