Excel Dropdown List: Create and Manage Cell Choices

To make a dropdown list in an Excel cell, select the cell, choose Data > Data Validation, set Allow to List, and enter the choices in Source or select a range containing them. Leave In-cell dropdown selected, then click OK. The cell will show an arrow when selected, letting someone choose an allowed entry instead of typing it.

What an Excel dropdown list does

An in-cell dropdown is a type of data validation: it offers a set of permitted values for a cell. It is useful when entries need to stay consistent, such as department names, order statuses, product sizes, or yes/no responses. Instead of accepting variations like “In progress,” “In Progress,” and “in-progress,” a list gives users a standard choice.

The dropdown does not change the cell into a special object. The selected value is still ordinary cell content, so it can be sorted, filtered, counted, or used in formulas like other text. Data Validation controls what people can enter through normal editing, but it is not a complete security feature: depending on how data is added, pasted or imported content may bypass the validation rule.

Create a dropdown from a typed list

For a short list that is unlikely to change, you can type the choices directly into the validation settings. In Excel for Microsoft 365 on Windows:

Select the cell or cells where the dropdown should appear. To apply the same list to a block, select the whole block before opening the settings.
Go to Data > Data Validation. If the command is unavailable, check whether the worksheet is protected or whether you are editing a cell rather than selecting it.
On the Settings tab, set Allow to List.
In Source, type the choices separated by the list separator used by your Excel and Windows regional settings. A comma is common, but some regional settings use a different separator.
Keep In-cell dropdown selected. Optionally keep Ignore blank selected if an empty cell should be allowed.
Choose OK, then select a cell in the range and use its arrow to choose an entry.

For example, a short status list might contain Open, In progress, and Closed. Because the separator depends on regional settings, if Excel treats the whole entry as one choice, edit Source and try the separator your system uses. A typed list is convenient for a few stable options, but it can be awkward to maintain: changing the choices means reopening the rule, and the directly typed source has a 255-character limit.

Build the dropdown from worksheet cells

A cell range is usually the better source when the choices may change or when the list is long. Put one option in each cell in a dedicated area, such as a Lists worksheet. For instance, enter North, South, East, and West in A2:A5. Then select the destination cells, choose Data > Data Validation, set Allow to List, and set Source to the range, such as =Lists!$A$2:$A$5. The equals sign identifies the source as a reference; the dollar signs keep the range fixed.

A separate worksheet helps keep the main data-entry area uncluttered. You can hide the Lists worksheet if that makes the workbook easier to use, though hiding it does not protect the source values. Avoid including a heading such as “Region” in the source range unless you want that heading to appear as a selectable option. Blank cells in the source range can create blank choices, so use a clean range containing only the intended entries.

For a list that should expand as new entries are added, format the source list as an Excel table: select the source data and choose Insert > Table, confirming whether the range has headers. Tables expand when rows are added. To use a table column reliably as a validation source, create a defined name that refers to that column, then enter the name in the Data Validation Source box with an equals sign. Create or manage names through Formulas > Define Name. This separates the source setup from the rule and is easier to maintain than repeatedly editing a fixed range.

Choose the right source method

Use the method that fits how often the choices are likely to change and who will maintain the workbook:

Typed entries: Best for a small, stable set such as Yes and No. It is quick to set up, but edits must be made in the validation rule and separators can vary by regional settings.
Fixed cell range: Best when the source values are visible and maintained in worksheet cells. It is easy to understand, but newly added options outside the specified range will not appear automatically.
Named range or table-backed source: Best for a shared workbook or a list expected to grow. A clear name makes the validation rule easier to read, and a table can grow with added rows. Set up and verify the defined name in the workbook before relying on it.

For a one-off worksheet, a fixed range may be all you need. For a workbook that will be reused, a maintained source list reduces the chance that an old option remains in one validation rule while a newer option is added elsewhere.

Set input messages and error alerts

Data Validation can explain what users should do and respond when they enter a value outside the list. In the Data Validation dialog, use the Input Message tab to show a short instruction when a validated cell is selected. For example, a message could say, “Choose the current order status from the list.” This is especially helpful when the cell’s purpose is not obvious.

On the Error Alert tab, keep Show error alert selected if Excel should warn when someone types an unlisted value. The Stop style prevents a typical invalid entry from being accepted through direct typing; Warning and Information styles allow the user to continue after a prompt. Choose the style that matches the workflow. A strict Stop alert is suitable for controlled data entry, while a less restrictive setting may be useful when users sometimes need an exception.

These controls improve data quality but do not guarantee that every value in the column is valid. Pasting, filling, importing, or other workbook operations may leave invalid values. If the list changes, existing entries that are no longer on the list are not automatically replaced. Review the data separately when accuracy matters.

