What is the fastest way to create an Excel drop-down list?

Go to Data > Data Validation > List and specify the source range. For short lists, you can also enter comma-separated values directly.

How do I use a list from another worksheet as the source?

Define a name for the source range, then specify it in the Data Validation source as =name. This is a reliable way to work around cross-sheet limitations.

How do I create a dynamic drop-down list?

Use a Table plus SORT(UNIQUE(...)) to create a unique list in a helper cell, then set the Data Validation source to a spilled range such as H2#.

How do I create a dependent drop-down list (parent-child)?

Use FILTER to create a child list in a helper range based on the parent selection, then set the spilled range as the child drop-down source. You can also use defined names with INDIRECT.

My drop-down list is too long to scroll through. Can I search it?

Autocomplete is available in some environments, including Mac and mobile. It displays only items that match the text you enter, making selection faster.

CONTENTS

How to Create Excel Drop-Down Lists: Basic, Dynamic, and Dependent Lists with Data Validation

To enforce accurate data entry in Excel, drop-down lists are the fastest and safest option. This guide provides step-by-step instructions and practical examples for basic → dynamic (automatically updated) → dependent (parent-child) lists that even beginners can use right away.

Quick Fix (Done in 3 Minutes)

Prepare the list: Enter values in A2:A6 on the same worksheet.
Select the target: Select the drop-down cell → Data > Data Validation.
Configure it: Allow = List → Source = $A$2:$A$6 → OK.
Source on another worksheet: Define the range with the name CityList, then use =CityList as the source.
Dynamic updates: Use a Table and a helper cell with SORT(UNIQUE(...)) → Source = =H2#.
Dependent list: Create a helper cell with FILTER → Set the source to =I2#.

Concept: Data Validation and List Sources

Select List in Data Validation to display a drop-down in the cell.
The source can be comma-separated values or a range/name. For dynamic lists, a spilled (#) range is recommended.

Basic Drop-Down List

Same Worksheet

Source: =$A$2:$A$6. Use the Error Alert tab to display a message for invalid entries.

Another Worksheet

Define a name for the source range (for example, CityList).
Drop-down source: =CityList.

Dynamic Drop-Down List (Table + UNIQUE + # Reference)

Sheet2!H2: =SORT(UNIQUE(Table1[City]))

Drop-down source: =Sheet2!H2# — it updates automatically as the Table grows.

Dependent Drop-Down List (Region → Store)

Method A: FILTER + Spilled Range Reference

H2: =SORT(UNIQUE(TableRegion[Region]))
I2: =SORT(UNIQUE(FILTER(TableStore[Store], TableStore[Region]=A2)))

Parent source: =H2#, child source: =I2#

Method B: Defined Names + INDIRECT

Create named ranges for each region and use =INDIRECT(A2) as the child source.

Searchable Autocomplete

In some environments, including Mac and mobile, drop-down autocomplete is available to make navigating long lists faster. Refer to the official announcements for availability.

Troubleshooting

Issue	Cause	Solution
Arrow does not appear	Display option is turned off	Data Validation settings > select In-cell dropdown
List does not update	A fixed range is used	Use a Table + UNIQUE helper cell and a `#` reference
#SPILL!	Spill range is blocked or at a boundary	Clear surrounding cells, unmerge cells, or move the formula
Cannot link another worksheet	Cross-sheet limitation	Wrap the range in a defined name, then use `=name`
Selected value disappears	Source item was deleted	Manage the Table and turn Error Alert on

Checklist & Next Articles

엑셀 TEXTSPLIT · TEXTBEFORE · TEXTAFTER 완벽 가이드 — 문자열 분해 자동화