How to Show a Dropdown List in Excel

To show a dropdown list in Excel for Microsoft 365 on Windows, select the cell or cells, go to Data > Data Validation, choose List under Allow, enter or select the list items in Source, and make sure In-cell dropdown is checked. Select OK; the arrow appears when you select a validated cell.

What an Excel dropdown list does

A dropdown list lets someone choose a value from a set you specify instead of typing it freely. It is useful for standardizing entries such as department names, order statuses, priority levels, or answers to a survey. For example, a status cell might offer only Not started, In progress, and Complete. Using the same options makes the sheet easier to fill in and reduces spelling variations that can complicate sorting, filtering, or summaries.

Excel creates this kind of list with Data Validation. Validation can restrict entries in other ways too, such as allowing only dates in a given range, but the List setting creates the selectable menu. It does not turn the cell into a permanent visible button: the dropdown arrow normally shows when the cell is selected. This is different from the filter arrows in a table header, which help filter rows rather than choose a value for an individual input cell.

Create a dropdown from typed items

For a short list that will not change often, type the options directly into the Source box. First select the destination cell or range. Then use these steps in Excel for Microsoft 365 on Windows:

Open Data > Data Validation. If the command is unavailable, check whether the worksheet is protected or the workbook is in a state that prevents editing.
On the Settings tab, open the Allow menu and select List.
Leave Ignore blank selected if users may leave the cell empty. Keep In-cell dropdown selected so the arrow is available.
Click in Source and type the options separated by commas, such as Low,Medium,High.
Select OK, then select a cell with the rule to confirm the choices appear.

Direct entry is convenient for a handful of stable choices, but it has a practical limit: the typed source string cannot exceed 255 characters. Also, commas separate the choices, so an item that itself contains a comma can be awkward to enter this way. If the list is longer, includes punctuation, or may need updating, store the options in worksheet cells instead.

Use worksheet cells as the list source

Put each option in its own cell, usually in a single column. For example, enter Not started, In progress, and Complete in A2:A4. Select the destination cells, open Data > Data Validation, choose List, and use the range as the Source. On the same worksheet, select the Source box and then select A2:A4, or enter =$A$2:$A$4. The dollar signs keep the source range fixed if the validation rule is copied.

Keeping choices in cells makes them easier to inspect and edit than a comma-separated string. You can also put the source list on a separate worksheet to keep the working sheet tidy. Excel data validation generally cannot use a direct reference to another worksheet in the Source box. A common solution is to select the source cells, create a defined name with Formulas > Define Name, and enter that name in Source preceded by an equals sign, such as =StatusOptions. Choose a clear name with no spaces, and confirm that its Refers to range covers the intended choices.

If the choices are stored in an Excel Table, the table can expand as you add rows. For a validation list that should follow the expanding column, create a defined name that refers to the table column and use that name as the Source. This takes a little more setup than selecting a fixed range, but avoids having to revise the rule each time a new option is added.

Set helpful messages and control invalid entries

The Data Validation dialog includes options that make the dropdown clearer and help enforce the intended choices. On the Input Message tab, you can enable a short title and message that appear when a user selects the cell. For example, a message might say, “Choose one of the listed project statuses.” This is useful when a label or process is not self-explanatory, though it is not a substitute for a clear column heading.

On the Error Alert tab, you can choose whether Excel warns users when they enter a value that is not in the list. The Stop style prevents the invalid entry when the alert is enabled; Warning and Information allow a user to proceed after seeing a message. You can change the alert title and text to explain what to do. If users should be limited to approved choices, keep the alert enabled and use Stop. If the list is only a convenience and exceptions are acceptable, a less restrictive alert may suit the sheet better.

Validation is not a complete security or data-cleaning system. Pasting or filling data can introduce values that do not follow the intended rule, and existing invalid values are not automatically corrected when you create a dropdown. Review imported or pasted data separately if accuracy matters. You can use Data > Data Validation to inspect or change the rule for selected cells, and the dialog’s validation-circle option can help identify existing entries that fail a rule.

