Static dropdowns need manual updating. Dynamic dropdowns update themselves when your data changes.
A static dropdown has a fixed list of options you type manually. A dynamic dropdown pulls from a range — add new items to the range and they appear in the dropdown automatically.
-- Step 1: In a spare column, extract unique values:
=UNIQUE(A2:A100) -- spills into as many rows as needed
-- Step 2: Name the spill range
-- Click the UNIQUE formula cell
-- In the Name Box, type: UniqueRegions, press Enter
-- Step 3: Data Validation → List → Source:
=UniqueRegions
-- The dropdown now shows all unique values and updates automatically-- Convert your options list to a Table (Ctrl+T)
-- Name the table "Regions" (Table Design → Table Name)
-- Data Validation → List → Source:
=Regions[Region] -- references the Region column of the Table
-- Adding rows to the Table automatically adds options to dropdownSecond dropdown shows options based on the first dropdown selection — e.g. choose Country, then see only cities in that country.
-- Step 1: Name each country's city list as the country name
-- Select UK cities → Name Box → type "UK" → Enter
-- Select USA cities → Name Box → type "USA" → Enter
-- Step 2: For the cities dropdown, Source:
=INDIRECT(A2)
-- A2 contains the selected country
-- INDIRECT converts "UK" to the named range UK-- Named range formula (Formulas → Name Manager):
=OFFSET(Sheet1!$A$1, 0, 0, COUNTA(Sheet1!$A:$A), 1)
-- Starts at A1, always covers every non-empty cell in column A
-- Use this named range as the dropdown sourceUNIQUE is Excel 365 only. OFFSET works in all versions but is volatile. For maximum compatibility, use Excel Tables as the source — they work in Excel 2010 and later and auto-expand without any formula.
Practice these formulas in the Data Analyst track — 100 exercises covering dynamic arrays, data cleaning, and analysis. Free to start.
Start the Data Analyst track free →