Apply, edit, copy, or remove a rule

Selecting a multi-cell range before creating the rule applies it to every selected cell. To copy the rule later, copy a cell with the validation and paste it into the destination cells; ordinary paste can also replace other cell contents or formatting, so use Paste Special when you only want the validation. In Windows Excel, use Home > Paste > Paste Special and choose Validation when that option is available in the dialog.

To inspect or change a rule, select a cell that uses it and open Data > Data Validation. If the selection contains cells with different rules, Excel may not show one shared rule to edit. Use Find & Select or inspect smaller ranges if you are unsure which cells have validation. To remove a rule, select the relevant cells, open Data > Data Validation, and choose Clear All in the dialog; this clears the validation settings, not the cell values.

You can also use the Data Validation dialog’s Circle Invalid Data command to mark values that do not meet the current rule. This can help identify entries that became invalid after a source list changed or were introduced by paste. Review the marked cells and correct or explicitly retain exceptions as appropriate.

Create a dependent dropdown

A dependent dropdown is a second list whose choices depend on the first selection. For example, a user could choose a region in one cell and see only the cities for that region in the next. This takes more setup than a basic list: you need separate source lists and a way for the second validation rule to refer to the selected category.

One traditional approach uses defined names for each category and the INDIRECT function in the second list’s Source field. The selected category must correspond to a valid defined name; names cannot contain spaces, so categories such as “North Region” need a naming convention or a transformation. This approach is manageable for a small, carefully designed workbook, but it can be fragile when categories are renamed or contain punctuation. For larger or frequently changing datasets, consider a more structured design and test it in the Excel versions used by everyone who will open the file.

Whichever approach you choose, decide what should happen when the first selection changes. A value already chosen in the dependent cell may no longer belong to the new category, and Excel does not necessarily clear that old value for you. Users may need to select a new dependent value, or the workbook can include a separate review process for mismatched pairs.

Common problems and how to fix them

No dropdown arrow appears: Select the cell and check Data > Data Validation. Confirm Allow is List and In-cell dropdown is selected. Also check whether the cell is merged or the worksheet is protected.
The source range is rejected: Check that the reference starts with an equals sign, points to a valid range, and uses the correct worksheet name. A sheet name with spaces needs single quotation marks around the name, as in ='Sales Lists'!$A$2:$A$8.
There is only one choice when several were intended: The typed list may use the wrong regional separator. Try a worksheet range instead, which avoids entering separators between choices.
New options do not show up: A fixed source range does not automatically include cells added below it. Expand the range or switch to a properly configured table-backed named source.
An invalid value still appears: It may have been pasted, imported, or entered under a permissive error-alert setting. Use Circle Invalid Data and verify the alert settings.
Duplicate choices appear: Remove duplicates from the source list. Data Validation displays the source entries; it does not automatically make them unique.

Excel on Mac and Excel for the web

In Excel for Mac, the basic workflow is also to select the cells and open Data > Data Validation, then choose a list and specify its source. The precise dialog layout can vary by Excel release, but the important settings are the same: a List validation type, a source, and an in-cell dropdown. On Mac, shortcuts and some ribbon arrangements differ from Windows, so the Data tab is the dependable route rather than relying on a Windows-specific keyboard sequence.

Excel for the web supports data validation, but available controls and dialog details can differ from the desktop app, and some advanced workbook features are easier to manage in desktop Excel. If a workbook uses named ranges, dependent lists, or a complicated source setup, verify the result in the version where it will be maintained. If a command is not available in the browser, open the workbook in the desktop app to configure it.

Frequently Asked Questions

Can I put a dropdown list in multiple cells at once?

Yes. Select all the destination cells before opening Data > Data Validation and create the list rule once. Check the result in several cells afterward, especially if the selection includes cells that already had different rules.

Can I let users type a value that is not in the list?

Yes. In Data Validation, open the Error Alert tab and clear Show error alert, or choose a less restrictive alert style. The list can still provide convenient choices, but users may enter other values. This makes consistency checks more important.

Does changing the source list update existing cell values?

No. Changing or removing an option from the source changes the choices offered going forward; it does not automatically rewrite values already selected in destination cells. Review existing entries if the permitted choices have changed.

Can I use a list on another worksheet?

Yes. A source list can be stored on another worksheet. For a straightforward setup, refer to that range in the Source box. If Excel does not accept a cross-worksheet reference in your setup, define a name for the source range and use that name in the validation rule.

Why can someone paste a value that is not in the dropdown?

Data Validation is designed to guide entry, not to lock down a workbook against every route for adding data. Pasting and importing can bypass or replace validation behavior. Use error alerts and review tools, and check the data after bulk updates if every entry must conform.

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.