How To Create A Drop-Down List In Excel (2026)
A drop-down list lets people pick an entry from a menu instead of typing it, which keeps spelling and wording consistent.
In Excel you build one with Data Validation, using a list you type on a sheet or a list you type straight into the rule.
The steps below follow Microsoft’s support pages, including how to edit or remove the list later and why the command is sometimes greyed out.
Quick answer: make a drop-down list
| What you want | Where to go |
|---|---|
| Create the list | Data > Data Validation > Settings > Allow: List |
| Point to your entries | Click in the Source box and select the list range |
| Add or remove items | Edit the source list, or edit the Source box for a typed list |
| Remove the drop-down | Data > Data Validation > Clear All |
| Command greyed out | Unprotect the sheet or stop sharing the workbook |
Prepare your list of entries
Type the entries you want in the menu into a column on a worksheet.
Microsoft recommends turning the list into an Excel table by selecting a cell in the range and pressing Ctrl+T.
A drop-down built on a table updates automatically when you add or remove items.
Microsoft also recommends sorting the list.
Create the drop-down list step by step
- Select the cell where you want the drop-down.
- Go to the Data tab and select Data Validation.
- On the Settings tab, in the Allow box, select List.
- Click in the Source box and select your list range, leaving out the header row.
- Tick Ignore blank if people can leave the cell empty.
- Tick In-cell dropdown.
- Select OK.
Microsoft’s example uses the range A2:A9 on a sheet named Cities as the source.
What each setting does
| Setting | Where | What it does |
|---|---|---|
| Allow: List | Settings tab | Turns the cell into a menu of choices |
| Source | Settings tab | The range that holds the entries |
| Ignore blank | Settings tab | Lets people leave the cell empty |
| In-cell dropdown | Settings tab | Shows the arrow beside the cell |
| Show input message when cell is selected | Input Message tab | Shows a prompt you write, up to 225 characters |
| Show error alert after invalid data is entered | Error Alert tab | Shows a message when someone types a value that is not in the list |
Choose what happens when someone types a wrong entry
On the Error Alert tab, pick a Style and write your own title and message.
| Style | Effect |
|---|---|
| Stop | Blocks entries that are not in the list |
| Warning | Shows a warning icon but does not block other entries |
| Information | Shows an information icon but does not block other entries |
If you leave the title or text blank, Excel uses Microsoft Excel as the title and a default message saying the value is not valid.
Add or remove items later
First select a cell with the drop-down, go to Data > Data Validation, and look at the Source box on the Settings tab to see how the list was built.
| List type | How to add an item | How to remove an item |
|---|---|---|
| Table column | Type the new item at the end of the list and Excel updates the drop-down | Select the cell and press Delete |
| Named range | Type at the end, then update the range under Formulas > Name Manager in the Refers to box | Delete the item, then update the named range the same way |
| Range of cells | Type at the end, then reselect the cells in the Source box | Right-click the cell, choose Delete, then OK to shift cells up |
| Typed list | Edit the Source box and separate items with commas, no spaces, such as Yes,No,Maybe | Delete the item from the Source box |
For a range of cells, you can tick Apply these changes to all other cells with the same settings to update every cell that uses the drop-down.
Use a list from another sheet
If the entries live on a different sheet, Microsoft suggests naming the range with Define Name on the Formulas tab, in the Defined Names group.
You can then hide that sheet and protect the workbook so users cannot get at the list.
Microsoft also suggests hiding and protecting the list sheet if you want to keep people from changing it.
Remove a drop-down list
- Select the cell with the drop-down, or hold Ctrl and click several cells.
- Click Data, then Data Validation.
- On the Settings tab, click Clear All.
- Click OK.
If you do not know where the drop-downs are, press Ctrl+G, click Special, choose Data Validation, and select All or Same, then repeat the steps above.
If Data Validation is greyed out
Microsoft says the command may be unavailable because the sheet is protected or the workbook is shared.
Unprotect the sheet or stop sharing the workbook, then try again.
For a drop-down in a protected sheet, set up the validation first, then protect the sheet, and unlock the validated cells or users will not be able to choose in them.
See our guide to fixing an Excel file locked for editing if the workbook is open as read-only.
Pasting can bypass the list
Microsoft says validation messages appear only when someone types directly into a cell.
Copied or filled data does not trigger them.
To block fill and drag-and-drop, clear Enable fill handle and cell drag-and-drop under File > Options > Advanced > Editing options, then protect the worksheet.
Find cells that already have validation
On the Home tab, in the Editing group, click Find & Select, then Data Validation.
To check existing entries against a rule, select the cells and go to Data > Data Tools > Data Validation > Circle Invalid Data.
The circles disappear automatically when an entry is corrected, and the feature also works on cells that held data before validation was applied.
Other rules you can set with Data Validation
The Allow box offers more than List.
| Allow option | Use it to |
|---|---|
| Whole Number | Accept whole numbers that meet a condition |
| Decimal | Accept decimal numbers that meet a condition |
| Date | Accept dates that meet a condition |
| Time | Accept times that meet a condition |
| Text Length | Limit how long an entry can be |
| Custom | Use a formula as the rule |
Related Excel guides
To keep a header row in view while you pick from the list, see how to freeze rows and columns in Excel.
To stop others changing the rest of the sheet, see our guide to password protecting a Word or Excel file.
If a workbook locks up while you edit it, read fixing Excel not responding or freezing.
Sources
Microsoft Support: Create a drop-down list.
Microsoft Support: Add or remove items from a drop-down list.
Microsoft Support: Remove a drop-down list.
Microsoft Support: Apply data validation to cells.
Microsoft Support: More on data validation.