How to create a drop-down list in Excel

Applies to Excel for Microsoft 365, Excel 2016-2024, Excel for Mac and Excel for the web (where the button is under Data > Data Validation).

Create a drop-down list

From a list of cells (recommended)

1. Type the options in a column, for example on a separate sheet named Lists in A1:A5.
2. Select the cells where you want the drop-down.
3. Go to Data > Data Validation (Data Tools group).
4. On the Settings tab, set Allow to List.
5. Click in Source and select the range with your options (it appears as =Lists!$A$1:$A$5).
6. Keep In-cell dropdown ticked and click OK.

Typing the items directly

1. Follow steps 2-4 above.
2. In Source, type the items separated by commas: Open,In progress,Done.
3. Click OK.

Make the list update automatically

Turn your options into an Excel Table: select them and press Ctrl+T. Then point the Source at the table column, for example =INDIRECT("Table1[Status]"), or select the table's cells. New items added to the bottom of the table appear in the drop-down automatically.

Add an input message or custom error

In the same Data Validation window:

• Input Message tab: shows a tip when someone selects the cell.
• Error Alert tab: choose Stop to block anything not in the list, or Warning/Information to allow other entries with a prompt.

Edit, copy or remove a drop-down

• Edit: select a cell with the list, open Data Validation, change the Source, and tick Apply these changes to all other cells with the same settings.
• Copy: copy a cell, then Paste Special > Validation on the target cells.
• Remove: select the cells, open Data Validation and click Clear All.