To make a dropdown list in Excel for Microsoft 365 on Windows, select the cells, choose Data > Data Validation > Data Validation, set Allow to List, enter or select the list under Source, and make sure In-cell dropdown is checked. Click OK. The list lets people choose an approved value in a cell instead of typing one from scratch.
What an Excel dropdown list does
An Excel dropdown list is a data-entry control created with Data Validation. When a user selects a cell that has a list rule, an arrow appears beside it; selecting the arrow displays the available choices. The selected item is stored as the cell’s value, so you can still sort, filter, count, or look it up like other worksheet data.
Dropdowns are useful when entries should follow a consistent set of terms. For example, a project tracker might use Not started, In progress, and Complete. Without a controlled list, people could enter variations such as Done, Finished, or complete, making filters and summaries less reliable. A dropdown helps prevent those differences at the point of entry.
It does not create a separate form or a database, and it does not automatically make a workbook secure. It is a rule attached to a cell. That distinction matters: validation can guide normal entry, but pasted or imported data may still need checking.
Create a dropdown from a typed list
For a short list that is unlikely to change, enter the options directly in the Data Validation dialog:
- Select the cell or range where users should choose an option. To apply one rule to several cells, select the whole range first.
- On the ribbon, choose Data > Data Validation > Data Validation.
- On the Settings tab, choose List in the Allow box.
- Click in Source and type the choices separated by commas, such as Low,Medium,High.
- Confirm that In-cell dropdown is checked, then select OK.
Select a cell in the range to check that the arrow appears. If you typed the items into Source, they are kept in the rule itself rather than in worksheet cells. This is convenient for a small, fixed set of choices, but editing a long list in the dialog is awkward. Also, list separators can depend on regional settings; if commas do not separate the entries as expected, use a source range in cells instead.
The Settings tab also includes Ignore blank. With this option selected, a blank cell is allowed even though the list contains choices. This is often appropriate when a field is optional. If every record must have a selection, validation alone is not a complete enforcement mechanism, especially if people can paste data into the cell.
Use worksheet cells as the list source
A range is usually easier to maintain than a typed list. Put each option in its own cell, preferably in a clearly labelled area or on a separate sheet. For example, enter department names in cells A2:A6, then select the cells that need the dropdown and open Data > Data Validation > Data Validation. Choose List and set Source to the range containing the options. If the list is on the same sheet, you can select the source cells with the mouse while the Source box is active.
Keeping the choices in cells means you can revise the list without reopening the rule to rewrite a string. It also makes the choices visible to workbook editors. Avoid including a heading such as Department in the source range unless you want that heading to appear as a selectable option. A range with blank cells may produce blank entries in the dropdown, so keep the source list compact.
Excel’s Data Validation source field generally expects a range on the same worksheet when you select cells directly. To use a list stored on another worksheet, create a defined name for the source range. Select the source cells, use Formulas > Define Name, give the range a simple name such as DepartmentList, and confirm the reference. Then set the validation Source to =DepartmentList. The leading equals sign tells Excel to use the named range.
Names cannot contain spaces, and a name should not look like a cell reference. If you need to change the source later, use Formulas > Name Manager to inspect or edit the definition. This approach keeps the dropdown source separate from the worksheet where people enter data.
Choose a source that is easy to maintain
Use a typed list for a handful of stable options, such as Yes and No. Use a cell range when options may change, when the list is longer, or when other people need to see and manage the choices. For a workbook that will grow over time, plan for new options rather than pointing the rule at a fixed range that ends too soon.
One practical approach is to format the source choices as an Excel table. Select the source data and choose Insert > Table, confirming whether the data has headers. Tables expand when you add rows. To use a table column as a validation source, define a name in Formulas > Define Name whose Refers to box points to that table column, for example =Table1[Department], and use the defined name in the validation Source box as =DepartmentList. Use the table’s actual name and column heading in your workbook. This separates the dynamic source from the validation rule and is more maintainable than repeatedly expanding a fixed cell range.
Keep the source values distinct and clear. Duplicate choices may be confusing, and a blank option can be hard to distinguish from an accidental empty entry. If order matters, arrange the source list in the order users expect, such as a workflow sequence rather than alphabetical order.
Set input messages and error alerts
Data Validation can explain what a user should enter and respond when they type a value that is not on the list. In the Data Validation dialog, the Input Message tab lets you add a title and short instruction that appears when a user selects the cell. For example, an instruction might say Choose the current project status. This is useful when the choice is not self-explanatory, but a concise message is less distracting than a long set of directions.
On the Error Alert tab, keep Show error alert after invalid data is entered selected if you want Excel to warn about an unlisted entry. The Stop style blocks ordinary typed entries that do not match the rule. Warning and Information styles allow the user to proceed after seeing a message, so they are less strict. A useful alert names the allowed choices or tells the user where to find them.
These settings improve guidance, but they should not be mistaken for comprehensive data protection. A user may paste values into validated cells, and data brought in by other processes may not have been checked against the list. If the workbook is used for reporting, review the data for unexpected values as part of the workflow.
Create dependent dropdowns carefully
A dependent dropdown changes its available choices according to another cell. A common example is selecting a country in one cell and then choosing a city in the next. This is more involved than a single list because Excel must connect each first-level choice to the matching second-level choices. One traditional method uses defined names for each group and a formula such as INDIRECT in the second validation rule. It requires names and labels to be designed consistently, so punctuation and spaces can cause problems if they are not handled deliberately.
For a small, stable workbook, named groups can be manageable. For a larger or frequently changing catalogue, consider whether a lookup-based form, a carefully designed table, or a different data-entry system will be easier to maintain. Test every combination, including a change to the first dropdown after a second choice has already been made. Excel does not necessarily erase a now-inappropriate value from the dependent cell automatically; a user may need to select a new valid option.
Modern Excel versions offer dynamic-array functions, but support for those functions and for their use in validation sources depends on the workbook design and Excel version. If a solution is shared with people using older Excel releases, test it in the oldest version that must be supported instead of assuming every recipient has the same features.
Edit, copy, or remove a dropdown
To change a rule, select a cell that has it and choose Data > Data Validation > Data Validation. Update the Source or other settings, then select OK. If the same rule applies to a range, select the full range before changing it. The dialog may offer to apply the changes to other cells with the same settings; review that choice so unrelated cells are not changed.
To apply an existing dropdown to other cells, copying the validated cell and pasting it to the destination normally copies its validation rule too. Be aware that a regular paste can also replace the destination’s contents or formatting. If you only want to copy the rule, use Paste Special and choose Validation where that option is available in the paste menu.
To remove a dropdown, select the affected cells, open the Data Validation dialog, and choose Clear All, then confirm. This clears the validation settings from the selected cells; it does not erase their existing values. If the arrow or rule remains elsewhere, check whether the selection included the full range where validation was applied.
Common problems and how to fix them
- The arrow is missing: Select the cell and check that In-cell dropdown is selected in the List settings. The arrow normally appears only while the validated cell is selected.
- The list shows one combined entry: If you typed choices directly into Source, your regional list separator may differ. Put the choices in separate cells and use that range as the source.
- A value outside the list appears in a cell: It may have been pasted or imported. Re-enter it using the dropdown, or check the data against the allowed source list.
- The dropdown is missing from some rows: The rule may not have been applied to the full range, or a paste operation may have replaced validation. Select the intended cells and inspect the Data Validation dialog.
- A new source item is not available: Check whether the validation source points to a fixed range that stops above the new item. Expand the range or use a maintained named source.
- A source on another sheet is rejected: Define a name for that range and use the name in the Source box, as described above.
To find cells containing values that do not meet their rules, open Data > Data Validation and use Circle Invalid Data, where available in the ribbon. Excel marks invalid entries with circles so they can be reviewed. This is a diagnostic aid, not a substitute for correcting the values or adjusting the rule deliberately.
Excel for Mac and Excel for the web
In Excel for Mac, the same basic feature is available through the Data tab and Data Validation command, although dialog layout and ribbon details can vary by release. Select the target cells, choose the list validation type, enter or select a source, and enable the in-cell dropdown. Keyboard shortcuts also differ between Mac and Windows, so using the ribbon is the clearest cross-platform instruction.
Excel for the web supports data validation, though its controls and available editing options can differ from the Windows desktop dialog. If a workbook relies on named ranges, dependent lists, or more advanced validation behaviour, check the rule in the desktop app before distributing it. Shared workbooks can also be opened in different Excel versions, so test the actual entry process used by collaborators.
Frequently Asked Questions
Can I make an Excel dropdown list from another sheet?
Yes. Define a name for the source range on the other worksheet, then enter that name with a leading equals sign in the Data Validation Source box, such as =DepartmentList.
Can users type a value that is not in the dropdown?
With a Stop error alert, Excel blocks ordinary typed entries that do not match the list. Pasting or importing data can bypass validation, so check data quality separately when accuracy is important.
Why does the dropdown arrow only appear sometimes?
The in-cell arrow is normally visible when you select a cell with list validation. If it does not appear then, open the rule and confirm that In-cell dropdown is checked.
How do I add a new choice to the dropdown?
Update the source list. If the validation uses a fixed range, make sure the range includes the added cell; if it uses a defined name or table-based source, check that its reference includes the new choice.
Can one dropdown depend on another?
Yes. Dependent lists can be built with named ranges and validation formulas, but they need careful setup and testing. Confirm that changing the first selection does not leave an outdated second selection in place.
Related
- Creating a Four-Quadrant Scatter Plot in Excel: Steps
- CSV Import Wizard in Excel: How to Import Files
- Create a Slicer in Excel: Setup, Filtering, and Tips
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Add a Header to One Page Only in Microsoft WordTo put a header on just one page in Word, isolate that page in its own section with…
- Name Badge Template in Word: Create One Step by StepCreate a reusable name badge template in Word by setting up a page and sizing a table to…
- Outlook Automatic Rules: How to Create and Manage ThemCreate an automatic rule in Outlook by choosing a condition, such as who sent a message or a…
- Excel Consolidate: Combine Data from Multiple SheetsTo use Consolidate in Excel for Microsoft 365 on Windows, choose a blank destination cell, go to Data…
- How to Calculate CAGR in Excel for 3 YearsTo calculate a three-year compound annual growth rate (CAGR) in Excel, divide the ending value by the beginning…
- SUMIF Negative Values in Google Sheets: Formula HelpTo total negative numbers in Google Sheets, use SUMIF with the criterion "<0". If the values are in…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.