Excel Cell Dropdown from a List: How to Create One

To create an Excel cell dropdown from a list, select the cell, choose Data > Data Validation, set Allow to List, and enter either comma-separated choices or a range containing the choices in Source. Select In-cell dropdown and click OK; the cell will offer the list when selected.

What an Excel dropdown list does

A dropdown list is a data validation rule that lets someone choose an entry from a defined set of values. It is useful when a worksheet needs consistent inputs, such as a status, department, product category, or response. For example, a project tracker might offer only Not started, In progress, and Complete. A list reduces variations such as “In progress,” “In Progress,” and “underway,” which can make sorting and analysis harder.

The dropdown is attached to a cell or range of cells, not to the workbook as a whole. After you select a cell with the rule, Excel normally shows an arrow beside it. The arrow is available while the cell is selected; it is not a permanent button displayed in the worksheet. Users can select an option from the menu, and in many cases can also type a value directly. Whether an unlisted value is accepted depends on the rule’s error-alert settings.

Create a dropdown with typed choices

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

Select the cell or cells that should have the dropdown.
On the ribbon, choose Data > Data Validation. If the command is not visible, look in the Data Tools group on the Data tab.
On the Settings tab, set Allow to List.
Click in Source and type the choices, separated by commas, such as Not started,In progress,Complete.
Confirm that In-cell dropdown is selected, then click OK.

Select one of the cells to see the arrow and choose a value. To apply the same list to multiple cells, select the whole target range before opening Data Validation. You can also copy a cell with the rule and paste it to other cells, although pasting may replace existing content or formatting depending on the paste method.

Typed choices are convenient for a small, stable set. They are less convenient when the list is long or needs regular editing: every change requires returning to the validation dialog. The character limit for a typed Source list is also much smaller than the amount of data a worksheet can store. If choices may change or need more than a few entries, use a cell range instead.

Use cells as the dropdown source

Putting the choices in worksheet cells makes the list easier to maintain. For example, enter the three project statuses in H2:H4, then select the cells where users will make a choice. Open Data > Data Validation, choose List, and set Source to =$H$2:$H$4. The dollar signs keep the source range fixed if the validation rule is copied. Click OK and test the dropdown.

Keep the source entries together in one column or row, with no heading included in the selectable range. Avoid blank cells within the list because they can appear as empty choices. A range can be on the same sheet as the dropdown. If the source list is on another worksheet, Excel’s validation dialog may reject a direct cross-sheet range reference. A workbook-level defined name is a reliable way to refer to that list:

Select the source entries on the other sheet, excluding any heading.
Choose Formulas > Define Name, enter a short name such as ProjectStatuses, and set the scope to Workbook.
In Data Validation, choose List and enter =ProjectStatuses in Source.

Defined names make the source easier to recognize and reuse. Use a name that starts with a letter or underscore, contains no spaces, and does not resemble a cell address. If you later move or replace the source cells, check the name’s reference in Formulas > Name Manager.

Keep a changing list up to date

A plain source range does not automatically include new entries typed below it. If the list will grow, consider turning the source into an Excel table. Select the source data, including its heading, and choose Insert > Table; confirm that the table has headers. Excel expands a table when new rows are added directly beneath it, which is helpful for maintaining source data.

Validation’s Source box does not accept every table structured reference directly. One practical approach is to create a defined name that refers to the table’s data column, then use that name in the validation rule. For instance, if a table is named Table1 and its column heading is Department, define a workbook-level name such as DepartmentChoices that refers to the table column =Table1[Department]. Use =DepartmentChoices as the dropdown Source. Check the name in Name Manager if you rename the table or its column.

Tables help keep the source list current, but they do not make every dropdown update in every situation automatically. Test the rule after adding rows, and make sure the new entries are part of the table. For a simple fixed list, a normal range is easier to set up; use the table-and-name method when list maintenance matters more than the extra setup.

Control what users can enter

Data Validation includes options beyond displaying the menu. On the Settings tab, Ignore blank permits an empty cell, while In-cell dropdown controls whether the arrow appears. The Input Message tab lets you show a short prompt when a user selects the cell, such as “Choose the status that best describes the task.” This can explain the expected choice without adding worksheet clutter.

The Error Alert tab controls what happens when someone enters a value that is not in the list. Leave Show error alert after invalid data is entered selected if you want Excel to respond to an unlisted entry. The alert style affects how strict the rule is:

