Ravindra BagaleCourses & study guides मराठी Track your progress

2. Data Entry Tools

2.5 Dependent Drop-down: City › Area

By Last updated:

Dependent drop-down (City › Area) with example — in 30 seconds

What: A dependent drop-down shows only the choices that belong to the value picked in another cell.

Why: If City is Pune, the Area list should not offer College Road. Fewer choices mean fewer typing mistakes.

Tiny example: Pick Pune in D2. The Area drop-down in E2 shows only Kothrud, Baner and Hinjewadi.

First look at the data (before → after):

Dependent drop-down (City › Area) with example: before and after tables, fictional FreshGo Maharashtra data

Expected result — D2 = Pune → choices in the E2 drop-down

Lists!J (spill) Shown in E2 list
Kothrud Yes
Baner Yes
Hinjewadi Yes

About this data: Teaching sample for a fictional shop “Maple Bakery Co”. Figures and names (e.g. Aamir Khan) are made up for this lesson on dependent drop down city area.

Before table and formula as text

Before — Lists sheet: table CityArea

City Area
Pune Kothrud
Pune Baner
Pune Hinjewadi
Nashik College Road
Nashik Gangapur Road
Nagpur Dharampeth

About this data: Demo table only — not real customer or company data. Built to practise dependent drop down city area.

Formula / click path

Lists!J2: =FILTER(CityArea[Area], CityArea[City]=Orders!D2)
Orders!E2 › Data Validation › List › Source: =Lists!$J$2#

Do it step by step:

  1. Type the City and Area pairs on a sheet named Lists and press Ctrl + T. Name the table CityArea.
  2. In Lists!J2, type =FILTER(CityArea[Area], CityArea[City]=Orders!D2).
  3. Go to Orders!E2 and click Data › Data Validation.
  4. Choose List. In Source, type =Lists!$J$2#. Click OK.
  5. Pick Pune in D2, then open the drop-down in E2.

When the user picks a City, the Area drop-down should show only that city's areas.

Pune Solapur Nashik Sambhaji_Nagar Kolhapur Nagpur
Kothrud Hotgi Road College Road CIDCO Rajarampuri Dharampeth
Hinjewadi Murarji Peth Gangapur Road Nirala Bazar Tarabai Park Sitabuldi
Baner
Hadapsar
Wakad

About this data: Made-up rows so you can copy the pattern for dependent drop down city area. Currencies may use ₹, £, € or $.

Method A – named ranges + INDIRECT (all versions)

Steps in Excel

  1. Type the table above on the Lists sheet (headers in row 1). Note Sambhaji_Nagar uses an underscore because a name cannot contain a space.
  2. Select the whole block › Home › Editing › Find & Select › Go To Special › Constants (selects only filled cells).
  3. Formulas › Defined Names › Create from Selection › tick only Top row › OK. Excel creates names Pune, Solapur, Nashik, Sambhaji_Nagar, Kolhapur, Nagpur.
  4. City drop-down in Orders!D2:D1000: List source =CityList (the vertical list of the six real city names from 2.4).

  5. Area drop-down in E2:E1000: List source:

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

  6. Pick Sambhaji Nagar in D2 – the SUBSTITUTE turns it into Sambhaji_Nagar, INDIRECT turns that text into the named range, and E2 shows CIDCO and Nirala Bazar.

Method B – FILTER (Microsoft 365 / Excel 2021+)

Keep a two-column mapping table CityArea (City, Area). In a helper cell, say Lists!J2:

=FILTER(CityArea[Area], CityArea[City]=Orders!D2)

Then Area validation source: =Lists!$J$2#. This works for one input row at a time (a dashboard selector). For many rows, Method A is simpler.

Ravindra Bagale's Tip

In a dependent drop-down, the common mistake is that the city name and the named range name are slightly different – "Sambhaji Nagar" vs "Sambhaji_Nagar", or a space at the end. Then INDIRECT gives #REF! and the list appears empty. Use SUBSTITUTE, and select the City first and then the Area – if you do it the other way round, the old area stays as it is.

Practice task

Build the City › Area dependent drop-down for rows 2–100. Test all six cities. Bonus: add a conditional-formatting rule (2.8) that turns the Area cell red if it does not belong to the chosen city.

Loading practice tasks...

  1. VLOOKUP name (Easy): Name for ID "B103" with VLOOKUP.
  2. XLOOKUP value (Easy): The Rate per night for ID "B103".
  3. Position (Easy): Position of "Chloe Dubois" in B2:B9.
  4. INDEX and MATCH (Medium): ID for name "Chloe Dubois" using INDEX and MATCH.
  5. Not found message (Medium): Nights for ID "Z999", or "Not found".
  6. IFNA with VLOOKUP (Medium): VLOOKUP of "Z999" in A2:E9 column 4, or "Missing".
  7. XMATCH (Medium): Position of ID "B103" in A2:A9.
  8. Slab rate (Hard): Rate for E2 from the slab table G2:H6.
  9. Slab amount (Hard): E3 times its slab rate.
  10. Two-way lookup (Hard): Value in A2:E9 at the row of "Chloe Dubois" and column "Nights".
  11. Lookup the top (Hard): Name with the largest Nights.

See all practice tasks in the Task Hub →

Quick revision cards

Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.

Revision card 1: What is Dependent Drop Down City Area?
Card 1: What is Dependent Drop Down City Area? · Download PNG
Revision card 2: Tiny example — Dependent Drop Down City Area
Card 2: Tiny example — Dependent Drop Down City Area · Download PNG
Revision card 3: Checklist — Dependent Drop Down City Area
Card 3: Checklist — Dependent Drop Down City Area · Download PNG