How to Make a Drop-Down List in Excel

Excel’s Data Validation tool turns any cell into a pick-list, so anyone using the sheet can only choose from approved entries instead of typing free text. You build the list one of two ways: type the items directly into the validation dialog, or point it at a range of cells so the list can grow with your data. Here’s the exact path, both source options, how to edit the list once it’s live, and what the error-alert styles actually do.

Build the Drop-Down with Typed Items#

1. Select the cell or range that should show the drop-down.
2. Go to the Data tab and select Data Validation.
3. On the Settings tab, set Allow to List.
4. In the Source box, type the entries separated by commas, for example Open,In Progress,Closed.
5. Confirm In-cell dropdown is checked, then select OK.

This method works best for short lists that rarely change, like status flags or priority levels.

Use a Cell Range as the Source Instead#

For longer or growing lists, keep the entries on the sheet instead - one item per cell, in a single column or row, with no blank cells and no header included in the selection.

1. Open Data Validation the same way, from the Data tab.
2. Set Allow to List.
3. Click into the Source box, then select the range on the sheet, or type a reference such as =Sheet2!$A$2:$A$20.
4. Select OK.

If that source range is formatted as an Excel Table, new rows you add to it appear in the drop-down automatically. A plain comma-separated list never updates itself that way.

Source type	Where entries live	Best for	Updating later
Typed list	Inside the Source box, comma-separated	Short, stable lists (5-10 items)	Reopen Data Validation and edit the text
Cell range	A column or row elsewhere on the sheet	Longer or changing lists	Edit the cells directly; the range updates instantly
Excel Table column	A structured Table	Lists that expand often	Add a row to the Table; the drop-down picks it up on its own

Edit the Drop-Down List Later#

Select any cell that already has the validation, open Data > Data Validation, then adjust the same Source box you used to create it:

• Typed list: edit the comma-separated text directly.
• Range source: edit the values in those cells, or widen/narrow the reference in the Source box if rows were added or removed outside the original range.
• To push an edited rule out to every cell that shares it, check “Apply these changes to all other cells with the same settings” before selecting OK - otherwise only the currently selected cell is updated.

Turn On an Error Alert#

The Error Alert tab, in that same dialog, decides what happens when someone types something outside the list.

Style	What the user sees	Can the invalid entry stay?
Stop	Entry is blocked until it’s corrected	No
Warning	A Yes/No prompt asking whether to continue anyway	Yes, if they choose Yes
Information	A message is shown, then the entry goes through	Yes

Check “Show error alert after invalid data is entered”, pick a Style, and fill in a Title and Error message so people understand why the entry failed. Use Stop for anything that feeds a formula or a report; Warning or Information suit lists that are more of a suggestion than a rule.

Once the drop-down is live, it pairs well with the rest of a cleaner workbook: standardized values are what make functions like VLOOKUP return reliable matches, and a validated column summarizes cleanly in a pivot table without stray typos splitting a category into duplicates.

A drop-down list is one of the cheapest ways to keep a shared spreadsheet consistent, since it stops typos and near-duplicate labels before they reach a formula or report. Excel has no dedicated “edit list” menu, but the fix is always the same few clicks back into Data Validation and its Source box.