Make the list easier to maintain

Plan for how the list will change before applying the rule across a large workbook. A fixed source such as A2:A4 is simple, but an option entered in A5 will not be included until you update the source range. A defined name tied to a table column can make additions easier to manage. Keep the source values together, avoid blank rows within the list, and use wording that will make sense to the people completing the sheet.

To apply one rule to several cells, select the whole destination range before opening Data Validation. Excel applies the rule to the selected cells. If some cells already have different validation settings, check the option to apply the changes to other cells with the same settings only when that is what you intend. To reuse a rule elsewhere, copying a validated cell and using Paste Special > Validation can copy the validation without replacing the destination cell’s other contents or formatting.

When editing source options, remember that changing the list does not necessarily rewrite values already selected in the destination cells. If you remove In progress from the source list, cells that already contain that text can still display it; they may simply be invalid under the updated rule. This is one reason to review existing entries when changing a list that is already in use.

Why the dropdown arrow may not appear

The most common confusion is expecting the arrow to stay visible all the time. Select the cell first; the arrow is normally shown only while that cell is active. If there is no arrow after selection, open Data Validation and check that Allow is set to List and In-cell dropdown is checked. A rule can still exist with that box unchecked, but the user will not get the usual cell dropdown.

The list is empty or incomplete: Check that the Source points to the intended cells and includes every option. If you used a defined name, verify its Refers to range in Name Manager.
A typed list behaves strangely: Check the separators. Excel’s list separator can depend on regional settings, so a semicolon may be required instead of a comma on some systems. A cell range avoids many separator issues.
The arrow is present but an old value remains: Changing the source does not automatically replace values already in the destination cells. Select a new value or review the existing entries.
Users can still enter unexpected values: Confirm that the Error Alert is enabled and set to Stop if entries must be restricted. Also consider whether users are pasting data into the cells.
The command cannot be changed: A protected worksheet or restricted workbook permissions may prevent editing validation settings. The sheet may need to be unprotected by someone authorized to do so.

Another common mix-up is between an in-cell list and a filter dropdown. To filter records, select a cell in the data and use Data > Filter, or format the data as a table. Filter arrows appear in column headings and let you show or hide rows based on values. To offer a choice in an entry cell, use Data Validation instead.

Excel for Mac and Excel for the web

In Excel for Mac, the feature is also called Data Validation. Select the cells and use the Data tab’s Data Validation command; the dialog provides a List option and a Source field. Ribbon placement and dialog appearance can vary with the Excel build, but the core settings are the same. On a Mac keyboard, key combinations may differ from Windows, so using the ribbon is a dependable way to locate the command.

Excel for the web also supports working with data validation, although some dialog details and available editing options can differ from the desktop app. If a workbook’s validation rule is complex, uses names, or is difficult to edit in the browser, open it in the desktop version and check the rule there. In all versions, test the result by selecting a destination cell and confirming that the expected choices appear.

Frequently Asked Questions

How do I make a dropdown list in Excel with multiple cells?
Select all the destination cells first, then create the List rule through Data > Data Validation. The same Source and settings are applied to the selected range. Verify a few cells afterward, especially if the selection includes cells that already had different validation rules.

Can I create a dropdown list from another sheet?
Yes. Store the options on the other worksheet, create a defined name for that range using Formulas > Define Name, then enter the name with an equals sign in the validation Source box. This is usually more reliable than trying to type a direct cross-sheet reference there.

How do I add a new option to an existing dropdown?
Add the value to the source list, then check whether the validation Source includes the new cell. A fixed range must be expanded manually. If the source uses a defined name based on an expanding table column, adding a table row may extend the available options automatically.

Can I show the dropdown arrow all the time?
No. The standard in-cell validation arrow appears when a cell with the rule is selected, not as a permanently visible control in every cell. Keep a clear heading or instruction near the input column so users know where to select an option.

How do I remove a dropdown list?
Select the affected cells, open Data > Data Validation, and choose Clear All in the dialog, then select OK. This removes the validation rule; it does not necessarily erase the values already in those 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.