Excel Cell Dropdown: Create a Pick List and Use It

To create a dropdown pick list in Excel for Microsoft 365 on Windows, select the cell or cells, choose Data > Data Validation, set Allow to List, enter the choices or select a source range, make sure In-cell dropdown is checked, and select OK. The list lets someone choose a permitted entry from an arrow inside the selected cell.

What an Excel dropdown does

An Excel cell dropdown is created with Data Validation. A list validation rule gives a cell a menu of choices, such as departments, order statuses, regions, or Yes and No. Choosing a standard option is usually quicker than typing it and reduces inconsistent entries such as “In progress,” “In-Progress,” and “In progress ” with an accidental trailing space.

The arrow appears when the cell is selected; it is not necessarily visible while the cell is inactive. A dropdown is a data-entry aid, not a separate form control or a security boundary. Users can still paste or fill values into validated cells, and some kinds of edits can bypass the prompt. If the entries matter, check the data after importing or pasting it.

Create a basic dropdown list

First decide where the choices will live. For a short list that is unlikely to change, you can type the choices directly into the validation rule. For a list that may be edited or reused, put the choices in worksheet cells and use that range as the source.

Select the cell where the dropdown should appear. To use the same rule in several cells, select the whole target range first.
Open Data > Data Validation.
On the Settings tab, set Allow to List.
For Source, either type the choices separated by commas, such as Open,In progress,Complete, or select the cells containing the choices.
Keep In-cell dropdown checked. Select Ignore blank if an empty cell should be allowed.
Select OK, then select a target cell to confirm the arrow and choices appear.

If you type choices directly, the entries are stored in the validation rule rather than in worksheet cells. This is convenient for a tiny, stable set. The list separator may follow your computer’s regional settings, so a comma-separated source may need the separator used by your Excel installation. If the entries contain punctuation or are awkward to enter, use a cell range instead.

Use a range as the list source

A source range is easier to maintain when choices change. For example, enter the status names Open, In progress, and Complete in A2:A4 on a worksheet called Lists. Then select the target cell and open Data > Data Validation. Set Allow to List and use the source range in the Source box. A reference to cells on the same worksheet can be selected directly; Excel will show the corresponding cell reference. Select OK to save the rule.

When the list is on another worksheet, a named range is a convenient way to refer to it. Select the choice cells, open Formulas > Define Name, give the range a clear name such as StatusChoices, and confirm the worksheet scope and reference. Then select the cells for the dropdown, open Data > Data Validation, choose List, and enter =StatusChoices in Source. A name avoids typing a cross-sheet reference into the validation box and makes the rule easier to understand later.

Keep the source list in a sensible order and avoid blank cells between choices. Blank entries can appear as empty options, and a long list with duplicates makes selection harder. If several columns need the same set of choices, reuse the same named range rather than maintaining several nearly identical lists.

Keep the choices maintainable

Dropdowns do not automatically become well-managed just because their source is in a worksheet. Think about what should happen when a choice is added or removed. If the source is a fixed range such as A2:A10, entries added below row 10 are not included until the range or named range is extended. One practical approach is to reserve a reasonable number of rows for the list, while avoiding so many unused rows that the menu fills with blanks.

For a list that grows regularly, keep the source data in one clearly labeled place and update the named range when the list changes. An Excel table is useful for organizing and expanding source data, but do not assume that any table column reference will work when pasted directly into the validation Source box. A named range that refers to the intended choices is a clearer approach; test the rule after changing the source to confirm the new entries appear.

Use descriptive names and worksheet labels, especially in shared workbooks. A person maintaining the file should be able to identify which list feeds a dropdown without searching every validation rule. If you remove a choice that already appears in completed records, Excel does not automatically rewrite those existing cells. Decide whether those historical entries should remain or be corrected.

Control how Excel responds to other entries

The Settings tab also has an Ignore blank option. Leave it selected if a user may leave the cell empty; clear it if a selection is required as part of the data-entry process. A dropdown rule by itself does not make the cell visibly mandatory, so provide instructions or use a separate review method if missing values need attention.

