Create a Dropdown List in Excel: Setup, Sources, and Tips

To create a dropdown list in Excel for Microsoft 365 on Windows, select the cell or cells, go to Data > Data Validation > Data Validation, choose List under Allow, enter the choices in Source, and click OK. For a list you may update later, use a cell range as the source instead of typing every option into the dialog.

What an Excel dropdown list does

A dropdown list gives someone a set of choices to select in a worksheet cell. It is made with Excel’s Data Validation feature, which can limit what users enter and display a small arrow beside a selected cell. For example, a staff tracker might offer Pending, In progress, and Complete, while an order sheet might offer product names or shipping methods.

Lists are useful when people need to enter the same kinds of information repeatedly. They can reduce inconsistent spellings such as In progress, In-Progress, and In progress with an extra space, making it easier to filter, count, or summarize the entries later. They do not, however, make a workbook a secure form: a user may still change or remove data in ways that bypass validation, especially by pasting over cells.

Create a list by typing the choices

For a short list of stable options, enter the choices directly in the Data Validation dialog. First select the destination cell or range. Then use these steps:

On the Data tab, select Data Validation, then select Data Validation again if the menu opens with that option.
On the Settings tab, set Allow to List.
In Source, type the choices separated by the list separator used by your Excel settings. In many installations, that is a comma; for example, Pending,In progress,Complete.
Leave In-cell dropdown selected, then choose OK.

Select the cell to see the arrow, then choose an option. The arrow may not appear until the cell is selected. If the options do not split into separate entries, your regional settings may use a different separator. Check the list separator Excel expects in your setup, or use a source range instead, which avoids typing a separator between options.

Directly typed choices are convenient for a few short labels, but they are less convenient to maintain. When an option changes, you must edit the validation rule; the list also becomes harder to inspect when the Source box contains many items. Use a source range for longer lists or choices that change periodically.

Build a dropdown from worksheet cells

Put each choice in its own cell, preferably in a single column with no blank cells between entries. For example, type Pending, In progress, and Complete in A2:A4. Select the cell or destination range where users should make a selection, open Data > Data Validation > Data Validation, set Allow to List, and enter =$A$2:$A$4 in Source. Click OK. The dollar signs keep the source addresses fixed if the validation rule is copied elsewhere.

Keep the source values on the same worksheet when you use a direct cell reference. Excel’s Data Validation Source box does not accept an ordinary direct reference to a range on another sheet. To keep the list on a separate worksheet, define a named range for the choices and use that name as the source:

Select the source cells, such as A2:A4 on a sheet named Lists.
Open Formulas > Name Manager > New.
Give the range a clear name such as StatusOptions. Avoid spaces in the name.
Confirm that Refers to points to the intended cells, then close the dialog.
In the Data Validation Source box, enter =StatusOptions.

A workbook-level name is generally the easiest to reuse from other worksheets. If the named range is misspelled, scoped only to a different sheet, or points to the wrong cells, Excel may reject the source or show an unexpected list. You can review and correct names in Name Manager.

Choose a source that is easy to maintain

The best source depends on how often the choices change and who maintains the workbook. A typed list is simplest for a fixed set of a few options. A cell range is easier to review and edit, and a named range makes a source on another sheet manageable. For a list that grows over time, consider storing the source entries in an Excel table.

To make a table, select the source list and press Ctrl+T on Windows, or choose Insert > Table. Confirm whether the table has headers. Tables expand when you add entries directly below them, which can help keep source data organized. For Data Validation, create a workbook-level name that refers to the table’s list column, such as =Table1[Department], then use =DepartmentOptions in the Source box if the name is DepartmentOptions. Replace Table1 and Department with the actual table and column names in your workbook. Test the rule after adding an item: table behavior and named references can be affected by how the data is arranged, so do not assume a new choice is available until you verify the dropdown.

Dynamic-array functions such as UNIQUE and SORT are available in Microsoft 365 and Excel 2021 or later, but a formula that produces a spilled list is not always best entered directly in the Data Validation Source box. If you use a formula-generated list, put its results in worksheet cells and point a named range or validation rule at the resulting range. That makes the list’s contents visible and easier to troubleshoot.

Set input messages and error alerts

Data Validation can do more than show choices. On the dialog’s Input Message tab, you can show a short instruction when someone selects a cell, such as Select one status from the list. This is useful when an option’s meaning is not obvious, or when the worksheet is used by people who did not create it.

On the Error Alert tab, keep Show error alert after invalid data is entered selected if you want Excel to respond when someone types an entry that is not on the list. The alert style affects how strict the rule is:

