Excel Dropdown List: How to Create and Edit One

To create a dropdown list in Excel for Microsoft 365 on Windows, put the choices in a worksheet range, select the cells that should have the dropdown, then choose Data > Data Validation > Data Validation. Set Allow to List, enter the choices’ cell range in Source, make sure In-cell dropdown is checked, and select OK. The selected cells will then offer a menu of choices.

What an Excel dropdown list does

A dropdown list is a data-validation rule that lets someone choose from a defined set of entries in a cell. It is useful when entries should follow a consistent vocabulary: for example, a task tracker might offer Not started, In progress, and Done instead of relying on everyone to type a status the same way. Consistency makes filtering, sorting, and summarizing the data more dependable.

Excel can show the list when a user selects a cell, and it can also display an error message if someone enters a value that is not on the list. A dropdown is a convenience and a data-quality aid, not a security control: it does not prevent someone from changing the workbook or its rules. It also does not automatically correct existing entries when a validation rule is added.

Prepare the choices in a worksheet

For a list that may be edited later, store the options in cells rather than typing them into the validation box. For example, enter the headings Status and Priority in A1 and B1, then put Not started, In progress, and Done in A2:A4. A different column could hold Low, Medium, and High. Keeping choices in a separate area or on a dedicated worksheet makes them easier to inspect and maintain.

Put one choice in each cell, with no blank cells in the middle of the list.
Use clear, consistent wording and check for duplicate or unintended entries.
Keep the source values separate from the rows where people enter records, if that makes the workbook easier to use.

Blank cells at the bottom of a source range may appear as blank options in the dropdown. Select only the cells that contain choices. If your list has a heading, leave the heading out of the source range: in this example, use A2:A4 rather than A1:A4.

Create a dropdown from a cell range

Select the cell or range where users should choose an option, such as C2:C100 in a task table.
On the ribbon, select Data > Data Validation > Data Validation. In the Data Validation dialog, stay on the Settings tab.
Set Allow to List. Leave Ignore blank selected if an empty cell should be allowed.
In Source, enter the range containing the options. For choices in A2:A4 on the same worksheet, enter =$A$2:$A$4. The dollar signs keep the reference fixed if the validation is copied to other cells.
Confirm that In-cell dropdown is checked, then select OK.

Select one of the validated cells. A dropdown arrow appears when the cell is active; select it to see the available choices. Excel’s dialog may look slightly different in other releases, but the essential settings are Allow: List, a source, and the in-cell dropdown option.

Type choices directly into the Source box

For a very short list that is unlikely to change, you can type the options into Source instead of selecting a cell range. In the Data Validation dialog, set Allow to List and enter values separated by commas, such as Low,Medium,High. Do not include extra spaces unless they are intended to be part of the displayed option.

This method is quick for a temporary choice set, but it is less convenient to maintain: changing an option means editing the validation rule in every relevant place. The direct-entry source also has a 255-character limit, including separators, so it is unsuitable for long lists. A cell range is usually clearer when the list has several items, is shared across multiple columns, or may need updates.

List separators can depend on regional settings. If commas do not separate the entries correctly on your computer, use a source range instead; it avoids uncertainty about the separator used by your locale.

Use a named range or table for a maintainable list

A named range gives a source range a readable label. To create one, select the option cells, open Formulas > Define Name, enter a name such as StatusOptions, and confirm that the reference points to the intended cells. Then select the input cells and open Data Validation. Set Allow to List and enter =StatusOptions in Source. Names must follow Excel’s naming rules; for example, they cannot contain spaces.

Names are especially useful when choices are on a different worksheet. A direct cross-sheet reference may not be accepted in the Data Validation Source box, while a workbook-level name can refer to the source cells. Check the name’s scope and reference if the rule reports an error.

If the choices will grow, consider storing them in an Excel table. Select the source data and choose Insert > Table, confirming whether the data has headers. Tables expand as rows are added. A named reference to the table’s choice column can make the source easier to manage than a fixed range, but set up and test the name in the workbook before relying on it. A fixed source such as $A$2:$A$4 does not automatically include a new entry typed in A5.

Control messages and invalid entries

The Input Message tab in Data Validation can show a short instruction when someone selects a validated cell, such as Select the task status. This is helpful when the purpose of a column or the available choices may not be obvious. The Error Alert tab controls the response to an entry that is not on the list.