On the Error Alert tab, you can choose whether Excel displays a message when someone enters a value that is not in the list. Keep Show error alert after invalid data is entered selected to show the alert, then choose a style:

Stop blocks an invalid value entered through ordinary typing and offers a chance to retry.
Warning displays a warning but lets the user continue if they confirm.
Information informs the user but is less restrictive.

Add a short title and message that explain the expected entry, such as “Choose a listed status.” On the Input Message tab, you can also show guidance when a validated cell is selected. These messages are useful for explaining a less obvious list, but keep them concise so they do not interrupt routine entry.

Change or remove a dropdown

To edit a rule, select one of its cells and open Data > Data Validation. Change the source, settings, input message, or error alert, then select OK. If you selected a group of cells when applying the original rule, selecting a cell from that group is usually enough to edit the shared rule. Use the Data Validation dialog’s option to apply changes to other cells with the same settings when you intend to update matching rules as well; check the selection carefully before confirming.

To remove validation, select the relevant cells, open Data > Data Validation, and choose Clear All in the dialog. This removes the rule; it does not erase the values already in the cells. If only one cell should lose its dropdown, select just that cell before clearing. For a large workbook, verify the affected cells afterward, since clearing a rule from a broad selection can remove validation from more cells than intended.

Common dropdown problems

No arrow appears: Select the cell and reopen Data Validation. Confirm Allow is List and In-cell dropdown is checked. The arrow is normally shown only while the validated cell is active.
The list is missing new choices: Check whether the source range or named range includes the added cells. A fixed range does not expand merely because more entries are typed below it.
A source on another sheet is rejected: Create a defined name for the source cells and use that name, preceded by an equals sign, in the Source box.
Invalid values still appear: Confirm the Error Alert is enabled and set to Stop. Also consider whether the values were pasted, filled, or brought in from another source; validation is not a guarantee that all existing cell contents are valid.
There are blank options: Inspect the source range for empty cells and reduce or clean the range as needed.
The source is too long to type comfortably: Put the choices in cells instead. A range is easier to review and update than a long string in the validation dialog.

A standard validation dropdown is not the same as a searchable combo box. For a very long list, users may find a worksheet search, filter, or a purpose-built form easier than scrolling through a menu. Keep the list focused on realistic choices where possible.

Excel on Mac and Excel for the web

In Excel for Mac, select the target cells and use Data > Data Validation; the dialog wording and layout can differ slightly from Windows. The same basic approach applies: choose a list rule, specify its source, and enable the in-cell dropdown if that option is shown. Keyboard shortcuts and ribbon key tips differ between Windows and Mac, so using the Data menu is a reliable route when shortcut instructions do not match your setup.

In Excel for the web, select the cells and use Data > Data validation to create or manage a rule. The available controls can differ from the desktop dialog, and some advanced workbook features are easier to manage in the Windows or Mac desktop app. If a rule uses named ranges or behaves differently after editing online, open the workbook in the desktop app and inspect its Data Validation settings.

Frequently Asked Questions

Can I make a dropdown list in Excel from another sheet?
Yes. Put the choices on that sheet, define a name for the source cells using Formulas > Define Name, then enter =StatusChoices (using your own range name) as the list Source.

Can an Excel dropdown allow more than one selection?
A standard Data Validation list allows one choice per cell. It does not provide a built-in multi-select setting. To record several choices, use separate cells or columns, or consider a form or a carefully designed macro-based solution.

How do I make a dropdown required?
Clear Ignore blank in the validation settings and use a Stop error alert for invalid typed entries. However, a rule does not automatically flag every blank cell in a completed worksheet, and pasted data can bypass validation. Use a separate check to find missing entries.

Can users type a choice instead of opening the arrow?
Yes, they can type a value into the cell. With a Stop error alert, Excel rejects an entry that does not match the allowed list during ordinary entry. Pasting or filling may bypass the validation prompt, so review imported data.

How do I update a dropdown after adding a choice?
Add the entry inside the source range or extend the defined name so it includes the new cell. If the rule uses a fixed range, changing cells outside that range will not change the dropdown.

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.