How to Make a Drop-Down List in Excel
Create data validation dropdown lists in Excel from a manual list, a cell range, or a named range — plus how to make dependent dropdowns where the second list changes based on the first selection.
A dropdown list restricts what users can enter in a cell to a defined set of options. It prevents typos, enforces consistent data entry, and makes spreadsheets much easier to use — especially when you're sharing files with colleagues who aren't Excel-fluent.
Basic dropdown from a manual list
Step 1. Select the cell (or range of cells) where you want the dropdown.
Step 2. Go to Data → Data Validation → Data Validation.
Step 3. Under Allow, choose List.
Step 4. In the Source field, type your options separated by commas:
Open, In Progress, Closed, On Hold
Step 5. Click OK. The cell now shows a dropdown arrow with those four options.
✦ TIP
Add an Input Message (second tab in Data Validation) to show a tooltip when someone clicks the cell — useful for explaining what the field is for.
Dropdown from a cell range
Typing options directly works for small, stable lists. For anything longer or that might change, put your options in a column and reference them:
Step 1. Type your options in a separate column (e.g., H2:H10).
Step 2. In the Source field of Data Validation, enter:
=$H$2:$H$10
Now if you update the list in column H, all dropdowns using that source update automatically.
Dropdown from a named range (cleanest approach)
Named ranges make the source easier to read and manage, especially across sheets:
Step 1. Select your options list and go to Formulas → Name Manager → New. Name it (e.g., StatusList).
Step 2. In Data Validation → Source, type:
=StatusList
This works even if the named range is on a different sheet — regular range references don't work cross-sheet in Data Validation, but named ranges do.
Dependent dropdowns (second list changes based on first)
A dependent dropdown shows different options based on what was selected in a previous cell. For example: select "North America" → second dropdown shows US, Canada, Mexico.
Step 1. Create named ranges for each sub-list, named exactly as the parent options. If the first dropdown has "North America", create a named range also called NorthAmerica (no spaces — use underscores if needed).
Step 2. In the dependent dropdown's Source field, use INDIRECT:
=INDIRECT(SUBSTITUTE(A2," ","_"))Where A2 contains the first dropdown selection. INDIRECT converts the text to a range reference, and SUBSTITUTE handles spaces.
⚠ WATCH OUT
INDIRECT is volatile — it recalculates constantly. In large files with many dependent dropdowns, this can slow things down. Acceptable for most use cases.
The Griddy way
Setting up dependent dropdowns and keeping named ranges organized across a complex file takes real effort. Just describe the structure:
"Set up dropdowns in column D for Status (Open, In Progress, Closed) and in column E for Priority (High, Medium, Low) — apply them to all rows in the table"
Griddy creates the data validation rules across the entire dataset.
Skip the manual work
Describe it. Griddy does it.
Instead of writing this formula yourself, just tell Griddy what you need in plain English. Works in Excel and Google Sheets.