Excel Dropdown List: How to Create and Use One

To create and use a dropdown list in Excel for Microsoft 365 on Windows, select the cells, choose Data > Data Validation, set Allow to List, and enter or select the list items as the Source. Click OK, then select a cell and use its arrow to choose an item. A dropdown makes it easier to enter consistent values, such as statuses or department names, instead of typing each one by hand.

What You’ll Need

An Excel workbook and the cells where you want people to make a selection.
The choices for the list. You can type a short list directly into the validation settings, or keep the choices in a worksheet range. A range is easier to maintain when items may change.
If the choices are on a different worksheet, a workbook-level named range is the most dependable way to use them as the dropdown source.

The examples below use a list of four order statuses: New, In progress, Waiting, and Complete. You can substitute your own values. The instructions are for Microsoft Excel for Microsoft 365 on Windows; the core Data Validation feature is also available in Excel for Mac and Excel for the web, though menus and dialogs can look slightly different.

Step-by-Step

Prepare your choices. Decide what users should be able to select and write each choice once, without extra spaces before or after it. For a short, stable list, you can enter the choices directly in the Source box later. For a list you expect to edit, add a worksheet named Lists and put one choice in each cell, such as New in A2, In progress in A3, Waiting in A4, and Complete in A5. Keep the source list in a single, uninterrupted column and do not include a heading in the range you will use.
Select the destination cells. Go to the sheet where people will enter data. Select the cell or range that should have the dropdown. To apply it to a whole column of future entries, select an appropriate block of cells below the heading rather than the heading itself. For example, if statuses belong in column C, select C2:C200. You can also select several non-adjacent cells by holding Ctrl while clicking them, though applying the rule to one continuous range is simpler to manage.
Open Data Validation. On the ribbon, choose Data > Data Validation. If the Data Validation button has a menu, choose Data Validation again to open its settings. The dialog has settings for what kind of entry is allowed, how the dropdown behaves, and what message appears when someone selects the cell. If the command is unavailable, check whether the sheet or workbook is protected or whether you are editing a cell rather than selecting it.
Set the rule to a list. In the Settings tab, open the Allow menu and select List. Leave In-cell dropdown selected so Excel displays the arrow when a validated cell is active. Ignore blank is usually appropriate when entries are optional; clear it if every cell must contain a choice. This setting does not automatically fill empty cells, so it is still possible for a cell to remain blank unless you handle that separately.
Enter a short list directly, if appropriate. In Source, type the choices separated by commas, for example New,In progress,Waiting,Complete, without quotation marks. Click OK. This is convenient for a small list that is unlikely to change. Direct-entry lists have a 255-character limit, and commas are treated as separators, so this method is unsuitable for an item that itself contains a comma. Use a source range for longer or frequently updated choices.
Use a worksheet range for a maintainable list. If your choices are on the same sheet, select the destination cells, open Data > Data Validation, select List, and use the range selector beside Source to choose the cells containing the options. For example, a source in A2:A5 on the same sheet can be referenced as =$A$2:$A$5. Return to the dialog if needed, confirm the range in Source, and click OK. The dollar signs keep the reference fixed when the rule is applied to multiple cells.
Use a named range when the choices are on another sheet. On the Lists sheet, select the option cells, then choose Formulas > Define Name. Give the range a simple name with no spaces, such as StatusOptions, confirm that the reference points to the choice cells, and click OK. Select the destination cells, open Data > Data Validation, set Allow to List, and enter =StatusOptions in Source. Click OK. A named range avoids relying on a direct cross-sheet reference in the validation box and is easier to recognize later.
Choose how errors and instructions should appear. In the Data Validation dialog, use the Input Message tab if you want a short prompt to appear when a user selects a cell. For example, explain that the field records the current order status. In the Error Alert tab, keep Show error alert after invalid data is entered selected if users should be stopped from entering values outside the list. A Stop alert blocks ordinary invalid typing; Warning and Information alerts let users proceed after a prompt.
Save and test the dropdown. Click OK, then select one of the destination cells. Click the arrow that appears beside the active cell and choose an item. Enter a value that is not on the list to check the error alert, if enabled. Test more than one destination cell, especially the first and last cells in a large range. Save the workbook after confirming that the choices and error behavior are right.

In Excel for Mac, select the cells and look for Data > Data Validation; the dialog labels are similar, though the layout can differ from Windows. In Excel for the web, use the Data tab and its Data Validation command when available. If your version or current workbook does not expose the same controls, open the workbook in the Microsoft 365 desktop app to create or adjust the rule.

Common Mistakes

