To make a drop-down list in Excel, select the cells, go to Data > Data Validation, choose List under Allow, type your items separated by commas or point to a cell range in the Source box, and click OK.
Drop-down lists keep data entry consistent by letting people pick from set options instead of typing free text. The feature is called Data Validation, and it works the same way in Microsoft 365, Excel 2021, and Excel for the web. Below are the two most common ways to build one, plus how to keep it maintainable.
Method 1: type the items directly
This is the quickest approach for a short, stable list such as Yes/No or a set of statuses.
- Select the cell or cells that should have the drop-down.
- Click the Data tab, then Data Validation (in the Data Tools group).
- On the Settings tab, open the Allow box and choose List.
- In the Source box, type your items separated by commas, for example: Not started, In progress, Complete.
- Make sure In-cell dropdown is ticked, then click OK.
Now, whenever you select one of those cells, an arrow appears on the right; click it to choose a value. One limitation to keep in mind: a comma-separated list typed into the Source box is capped at 255 characters, so for anything longer than a handful of short options you should use a cell range instead, described next.
Method 2: use a range as the source
For longer lists, or lists you expect to change, it is better to keep the options in cells and reference them.
- Type your options into a column, for example F2 down to F10, ideally on a separate sheet kept for reference data.
- Select the cells that need the drop-down.
- Go to Data > Data Validation > Settings and choose List.
- Click in the Source box and select the range, or type it as =$F$2:$F$10.
- Click OK.
Which method should you use?
| Approach | Best for | Updating the list |
|---|---|---|
| Typed items (comma-separated) | Short, rarely changing lists | Edit the Source text by hand |
| Cell range | Longer lists you edit occasionally | Change the cells; extend the range if needed |
| Excel Table range | Lists that grow over time | New rows appear in the drop-down automatically |
Make the list grow automatically
If you base the source on an Excel Table, the drop-down expands as you add rows. First select your list of options and press Ctrl+T (Cmd+T on a Mac) to convert it to a Table. Then, because a plain range reference will not auto-expand, define a named range that points to the Table column and use that name in the Source box. When you add a new item to the bottom of the Table, it shows up in the drop-down with no further editing — a reliable way to keep a shared tracker current.
Control what happens with invalid entries
By default, Excel blocks values that are not on the list, but you can fine-tune the behavior on the Error Alert tab of the Data Validation dialog:
- Stop — rejects the entry entirely. Use this when only listed values are acceptable.
- Warning — shows a message but lets the user continue if they confirm.
- Information — displays a note and accepts the entry.
You can also add an input message on the Input Message tab, which appears as a tooltip when the cell is selected — handy for telling colleagues what to pick. A clear input message paired with a Stop error alert is often the difference between a form people fill in correctly and one that comes back full of typos.
Create dependent (cascading) drop-downs
A dependent drop-down narrows the second list based on the first choice — for example, pick a country and then see only its cities. The classic method uses named ranges and the INDIRECT function: name each group of options to match the value in the first list, then set the second cell’s Source to =INDIRECT(A2), where A2 holds the first selection. It takes a little setup, but it keeps long option lists manageable and prevents mismatched pairs. If you only ever need one list, you can skip this and stick with the simple methods above.
Edit or remove a drop-down
To change a list, select the cells, reopen Data > Data Validation, and edit the Source. To remove the drop-down completely, open the same dialog and click Clear All, then OK; the validation is removed but any values already in the cells stay put. If you applied validation to a whole column and want it everywhere, tick “Apply these changes to all other cells with the same settings” before saving so the change propagates consistently.
Tips for clean, shared lists
Keep your source options free of stray spaces and inconsistent capitalization so the drop-down stays tidy, and store reference lists on a dedicated tab away from your working data. A tidy layout matters when a form will be shared, so consider using merged cells for the form’s title row. If you are unsure how to structure a validation rule or an INDIRECT formula, an assistant powered by a large language model can draft one for you, though you should always test it on a copy first. And when you need to hand off a finished form as an image, our guide on how to take a screenshot on a Mac covers the shortcuts.
