How to Create a Drop-Down List in Excel (Step by Step)
If a column should only ever contain a handful of values — Yes/No, Paid/Unpaid, a list of departments — a drop-down list stops typos like "Recieved" or "paid " from creeping in. It also makes filtering and PivotTables far more reliable.
Create a simple drop-down list
1. Select the cells where you want the drop-down (for example, C2:C100).
2. Go to Data > Data Validation. (Click the top half of the button.)
3. On the Settings tab, under Allow, choose List.
4. In Source, type your options separated by commas: Yes,No,Maybe
5. Make sure In-cell dropdown is ticked.
6. Click OK.
2. Go to Data > Data Validation. (Click the top half of the button.)
3. On the Settings tab, under Allow, choose List.
4. In Source, type your options separated by commas: Yes,No,Maybe
5. Make sure In-cell dropdown is ticked.
6. Click OK.
Click any of those cells and a small arrow appears. Click it to choose an option.
Use a list of cells instead
Typing options into the Source box is fine for two or three items. For longer lists, it's easier to keep them in cells.
1. Type your options in a column somewhere — for example, H2:H10, or on a separate sheet called "Lists".
2. Select the cells for your drop-down and go to Data > Data Validation.
3. Choose List, then click in Source and select your options with the mouse. Excel fills in something like =$H$2:$H$10.
4. Click OK.
2. Select the cells for your drop-down and go to Data > Data Validation.
3. Choose List, then click in Source and select your options with the mouse. Excel fills in something like =$H$2:$H$10.
4. Click OK.
To change the options later, just edit those cells.
Make the list grow automatically
With a normal range, if you add an item in H11, it won't appear in the drop-down. Fix that by turning the options into a table:
1. Click in your list of options and press Ctrl + T, then OK.
2. Select the table's values (not the heading).
3. Use that as the Source in Data Validation.
2. Select the table's values (not the heading).
3. Use that as the Source in Data Validation.
Now anything you add to the bottom of the table appears in the drop-down automatically.
Block anything that isn't on the list
By default, Excel stops people typing something that isn't on the list. You can customise the message:
1. In Data Validation, click the Error Alert tab.
2. Choose a Style:
• Stop — not allowed, full stop.
• Warning — asks "Continue?".
• Information — just lets them know.
3. Type a friendly Title and Error message, for example: "Please choose Paid or Unpaid from the list."
2. Choose a Style:
• Stop — not allowed, full stop.
• Warning — asks "Continue?".
• Information — just lets them know.
3. Type a friendly Title and Error message, for example: "Please choose Paid or Unpaid from the list."
Show a hint when the cell is selected
On the Input Message tab, type a short note such as "Choose a status". It pops up when someone clicks the cell.
Copy a drop-down to more cells
Copy a cell that has the drop-down (Ctrl + C), select the new cells, then right-click and choose Paste Special > Validation. Only the drop-down is pasted, not the contents or formatting.
Find cells that have drop-downs
Go to Home > Find & Select > Data Validation to highlight every cell with a drop-down on the sheet.
Remove a drop-down list
1. Select the cells.
2. Go to Data > Data Validation.
3. Click Clear All, then OK.
2. Go to Data > Data Validation.
3. Click Clear All, then OK.
The values already chosen stay in the cells — only the drop-down is removed.
Troubleshooting
The arrow doesn't appear. Make sure In-cell dropdown is ticked in Data Validation. Also check you're clicking on the right cell — the arrow only shows on the selected cell.
Data Validation is greyed out. The sheet may be protected, or you might be editing a cell (press Esc). If the workbook is shared, you may need to open it in the desktop app.
New options aren't showing. Your Source range doesn't include them. Update the range, or convert the list to a table as shown above.
Wrapping up
Data > Data Validation > List is all you need. Keep longer lists in a table so they grow by themselves, and use the Error Alert tab to keep your data clean.