Selecting the wrong cells: If you select only the heading or only the first entry cell, the dropdown will not appear throughout the intended input area. Review the selected range before opening Data Validation.
Typing the wrong separator: A typed Source list uses commas between choices. Semicolons or line breaks may not divide the items as intended. If a choice contains a comma, use cells in a source range instead.
Using a source range that includes a heading or blank cells: A heading can appear as a selectable option, and blank cells can create empty-looking choices. Select only the cells containing the actual options.
Expecting the arrow to stay visible: Excel normally shows the in-cell dropdown arrow only when a validated cell is selected. The arrow disappearing when you click elsewhere does not mean the rule has been removed.
Assuming the rule cleans existing data: Adding validation does not automatically replace old entries that do not match. Use Data > Data Validation > Circle Invalid Data, where available, to identify entries that need review, then correct them against the approved choices.
Expecting validation to prevent every pasted value: Data Validation is useful for guiding normal entry, but pasted or filled-in data can introduce values that do not follow the rule. Check the column periodically if accuracy matters.

Tips

Keep source choices easy to find. Put shared options on a clearly named Lists sheet. You can hide that sheet to keep the main workbook tidy, but make sure workbook maintainers know where the choices live.
Use a named range for clarity. A name like StatusOptions is more understandable than a bare cell address when someone later edits the rule. Use a descriptive name without spaces, and verify its range if choices move.
Apply the rule before entering many rows. Select a reasonable future-entry range at the start so new records are covered. If the list is part of an Excel table, test how new rows behave in your workbook rather than assuming an existing rule will always extend exactly as intended.
Make prompts specific. An input message such as “Choose the current order status” tells a colleague what the field means. Avoid using a prompt as the only explanation of an important process; put longer instructions in a nearby note or worksheet guide.
Keep choices consistent. Use one spelling and capitalization for each option. Values such as In progress and In Progress may be treated as distinct text in later filtering or reporting, even if readers consider them equivalent.
Use a range when options change. To add an item, enter it in the source list and update the named range or selected source range if the new row falls outside it. The dropdown does not automatically include a choice placed beyond the defined source cells.

Troubleshooting

The dropdown arrow is missing. Click a cell that should have the rule and open Data > Data Validation. Confirm that Allow is set to List and In-cell dropdown is checked. Also verify that you have selected the correct sheet and cell; the arrow is shown for the active cell rather than permanently beside every cell.

The list is empty or shows the wrong options. Reopen the validation settings and inspect Source. For a range, check that it points to the correct cells and that those cells contain the choices. For a named range, open Formulas > Name Manager, select the name, and confirm its Refers to address. If you moved or deleted the source cells, repair the reference and test again.

Excel says the source evaluates to an error. Check for a misspelled named range, a missing leading equals sign before a name, or a reference to cells that no longer exist. When the source is on another worksheet, use a defined name as described above rather than typing a simple cross-sheet address into the Source box.

Users can still enter an unexpected value. Open the Error Alert tab and confirm that Show error alert after invalid data is entered is selected. Choose Stop if users should not be allowed to continue after an invalid entry. Remember that pasting values may bypass the usual typing prompt, so review data or use additional controls when a workbook needs stronger validation.

The dropdown has old choices after you update the list. Check whether the validation source covers the newly added cells. If it points to A2:A5 and you added a choice in A6, expand the range or adjust the named range. Then click the destination cell again and confirm the new choice appears.

The Data Validation command is unavailable. A protected worksheet, shared editing restrictions, or workbook state can limit changes. Check the Review tab for sheet protection and ask the workbook owner before removing protection. If you are using Excel for the web and cannot access a setting, open the file in the desktop version and make the change there.

Frequently Asked Questions

Can I add a dropdown list to multiple cells at once?
Select all the destination cells before opening Data > Data Validation, then create the rule once. If the cells already have different validation settings, Excel may ask whether to replace them; check the selection before confirming.

How do I edit or remove a dropdown?
Select the affected cell or range and open Data > Data Validation to change the source or settings. To remove the rule, open the dialog and choose Clear All, then click OK. Clearing validation does not necessarily erase values already entered in the cells.

Can the dropdown choices update when I add new items?
Yes, if the source reference includes the new cells or you update the named range to include them. A fixed range such as A2:A5 will not include an option added in A6 until you expand its reference. For a short list that rarely changes, updating the range manually is often sufficient.

Can a dropdown allow more than one selection in a cell?
Not with standard Data Validation alone. A normal dropdown replaces the cell’s contents with one selected item. Multi-select behavior generally requires a macro or a different workbook design; macros also have security and compatibility considerations.

Can I copy a dropdown to another worksheet?
Yes. Copy a cell with the validation rule and paste it where needed, or create the rule on the new sheet. If its choices live on another sheet, use a named range for the source and test the copied dropdown to confirm the reference still works.

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.