How to Make a Drop Down List in Excel That Updates Itself

If more than one person types into your spreadsheet, you'll know the chaos: "Sent", "sent", "SENT!" and "done" all meaning the same thing, which wrecks every filter and count you try to run. A drop-down list fixes that by letting people pick from options you've set, so the data stays clean without you policing it.

Excel builds drop-downs with a feature called Data Validation. It sounds technical, but it's a short dialog box. I'll show you the version Microsoft recommends, a quick typed version, and the optional messages that make your list friendlier for whoever's filling it in.

Setting up the list items for your Excel drop-down

Start by typing the choices you want people to see. Microsoft suggests putting them in a single column or row, ideally on their own sheet, without blank cells.

1. Add a new sheet and give it a clear name, such as Lists.
2. Type each option in its own cell down one column, for example New, In progress and Finished.
3. Click any cell in that list and press Ctrl + T to turn it into an Excel table, then select OK.

Why bother with the table? Microsoft's help explains that a drop-down based on a table updates automatically whenever you add or remove items. That's the part that saves you hours later.

Creating the drop-down list in Excel for Windows

1. Select the cell, or the whole column, where you want the drop-down to appear.
2. Go to the Data tab and select Data Validation.
3. On the Settings tab, in the Allow box, choose List.
4. Click in the Source box, then select your list of items. Leave out the heading cell, so it doesn't show up as a choice. Microsoft's own example points at a sheet called Cities, range A2:A9.
5. Make sure In-cell dropdown is ticked. Tick Ignore blank if it's fine for people to leave the cell empty.
6. Select OK.

Click the cell and a small arrow appears on its right. Select it and your options drop down.

Typing drop-down choices straight into the Source box

For a short list that rarely changes, you can skip the separate sheet. In the Source box, type your options separated by commas. Microsoft gives Yes,No,Maybe as an example on one version of its page and Fruit,Vegetables,Grains,Dairy,Snacks on another.

It's quick, but every edit means reopening Data Validation. If your list will grow, the table method wins.

Making a drop-down list in Excel for Mac and the web

The menus move around a little, according to Microsoft's help.

• Excel for Mac: on the Data tab, under Tools, select Data Validation or Validate. Choose List from the Allow pop-up menu, select your entries in the Source box, press Return or select Expand to bring the dialog back, then select OK.
• Excel for the web: go to Data, then Data Tools, then Data Validation. Choose List under Allow and either select your cells or type entries separated by commas.

Adding a message and an error alert to an Excel drop-down

These two tabs in the Data Validation box are optional, but they're what make a shared sheet easy for other people.

Input Message

Tick the option to show a message when the cell is selected, then add a title and a short note, such as "Pick the current status". Microsoft says the message can be up to 225 characters.

Error Alert

This controls what happens when someone types something that isn't on your list. You choose a style:

Error Alert style	What happens
Stop	Blocks anything that isn't on the list
Warning	Shows a message but lets the entry through
Information	Shows a gentler note and lets the entry through

If you don't write your own text, Microsoft says Excel uses a default title and a message saying the value isn't valid and that values in the cell are restricted. I'd always write a friendlier line, like "Please choose from the list".

Changing or removing an Excel drop-down list later

If your items sit in a table, just add or delete rows in the table and every drop-down follows. If you typed them into the Source box, select the drop-down cell, open Data Validation again and edit the Source. Microsoft has separate help pages for adding or removing items and for removing a drop-down entirely.

To keep people from fiddling with the list itself, Microsoft suggests hiding and protecting the sheet that holds it. My walkthrough on locking cells so they can't be changed covers the protection side.

If Data Validation is greyed out in Excel

Microsoft gives a clear reason: the worksheet might be protected or shared. You can't change Data Validation settings while either is true. Unprotect the sheet or stop sharing, set up your list, then turn protection back on.

Other things to check if the arrow doesn't appear:

• In-cell dropdown isn't ticked on the Settings tab.
• You selected the wrong cells before opening Data Validation.
• The column is too narrow to read the options. Widen it so longer entries aren't cut off.

Drop-down lists for creators and small business trackers

I use drop-downs anywhere a column should only hold a handful of answers: post status on a content calendar, platform names on a social schedule, lead stage on a simple client tracker, or Yes and No on an order sheet. Pair them with checkboxes in Excel for tick-off tasks and your tracker practically fills itself in.

Frequently asked questions

How do I make a drop-down list in Excel from another sheet?

Type your items on the other sheet, then in Data Validation choose List and select those cells in the Source box. Microsoft's own example uses a separate sheet called Cities.

How do I make an Excel drop-down list update automatically?

Put your list items in an Excel table by selecting a cell and pressing Ctrl + T. Microsoft says drop-downs based on a table update as you add or remove items.

Can I stop people typing something that isn't on the list?

Yes. On the Error Alert tab, choose the Stop style. Warning and Information styles show a message but still allow the entry.

Why can't I click Data Validation?

Microsoft says the worksheet might be protected or shared. Remove the protection or stop sharing, then try again.

Can I add a drop-down list in Excel for the web?

Yes. Go to Data, Data Tools, Data Validation, choose List and either select your cells or type the options separated by commas.