Dropdown List in Excel: Create, Edit, and Fix One

In Excel for Microsoft 365 on Windows, select the cells for the dropdown, choose Data > Data Validation, set Allow to List, and enter or select the list of options. A dropdown list lets people choose an approved value in a cell instead of typing it, which can make a shared workbook easier to fill in and analyze.

What a dropdown list does

An Excel dropdown is a form of data validation. It presents a set of permitted choices in a cell, such as task statuses, product names, regions, or response categories. The choices can be typed directly into the validation settings or taken from cells in the workbook. When someone selects a cell with an in-cell dropdown, an arrow appears; choosing an item puts that value in the cell.

Dropdowns are useful when entries need to match exactly. For example, a report that records Open, In progress, and Closed can be filtered and counted more reliably if users select those exact labels than if they type variations such as In-Progress or Complete. A dropdown does not, by itself, make a workbook a secure form or guarantee that every entry will remain valid: users may be able to paste data over validated cells, and validation settings can be changed by people who can edit the workbook.

Create a basic dropdown list

For a short list that will not change often, enter the options directly in the Data Validation dialog. First select the cell or range that should contain the dropdown. Then use these steps in Excel for Microsoft 365 on Windows:

Choose Data > Data Validation. If the ribbon shows a Data Validation menu, open its settings.
On the Settings tab, set Allow to List.
Make sure In-cell dropdown is selected.
In Source, type the choices separated by the list separator used by your Excel settings. In many installations, that is a comma. For example, type Open,In progress,Closed.
Select OK. Click one of the validated cells to see its dropdown arrow and choose a value.

Applying validation to a range at once is often more convenient than setting up each cell separately. If the cells already contain entries, check that they match the new choices; validation does not automatically replace existing values. Excel for Mac has a Data Validation command on the Data tab, though the dialog layout can differ slightly. Excel for the web also provides data validation controls, but some workbook features and dialog options may be presented differently from the Windows desktop app.

Choose where the options come from

A source range is usually easier to maintain than a comma-separated list. Put the choices in a column, such as H2:H4, then set the validation Source to that range. If the source cells are on the same worksheet, select them using the range picker in the dialog or enter a reference such as =$H$2:$H$4. The dollar signs keep the reference fixed when validation is applied to multiple cells.

If your choices are on another worksheet, use a defined name rather than entering a direct reference to that sheet in the Source box. Select the source cells, choose Formulas > Define Name, give the range a clear name such as StatusOptions, and confirm it. In Data Validation, set Source to =StatusOptions. Names are also useful because they make the validation rule easier to understand later. Choose a name that does not contain spaces, and check that its scope includes the cells using the list.

For a small, stable set of choices, typing them into Source is quick. For a longer or frequently revised set, a source range is generally more practical: you can edit the entries in one place and reuse them in multiple dropdowns. Keep the source values free of unintended blank rows and duplicates if those would confuse users. If a choice is removed from the source, an old cell entry using that value is not necessarily cleared; review existing data when changing the list.

Keep a changing list up to date

A normal fixed range does not necessarily include new choices added below its last cell. One reliable approach is to store the options in an Excel table and maintain a defined name for the table column. When the table expands, its column expands too; the named range can then refer to that column, and the validation rule can continue to use the name. This keeps the source list separate from the cells where people make selections.

In Microsoft 365, a helper area can also create a sorted list of distinct choices from a source column. For example, if A2:A6 contains North, South, North, East, and South, place this formula in G2:

With the source values in A2:A6 as listed, enter =SORT(UNIQUE(A2:A6)) in G2. It returns East, North, and South in G2:G4, with duplicates removed. Create a defined name that refers to the spill range beginning at G2, then use that name as the Data Validation Source. This is useful when choices should follow a changing source list, but check the result for blanks or unwanted values before using it for data entry. SORT and UNIQUE are available in Microsoft 365 and Excel 2021 or later; older Excel versions need a different method.

Set messages and control invalid entries

The Data Validation dialog can do more than display choices. On the Input Message tab, you can show a short prompt when a user selects a validated cell. A message such as Select the current project status can explain what the field expects. Keep it specific; an instruction that merely repeats the column heading adds little help.