Stop blocks an invalid entry through normal cell editing and asks the user to correct it.
Warning warns the user but lets them choose to keep the entry.
Information displays a message but is less restrictive.

Validation is a helpful input control, not a security boundary. Pasting data into cells can bypass or replace validation in some workflows, and users may alter or remove the rule if they can edit the workbook. For important reporting, check the entries before relying on them—for example, with filters, conditional formatting, or a separate review step.

Edit, copy, or remove a dropdown

To change a dropdown, select a cell that already has the rule and open Data > Data Validation. Edit the Source, prompt, or error alert, then click OK. If you selected a group of cells with different validation settings, Excel may report that the selection contains more than one type of validation; select a single representative cell or apply the desired rule to the full target range.

To remove a rule, select the affected cells, open Data > Data Validation, and click Clear All in the dialog. This removes the validation rule; it does not automatically erase values already in the cells. To remove both the rule and cell contents, clear the cell contents separately. When copying a validated cell, remember that the copied cell may carry its validation rule along with formatting or values. Use Paste Special when you want to control which parts are copied.

On Excel for Mac, the general process is similar: select the cells, open the Data tab, choose Data Validation, and configure a list. Ribbon labels and dialog placement can vary slightly by release. Excel for the web also supports dropdown lists and validation, though some advanced editing options and dialog details may differ from the Windows desktop application. If an option is missing in the web version, open the workbook in the desktop app.

Common problems and how to fix them

The arrow does not appear: Select the cell and check Data > Data Validation. Confirm that Allow is List and In-cell dropdown is selected. The arrow usually appears only while the validated cell is active.
The source list is not accepted: Check the range syntax, defined-name spelling, and whether the source includes a heading or blank cells. If choices are separated by commas in a typed list but Excel treats them unexpectedly, use a worksheet range; regional list-separator settings can affect typed entries.
New choices are missing: A fixed source range does not expand when entries are added below it. Extend the range or use a table column referenced through a defined name.
Invalid values still appear: Confirm that the error alert is enabled and set to Stop. Also consider whether values were pasted in or the rule was removed. Validation does not guarantee that every existing cell value is valid.
Only some cells have the dropdown: Check which cells were selected when the rule was created. Select the entire intended range and apply the rule again.

When diagnosing a workbook, select a cell that behaves correctly and inspect its validation settings, then compare them with a cell that does not. This often reveals a source range that was copied incorrectly or a rule that was applied to only part of the input area.

Dropdown lists versus other ways to standardize entries

A dropdown is a good choice when users should choose from a small, known set of options. It makes valid choices visible and avoids requiring people to memorize exact spelling. However, it can be inconvenient for a very long list, such as hundreds of customer names, because scrolling and selecting is slower than searching or typing.

For a large list, consider a searchable input workflow, a form, or a lookup-based design rather than forcing users through a long menu. For a short list where users may need to enter values outside the suggestions, use a Warning or Information alert—or do not use a restrictive validation rule. An Excel dropdown is not the same as autocomplete: validation presents allowed options but does not turn the cell into a searchable combo box. Choose the method according to whether consistency, flexibility, or speed matters most.

Frequently Asked Questions

Can I make an Excel dropdown from another sheet?
Yes. Create a workbook-level defined name for the source cells on the other sheet, then use that name, preceded by an equals sign, in the validation Source box. This avoids the cross-sheet reference limitation that can affect a direct range entry.

Can I allow more than one selection in a cell?
Not with the standard Data Validation dropdown alone. It selects one value for the cell. A multi-select workflow requires a different design or automation, such as VBA, and should be tested carefully because macros change how a workbook operates.

Why can I type a value that is not in the list?
Check the Error Alert tab in Data Validation. If the alert is disabled or set to Warning or Information, Excel may allow an unlisted value. Pasting can also bypass validation behavior, so check existing data when accuracy matters.

How do I add a new option to the dropdown?
If the source is typed in the dialog, edit the Source list. If it is a cell range, add the option inside the referenced range or update the range to include the new cell. A table column used through a defined name can be easier to maintain as the list grows.

Can I copy a dropdown to other cells?
Yes. Copy a cell that has the rule and paste it to the destination, or select all destination cells before creating the rule. Check the resulting cells afterward, especially if the source reference was not made absolute or the destination already had its own validation.

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.