To create a drop down list in Excel, select the cell, go to Data > Data Validation, set Allow to List, and enter your items in the Source box. The items can come from a column on a sheet or be typed right there, separated by commas. The steps are nearly identical on Windows, Mac and Excel for the web, and Microsoft documents all three.
Get your items ready first
There are three ways to tell Excel what the choices are:
- Type them into the Source box, separated by commas, such as
Fruits,Vegetables,Grains. This works for short lists because you don’t need a helper column. - Point to a range, with the items in one column or one row, no blank cells in the middle, and no header cell included.
- Point to an Excel table, which is what Microsoft recommends. Click any cell in the range and press Ctrl+T. When you add or remove items in the table, the drop-down updates by itself.
If the list will keep growing, like a list of cities or customers, use a table. For three fixed options such as Yes, No and Maybe, typing them in Source is enough.
How to create a drop down list on Windows
- Type the items in a column, ideally inside a table (Ctrl+T). They can sit on a different sheet of the same workbook.
- Select the cell where the list should appear.
- Go to the Data tab and click Data Validation.
- On the Settings tab, set Allow to List.
- Click the Source box and select the range of items, leaving out the header cell.
- Check Ignore blank if the cell is allowed to stay empty.
- Check In-cell dropdown, which is what shows the little arrow next to the cell.
- Click OK.
Now click the cell, then the arrow, and your options appear. To reuse the list in other cells, copy the cell and paste it where you need it. The rule comes along.
Mac and Excel for the web
On a Mac, select the cells and go to Data > Tools > Data Validation (some versions label the button Validate). On the Settings tab, open the Allow pop-up menu and choose List. In Source, select the items on the sheet or type them separated by commas. The dialog shrinks while you select. Press Return or click Expand to bring it back, then click OK.
In Excel for the web, go to Data > Data Tools > Data Validation. On Settings, set Allow to List, enter the range (without the header) or the comma-separated items, check the options you want, and click OK.
Input message and error alert
Two more tabs in the same window make the list clearer for whoever fills in the sheet.
Input message
On the Input Message tab, check Show message and fill in a title and text. It appears as a tip next to the selected cell, for example “Pick the order status”. Text in this tab is limited to 225 characters.
Error alert
On the Error Alert tab, check Show error alert after invalid data is entered, pick a Style, and write the title and message. The style decides what happens when someone types something that isn’t on the list:
| Style | What happens |
|---|---|
| Stop | Blocks any value outside the list |
| Warning | Shows a message but still lets the value in |
| Information | Shows a message but still lets the value in |
If you leave the title and message blank, Excel falls back to default text. When consistent entries are the whole point of the list, as in an order tracker, Stop is the safest choice.
How to edit the list
If the list comes from a table, add a row at the bottom or delete one and the drop-down follows. That is the reason Microsoft recommends tables. If it comes from a plain range, change the values in the source column. On a Mac you can also edit the range straight in the Source box.
Another feature that lives inside the cell is the checkbox, covered in our guide to inserting a checkbox in Excel. The two work well together in a task tracker.
How to remove a drop down list
- Select the cell with the list. To clear several cells at once, hold Ctrl while you select them.
- Go to Data > Data Validation.
- On the Settings tab, click Clear All, then click OK. In Excel for the web, confirm with Apply.
Clear All removes the validation rule from the cell, and the arrow goes with it.
Data Validation is grayed out
The command is unavailable when the sheet is protected or the workbook is shared. Unprotect the sheet or stop sharing the file to continue. If you want to keep other people from changing the items, Microsoft suggests the opposite approach: put the list on its own worksheet, hide that worksheet, and protect it.
To adjust how it looks, change the column width and row height. Long items get cut off in a narrow column. If you later collect files from several people, our guide to merging PDFs helps you bundle everything into one document.
FAQ
Can I type the items instead of using a column?
Yes. In the Source box, enter the values separated by commas. You skip the helper column, but to change an item you have to reopen Data Validation.
Why don’t I see the arrow?
Check that In-cell dropdown is ticked on the Settings tab. Without it the rule still applies, but the arrow stays hidden.
Does it work in Excel for the web?
Yes. Microsoft documents the same path for the web version: Data > Data Tools > Data Validation.
Which Excel versions support it?
Microsoft’s page covers Microsoft 365, Excel 2024, 2021, 2019 and 2016, plus Excel for the web.