On the Error Alert tab, you can choose whether Excel warns about entries outside the list. The Stop style blocks an invalid value entered directly and asks the user to correct it. Warning and Information alerts allow the user to continue after acknowledging the message. If accuracy matters, use Stop and test the rule. Even Stop validation is not a substitute for protecting important workbook structure or checking imported and pasted data: paste operations can bypass or replace validation in some situations.

The Settings tab also includes Ignore blank. This allows the validated cell to remain empty; it does not add a blank choice to the displayed list. If every row must be completed, you may need a separate completeness check, because a dropdown alone does not force a user to make a selection.

Use and edit dropdowns efficiently

To make a selection, click a validated cell and select the arrow beside it. On Windows, Alt+Down Arrow opens the dropdown for the active cell; on Mac, Option+Down Arrow is the corresponding shortcut in Excel. You can also type a value directly, but whether Excel accepts it depends on the validation rule and its error-alert settings. A dropdown is not necessarily a searchable menu: with a long list, scrolling or typing may be less convenient than using a form or a filtered source list.

To change a rule, select a cell that already has it and choose Data > Data Validation. Edit the source, alert, or other settings and confirm. To apply the same settings to other cells, use the range selector or copy a correctly validated cell and use Paste Special > Validation where that option is available in the desktop app. Take care not to overwrite cell values or formatting when copying rules. If you need to remove validation, select the affected cells, open Data Validation, and choose Clear All in the dialog; this removes the validation rule, not the cell’s existing value.

Common dropdown problems and fixes

The arrow is missing: Select the cell and open Data > Data Validation. Check that Allow is List and In-cell dropdown is selected. The arrow is typically visible when the cell is selected, not at all times.
The source range is not accepted: For a list on another sheet, define a name for the source range and enter that name in Source with a leading equals sign. Also check for misspelled names and references to deleted cells.
New options do not appear: The rule may point to a fixed range that ends above the new entries. Extend the source range, or use a table-based or otherwise dynamic source setup.
Excel rejects a value that looks right: Check for extra spaces, different punctuation, or a value that is not actually in the source. Confirm whether the cell has a different validation rule from nearby cells.
Invalid values appear anyway: Review the Error Alert style and consider whether the data was pasted or imported. Select the relevant cells and use Data > Data Validation to confirm that the rule still exists.
Choices look incomplete or duplicated: Inspect the source cells for blank entries, repeated labels, and values outside the intended range. A dropdown displays the source values; it does not automatically clean them.

Dropdowns compared with other ways to enter data

A dropdown works well when there is a finite set of choices and users should select one value per cell. It is different from an Excel filter: a filter controls which rows are displayed, while a validated dropdown controls or guides what can be entered into selected cells. It is also different from a combo box, a form control that can offer more interface options but takes extra setup and may behave differently when a workbook is opened in another app.

For free-form information, such as a comment or a detailed address, a dropdown is usually the wrong tool. For a large list that changes often, a searchable form, a lookup-based workflow, or a controlled data-entry system may be easier than scrolling through a cell menu. For a simple workbook, however, Data Validation is built in and does not require an add-in or a separate service. The best choice depends on list size, how often it changes, and how strictly entries need to be controlled.

Frequently Asked Questions

How do I create a dropdown list in Excel from another sheet?
Select the source values, choose Formulas > Define Name, and assign a name such as CategoryOptions. Then select the destination cells, choose Data > Data Validation, set Allow to List, and enter =CategoryOptions as Source.

Can I add a new item to an Excel dropdown?
Yes. Add the item to the source list and make sure the validation rule includes that cell. If the rule uses a fixed range, extend the range; a table-based or dynamic named source can reduce the need to update the rule manually.

Can a dropdown allow more than one selection in a cell?
Standard Data Validation lists allow one selected value per cell. A multi-select behavior generally requires a different design, such as separate cells for each choice or a VBA macro. Macros require a macro-enabled workbook and can have security and compatibility implications.

Why can someone type a value that is not in the list?
The rule may use Warning or Information rather than Stop, may not cover that cell, or may have been bypassed by a paste or import. Check the cell’s Data Validation settings and review existing entries separately.

How do I remove a dropdown without deleting its value?
Select the cell or range, open Data > Data Validation, and choose Clear All in the validation dialog. This removes the rule while leaving the current cell contents in place.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.