A drop-down list in Excel is built with data validation. Select the cell that should carry the list, choose Data > Data Validation, set the Allow box to List, point the Source at your entries, and select OK. The cell gets an arrow, and from then on anyone filling it picks from entries you chose in advance instead of typing free text.
That recipe is the whole feature. The rest is deciding where the entries live, keeping them current, and knowing how to take the list off again. Microsoft documents the same dialog for Excel on Windows, Excel for Mac (where the command can also appear as Validate), and Excel for the web, across Excel 2016 through 2024 and Microsoft 365. Data validation’s job, per Microsoft, is restricting what users can enter into a cell, the kind of data or the specific values; a list is the version of that restriction people meet most often, because it turns the rule into a picker.
How do you create a drop-down list in Excel?
The entries can come from two places: typed straight into the rule, or read from cells on a sheet.
Type the entries into the Source box
For a short, fixed list, skip the worksheet entirely. Select the cell, open Data > Data Validation, set Allow to List, and type the entries into the Source box separated by commas, with no spaces between them: Yes,No or High,Medium,Low. Microsoft’s create page documents this route directly: values can be typed into the Source box, separated by commas.
The trade-off is that the entries now live inside the validation rule itself, visible nowhere on any sheet. For two or three stable values that is fine. For a list that will grow or that anyone else needs to inspect, use cells.
Use a cell range, and make it a table
Type the entries one per cell, in a single column, with no blank cells in between. Microsoft suggests going one step further and formatting the range as an Excel table (select a cell in the range and press Ctrl+T in Excel for Windows), and the reason is maintenance: “When your data is in a table, then as you add or remove items from the list, any drop-downs you based on that table will automatically update.”
Then select the cell or cells that should carry the list, open Data > Data Validation, set Allow to List, click into the Source box, and select the range on the sheet. Leave the header cell out of the selection; you want the entries, not the label above them.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Task | Priority | Choices | |
| 2 | Review Q3 model | High | High | |
| 3 | Update forecast | Medium | Medium | |
| 4 | Archive old files | Low |
Two check boxes on the Settings tab are worth a glance before you select OK. Ignore blank controls whether leaving the cell empty is acceptable. In-cell dropdown is what makes the arrow appear on the cell; keep it checked, because a list without its arrow still restricts entries but no longer advertises what they are.
Can the list source live on another sheet?
Yes, and Microsoft’s own procedure starts that way: its first step types the entries on a new worksheet, away from the data entry area. When you select the range while the Source box is active, Excel records the cross-sheet reference for you; the Sheet1!A1 form it writes is covered in referencing another sheet in Excel.
The tidier version of the same idea is a defined name. Name the entry range something like Departments, then use =Departments as the Source. The name reads better than an address, and it gives you a single place to maintain the list, which the editing section below comes back to. Creating names and the rules they follow are covered in named ranges in Excel.
A source sheet full of lookup lists is also something you may not want colleagues editing. Microsoft’s advice: “If the list of entries for your drop-down list is on another worksheet and you want to prevent users from seeing it or making changes, consider hiding and protecting that worksheet.”
Input messages and error alerts
The other two tabs of the Data Validation dialog decide how the list behaves toward the person using it.
On the Input Message tab, check Show input message when cell is selected and type a title and a message of up to 225 characters. The message appears when the cell is selected, before anything is typed, so it works as an instruction: “Pick a region from the list.”
On the Error Alert tab, the Style box decides what happens when someone enters a value that is not in the list. Stop requires the entry to be fixed before it can be committed. Warning flags the value as invalid and asks for a Yes or No decision. Information flags it and lets the person continue after dismissing the message. Only Stop actually blocks the value; with the other two styles, an entry from outside the list can end up in the cell.
How do you edit a drop-down list in Excel?
How you edit the list depends on which kind of source it has. There are four cases, and each has a different procedure.
The source is an Excel table
Edit the table and you are done. Type new entries at the end of the list, or remove one by right-clicking its cell and selecting Delete, then OK to shift the remaining entries up. Per Microsoft’s editing page, Excel updates every drop-down based on that table on its own; there is no dialog to revisit. This is the payoff of the table recommendation from the create step.
The source is a plain cell range
Edit the entries on the sheet first, then tell the rule about it: select a cell that carries the drop-down, open Data > Data Validation, click into the Source box, and reselect the full range including the entries you added. Before you select OK, check Apply these changes to all other cells with the same settings, so every cell sharing the rule picks up the new range in one pass instead of leaving a mix of old and new lists behind.
The source is a named range
Edit the entries on the sheet, then open Formulas > Name Manager, select the name, and reselect the full entry range in the Refers to box; select Close and confirm with Yes. Every drop-down built on the name follows, because the cells still point at the name and only the name’s target changed. The named range guide covers the Name Manager in detail.
The entries were typed into the Source box
Reopen Data > Data Validation on a cell that has the list and edit the Source line directly, keeping the entries separated by commas with no spaces between them, in the Yes,No,Maybe pattern from Microsoft’s page. Check Apply these changes to all other cells with the same settings here too, for the same reason as with a plain range.
How do you remove a drop-down list in Excel?
Select the cell with the drop-down, open Data > Data Validation, and on the Settings tab select Clear All, then OK. The rule and the arrow are gone. If several cells carry lists you want to remove, hold Ctrl and click each of them first, then run the same steps once.
When you do not know where the validated cells are, Microsoft’s removal page points at the Go To Special dialog: in Excel for Windows, press Ctrl+G, select Special, then Data Validation. Choosing All selects every cell on the sheet that has any validation rule; Same narrows that to cells whose rule matches the active cell’s. Then Clear All as above.
One thing removal does not do: it does not touch the values already in the cells. A column that spent a year offering High, Medium, and Low keeps whatever was picked; the cells only stop restricting what comes next.
Yes/no lists, dependent lists, color, and multiple picks
Four variations come up constantly.
A yes/no drop-down
This is the typed-source case at its smallest: set Allow to List and type Yes,No into the Source box, comma and no space between the entries. Nothing else changes.
Dependent (cascading) drop-down lists
A dependent list changes its entries based on another cell’s pick: choose Fruit in the first drop-down and the second offers apples and pears, choose Vegetable and it offers carrots and leeks. The standard construction combines two things this page has already used. Give each first-list option a matching named range holding its detail entries (a range named Fruit, a range named Vegetable), then set the second drop-down’s Source to an INDIRECT formula pointed at the first cell:
This works because of what INDIRECT is documented to do: it returns the reference that a text string names, and that string can be a defined name. Two caveats follow from the same mechanics. INDIRECT returns the #REF! error value when its text is not a valid reference, so the second list has nothing to offer until the first cell holds a pick that matches a name. And since range names cannot contain spaces, the first list’s options have to be single words or use underscores; Dry Goods cannot be a range name, Dry_Goods can.
Color-coded entries
The Data Validation dialog has no formatting options; it controls what can be entered, not how the cell looks. To color the picks, add conditional formatting on top: select the drop-down cells, then Home > Conditional Formatting > Highlight Cells Rules > Text that Contains, type one list value, choose its fill, and repeat with a rule per value. The list and the formatting are independent rules that happen to agree.
Multiple selections
Excel’s built-in drop-down list accepts one value per cell. The Data Validation dialog has no setting that allows several picks in the same cell, and choosing a second entry replaces the first. If a row genuinely needs several choices recorded, give each choice its own cell or column; that keeps the values usable by formulas, too.
The short version
- Create: select the cell, Data > Data Validation, set Allow to List, and set the Source to a cell range or typed comma-separated entries.
- Put the entries in an Excel table (Ctrl+T in Excel for Windows) and drop-downs based on it update themselves when the list changes.
- Edit by source type: a table needs nothing, a plain range needs the Source reselected, a named range is repointed once in the Name Manager, typed entries are edited in the Source box. Check Apply these changes to all other cells with the same settings so no cell keeps the old list.
- Remove with Clear All on the Settings tab; Ctrl+G (Windows), then Special, then Data Validation finds the cells you forgot. Existing values stay put.
- One pick per cell is the built-in behavior. Dependent lists come from named ranges plus
=INDIRECT(A2), and color comes from conditional formatting, not from the list itself. - For a plain on/off rather than a value from a list, add a checkbox instead.
A drop-down list is the List rule of data validation; the same dialog can also hold a cell to a whole number, a date, a text length, or a custom formula. Either way it is a control on what goes into a workbook, which makes the entries themselves worth governing. In a shared template, the allowed values can drift: someone extends a range, retypes a Source line, or repoints a name, and every file built on the template quietly accepts different answers than before. The list source is ordinary cells, so when two versions of a template disagree, compare the two files and the changed entries show up cell by cell instead of staying a mystery.
