How do I create a drop-down list in Excel?

Select the cells, go to Data > Data Validation, choose List, and enter comma-separated values or select a source range.

How do I make an Excel dropdown update automatically?

Use an Excel Table or a dynamic array source such as SORT(UNIQUE(range)) so new items can flow into the dropdown source.

What is the best source for a drop-down list in Excel?

An Excel Table is often best because the source can expand as new list items are added.

Can a drop-down list show unique values automatically?

Yes in Microsoft 365. Use SORT and UNIQUE to create a dynamic source, then point Data Validation to the spill range.

Tutorials - 2026-05-12

How to Create a Drop-Down List in Excel

Learn how to create Excel drop-down lists with Data Validation, dynamic source ranges, dependent dropdowns, and common fixes when lists do not update.

Why Drop-Down Lists Matter

Drop-down lists make Excel workbooks easier to use and harder to break. Instead of letting users type anything, you give them a controlled list of valid options. This is useful for: Status fields such as Open, Closed, Pending Product names Department names Regions Approval choices Data entry forms The core feature is Data Validation.

Create a Basic Drop-Down List

Select the cells where you want the dropdown. Go to Data -> Data Validation. Choose Allow: List. Type the list items separated by commas, or select a range that contains the options. Example source: \\\ Open,Pending,Closed \\\ For a worksheet range: \\\ =A2:A10 \\\ Click OK, and Excel adds the dropdown arrow to the selected cells.

Best Practice: Use a Source Table

For lists that change over time, put options in an Excel Table. Tables expand automatically when you add new items. Recommended setup: Create a sheet called Lists. Put options in a column. Convert the range to a Table with Ctrl+T. Name the table something clear, such as StatusList. This makes maintenance easier and reduces broken dropdowns.

Dynamic Drop-Down Lists

If your Excel version supports dynamic arrays, you can create a clean source list with UNIQUE and SORT. \\\ =SORT(UNIQUE(A2:A100)) \\\ This produces a dynamic list of unique options. Use the spill range as the dropdown source: \\\ =E2# \\\ The # symbol references the entire spilled array.

Dependent Drop-Down Lists

A dependent dropdown changes based on a previous selection. For example, choosing a region changes the list of available stores. Common approach: Create named ranges for each group. Use INDIRECT to refer to the selected group. \\\ =INDIRECT(A2) \\\ If A2 contains East, Excel uses the named range East as the source.

Common Drop-Down Problems

The dropdown does not show Check that Data Validation is applied to the selected cells and that the source range is valid. New items do not appear Use an Excel Table or dynamic array source instead of a fixed range. The source has duplicates Use UNIQUE to create a clean dropdown source. The dropdown allows invalid entries In Data Validation, make sure Error Alert is enabled.

Related Guides

UNIQUE function SORT function Excel Tables Benefits Excel Formula Audit Checklist

Drop-Down List Design Choices

List type Best for Source example Typed values Short static lists Open, Closed, Pending Range source Lists maintained by users =$H$2:$H$20 Excel Table source Lists that grow =StatusList[Status] Dynamic array source Unique sorted values =SORT(UNIQUE(A2:A100))

Expert Validation Tips

Keep the source list on a dedicated setup sheet when the workbook is shared. Protect formula cells but leave input cells unlocked. Use input messages to explain what users should select and error alerts to prevent invalid entries. For dependent drop-downs, keep category names simple and avoid special characters that make named ranges harder to manage.

Related Internal Guides

Use Data Validation in Excel for broader validation rules, UNIQUE and SORT for dynamic sources, and Lock Cells in Excel when sharing templates.

Dependent dropdown sequence

Create primary list (Region) on Setup sheet. Name each region's city list (East_Cities, West_Cities). Data Validation on City cell: \

Frequently asked questions

How do I create a drop-down list in Excel? Select the cells, go to Data > Data Validation, choose List, and enter comma-separated values or select a source range.
How do I make an Excel dropdown update automatically? Use an Excel Table or a dynamic array source such as SORT(UNIQUE(range)) so new items can flow into the dropdown source.
What is the best source for a drop-down list in Excel? An Excel Table is often best because the source can expand as new list items are added.
Can a drop-down list show unique values automatically? Yes in Microsoft 365. Use SORT and UNIQUE to create a dynamic source, then point Data Validation to the spill range.