How can I automatically include new items in a drop-down list?

Convert the list to a table (Ctrl+T) and set the source to =TableName[Column], or create a UNIQUE spill range and reference it as =cell#.

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

For legacy Excel, use INDIRECT and defined names. In Microsoft 365 or later, spill the available options with FILTER/UNIQUE and set the child drop-down source to =spillcell#.

I want to use a list from another sheet as the source.

Data Validation has limitations when referencing another sheet. Create a defined name (=OtherSheet!range), then use =Name as the source.

Can I prevent users from entering values that are not in the list?

In Data Validation, clear 'Ignore blank' and set the Error Alert to 'Stop' to block values that are not in the list.

CONTENTS

How to Create Excel Drop-Down Lists: Accurate Input with Data Validation

Excel drop-down lists use Data Validation to create selection boxes in cells, preventing typos and formatting errors. With tables, UNIQUE, and FILTER, lists update automatically as they grow, and you can even create linked (dependent) lists.

Quick Fix (3 Minutes)

Convert the source list to a table (Ctrl+T) and name it ListT.
Select the drop-down cell → Data > Data Validation → Allow = List.
Set Source to =ListT[Item] or a spill reference (=F2#).
Set an Input Message and Error Alert for guidance and restrictions.

Auto-Expanding Drop-Down Lists

Table References

Using =ListT[Item] as the source automatically includes new items when they are added.

UNIQUE Spill

=SORT(UNIQUE(ListT[Category]))   // Spills from F2

Drop-down source: =F2#

Linked (Dependent) Drop-Down Lists

INDIRECT Method (Legacy)

=INDIRECT($C$2)

Create named ranges in advance with names that match the parent values.

FILTER Method (Recommended for Microsoft 365)

=SORT(UNIQUE(FILTER(CityT[City], CityT[Region]=$C$2)))

Child drop-down source: =G2#

Other Sheets, Hidden Sheets, and Protection

Define a name: ItemList = DataSheet!$A$2:$A$200 → Source: =ItemList
Hide the list sheet, unlock only the drop-down cells, then protect the sheet.

Input Messages and Error Alerts

Input Message: A guidance tooltip displayed when the cell is selected
Error Alert: Choose Stop, Warning, or Information
Clear “Ignore blank” to require a selection

Troubleshooting

Issue	Cause	Solution
New items are not included	Fixed range	Switch to a table reference or spill `#` reference
Error when referencing another sheet	Data Validation limitation	Use a defined name as a workaround
Dependent list is empty	Parent value is blank or misspelled	Require a parent selection and provide guidance with an Input Message
#SPILL! error	No room for the spill range	Clear nearby values or move it outside the table
INDIRECT is slow	Many names or heavy calculation load	Switch to the FILTER method

엑셀 순위 함수 완전정복 — RANK·RANK.EQ·RANK.AVG로 동점·그룹별 순위 해결

Excel Drop-Down Lists: Accurate Input and Dependent Lists with Data Validation