Stop blocks a typed entry that does not meet the rule and asks the user to correct it.
Warning tells the user the entry is invalid but allows them to continue.
Information displays a notice but is less restrictive.

These alerts guide normal data entry; they are not a substitute for access controls or careful review. Pasting data into validated cells can bypass the warning or replace the validation rule. If consistency matters, review the data after importing or pasting, and protect the worksheet where appropriate while remembering that sheet protection is not strong security for sensitive information.

Apply the rule to more than one cell

You can select a range before opening Data Validation to apply one rule to many cells at once. For example, select B2:B100 in a status column and create the list rule once. Each cell then offers the same options. If a rule already exists in one correctly configured cell, you can copy that cell and use Paste Special > Validation to copy only its validation settings; this avoids replacing values or formatting in the destination cells.

When you edit a rule, the change applies to the selected cells covered by that validation setting. Use Data > Data Validation with one of the cells selected to inspect its current settings. If you select multiple cells with different rules, Excel may show that the selection contains mixed settings, so inspect or update the cells in smaller groups.

In-cell dropdowns are different from form controls such as a Combo Box. A validation dropdown stores the chosen text directly in the cell and is usually the simplest choice for ordinary worksheet data. A Combo Box is a separate control that can have additional settings, but it takes more setup and may be unnecessary when the goal is simply to offer a list of allowed cell entries.

Common problems and how to fix them

The arrow is missing: Select the cell and check Data Validation. Confirm that Allow is List and In-cell dropdown is checked. The arrow normally appears when the validated cell is active, not all the time.
The source text appears as one option: Check the separator used by your regional settings. Alternatively, place each choice in a separate cell and use a range.
Excel says the source evaluates to an error: Check the source address, named range spelling, and name scope. A reference to another sheet should usually be handled through a defined name.
A new source item is missing: Check whether the rule refers to a fixed range that ends above the new item. Expand the source range or use a suitable maintained range or table-based setup.
An invalid value is still present: Validation settings do not automatically erase existing entries that no longer match the list. Use Data > Data Validation and the dialog’s Circle Invalid Data option to identify entries that do not comply, then correct them.
Users can enter something else: Check the Error Alert tab and ensure an alert is enabled with Stop if you want to block ordinary invalid typing. Also remember that pasting can bypass validation, so check pasted or imported data.

A dropdown also does not automatically sort or remove duplicate source items. Put the source choices in the order you want users to see, and remove duplicates in the source list if repeated choices would be confusing. Blank source cells may also result in blank-looking options, so review the source range when the menu contains an unexpected empty entry.

Excel for Mac and Excel for the web

The instructions above are for Excel for Microsoft 365 on Windows. In Excel for Mac, the feature is also called Data Validation and is available from the Data tab; select the destination cells and choose Data Validation to configure the list. Ribbon layout and dialog presentation can differ slightly by release. The Windows shortcut Ctrl+T for creating a table is Command+T on Mac in Excel.

Excel for the web also supports dropdown lists and Data Validation, though the location or availability of particular dialog options can vary as Microsoft updates the web interface. If an existing workbook uses a feature or setting that is unavailable in the web version, open it in the desktop app to make that change. For collaboration, test the list in the version your users will actually use, especially if the source relies on named ranges or more elaborate formulas.

Frequently Asked Questions

How do I make a dropdown list in Excel with multiple choices?
Select the target cells, open Data > Data Validation, set Allow to List, and enter the options in Source separated by your Excel list separator. For more than a few choices, enter each option in its own worksheet cell and use that range as the source.

How do I add another option to an existing dropdown?
Edit the source in Data Validation if the choices were typed directly. If the source is a cell range, add the new choice within the referenced range or expand the range so it includes the new cell. Then select the dropdown again to check that the option appears.

Can an Excel dropdown list use choices on another sheet?
Yes. Define a named range that refers to the cells on the other worksheet, then enter the name preceded by an equals sign in the Data Validation Source box. A plain direct reference to another worksheet is not the usual supported method in that box.

Can I let users type their own option?
Yes, but that changes the purpose of the list. On the Error Alert tab, turn off the option to show an error alert, or use a less restrictive alert style if appropriate. Users can then enter values outside the list, so the column may contain inconsistent entries.

How do I remove a dropdown list without deleting its value?
Select the validated cells, open Data > Data Validation > Data Validation, and choose Clear All in the dialog, then confirm. This removes the validation rule; it does not ordinarily clear the existing cell contents. Check the selection first so you do not remove rules from unrelated cells.

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.