Stop blocks an invalid entry through normal typing and shows an error message.
Warning alerts the user but allows them to continue if they confirm.
Information displays a notice but is less restrictive.

Use Stop when entries should match the approved choices. A rule can still be bypassed or replaced in some workflows, such as by pasting data or changing the workbook, so validate important imported data separately. To review existing entries after applying a rule, select a cell with the rule and use Data > Data Validation > Circle Invalid Data where that command is available. This can help reveal older values that do not match the new list.

Edit, copy, or remove a dropdown

To change a list rule, select a cell that uses it and open Data > Data Validation > Data Validation. Edit the Source, message, or error-alert settings and select OK. If the same rule applies to several cells, select the full range before editing. The dialog’s Apply these changes to all other cells with the same settings option can also help update matching cells; check the selected range before confirming.

When copying a validated cell, ordinary copying can carry its validation rule along with other cell content. If you only want to copy the rule, use Paste Special and choose Validation, if that option is available in your Excel version. To remove a rule, select the relevant cells, open Data Validation, and choose Clear All in the dialog. This removes validation; it does not necessarily clear the values already in those cells.

Be careful when pasting over a validated range. Depending on the paste method, you may replace the validation rule as well as the displayed value. If the dropdown unexpectedly disappears, inspect the cell’s Data Validation settings and restore the rule to the intended range.

Common dropdown problems and fixes

No arrow appears: Select the cell and check that In-cell dropdown is checked in its List rule. The arrow normally appears only while the cell is selected.
The source is rejected: Confirm the source range exists and that the reference is entered correctly. For choices on another sheet, try a defined name that refers to the source cells.
New choices are missing: A fixed range will not grow by itself. Extend the range or use a suitable named reference to a table column.
Blank choices appear: Remove blank cells from the selected source range or adjust the range to cover only populated choices.
Invalid values remain: Adding a rule does not necessarily rewrite old data. Review existing entries and correct them; use Circle Invalid Data if available.
The menu is hard to use: Shorten or clarify the options, and consider placing the source list in a visible, clearly labeled area for workbook maintainers.

Also check whether the worksheet is protected or whether the cell was pasted from somewhere else. Protection settings, pasted content, and workbook edits can affect whether users can change a rule or whether the rule remains in place.

Dropdowns in Excel for Mac and the web

These steps describe Microsoft Excel for Microsoft 365 on Windows. Excel for Mac also provides data validation from the Data tab, though the dialog layout can differ. Look for Data Validation, select a List-style rule, specify the source, and enable the in-cell dropdown option if shown. Menu names and placement can vary slightly by release.

Excel for the web supports working with dropdown lists, but some advanced rule-management options may differ from the desktop application. If a workbook’s source uses named ranges, table references, or more involved validation settings, check the rule in the desktop version if the web interface does not expose the setting you need. The workbook’s underlying data and validation should be reviewed in the application where the rule was created when troubleshooting a complicated setup.

Frequently Asked Questions

How do I add a new item to an Excel dropdown?
Update the cells used as the source, then check that the validation source includes the new item. A fixed range such as A2:A4 will not include a new choice entered in A5 unless you extend it. A suitable table-based named reference can make an expanding list easier to maintain.

Can I let users select more than one item in a cell?
Excel’s standard Data Validation list allows one selection per cell, not multiple selections that accumulate in the same cell. A multi-select behavior requires a different design, such as separate columns or a custom macro. Macros need extra care and may be restricted by workbook security settings.

Can the dropdown choices come from another worksheet?
Yes. Put the options on the other worksheet and create a defined name that refers to those cells. Use the name, beginning with an equals sign, in the Source box. This avoids the direct cross-sheet reference limitation in the validation dialog.

Why can I type a value that is not in the list?
Check the rule’s Error Alert tab and make sure the alert style is Stop. Warning and Information alerts can permit a user to continue. Pasting data or changing the workbook can also bypass or replace validation, so a dropdown should not be treated as a guarantee that every stored value is valid.

How do I remove a dropdown without deleting its cell value?
Select the cell or range, open Data > Data Validation > Data Validation, and choose Clear All in the dialog. This removes the validation rule; the existing cell contents remain unless you separately clear or replace them.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.