Guide · Spreadsheets

How to create a drop-down list in Excel and Google Sheets

A drop-down list stops people typing "Pending", "pending " and "PEND" into the same column. Everyone picks from the same options, so your filters, totals and pivot tables actually work. Here is how to add one in Excel and Google Sheets, make it grow on its own, and link two lists together.

Want it done for you? Spreadsheet clean-up from £15

Excel: add a drop-down list in five steps

1. Type your options in a column somewhere, ideally on a separate sheet called Lists (for example A2:A6: New, Quoted, Booked, Done, Lost).
2. Select the cells where you want the drop-down, such as the whole Status column.
3. Go to Data, then Data Validation.
4. Under Allow choose List. In Source, select your options range (for example =Lists!$A$2:$A$6) or type short options separated by commas, like Yes,No,Maybe.
5. Leave In-cell dropdown ticked and press OK.

Optional: on the Error Alert tab, choose Stop so nobody can type anything that is not on the list, and on Input Message add a short hint such as "Pick a status".

Google Sheets: add a drop-down

1. Select the cells.
2. Choose Insert, then Dropdown (or right-click and choose Dropdown).
3. In the panel on the right, type each option on its own line and pick a colour for each if you like.
4. To use a list of cells instead, change the criteria to Dropdown (from a range) and select the range.
5. Under Advanced options, choose Reject the input to block anything not on the list, then click Done.

Make the list grow automatically (Excel)

If you point the drop-down at A2:A6 and later add a sixth option in A7, it will not appear. The fix is to keep the options in a Table:

1. Click inside your options and choose Insert, then Table.
2. Select the option cells (not the header), type a name such as StatusList in the Name Box to the left of the formula bar and press Enter.
3. In Data Validation, set the Source to =StatusList.

Now anything you add to the bottom of the Table shows up in every drop-down. In Google Sheets, point the range at the whole column (for example Lists!A2:A); blank cells are ignored.

Dependent drop-downs (pick a category, then an item)

Useful for things like Category then Product, or Region then Branch.

1. On your Lists sheet, put each category as a column header with its items underneath, for example Cards, Mugs and Tees.
2. Select each column of items and name it exactly the same as its header (no spaces; use Party_Bags rather than Party Bags).
3. In column A of your main sheet, add a normal drop-down of the category names.
4. In column B, add a Data Validation list with the Source =INDIRECT($A2).

Choosing "Mugs" in A2 now shows only the mug options in B2. In Google Sheets, a dropdown from a range cannot take a formula like INDIRECT, so the usual method is a helper column with =FILTER() that lists the matching items, with the second drop-down pointed at that helper range.

Edit, copy or remove a drop-down

• Edit: select a cell with the list, open Data Validation, change the Source. Tick "Apply these changes to all other cells with the same settings" to update the whole column at once.
• Copy: copy a cell with the drop-down, then Paste Special, Validation, to add it elsewhere without overwriting values.
• Remove: select the cells, open Data Validation and click Clear All. In Google Sheets, open Data, then Data validation, and delete the rule.
• Find every drop-down: in Excel, Home, then Find & Select, then Data Validation highlights them all.

Common problems

• No arrow appears: In-cell dropdown is unticked, or the sheet is protected.
• Old values do not match the list: validation only checks new entries. Use Data, then Data Validation, then Circle Invalid Data in Excel to spot old rows that need fixing.
• Blank option at the top: the source range includes an empty cell; tighten the range or use a Table.
• Options with commas: typed lists split on commas, so put options like "Smith, J" in cells and point at the range instead.

Related guides: clean up a messy spreadsheet, remove duplicates, merge two spreadsheets, keep track of job applications and household budget.

Frequently asked questions

How do I add a drop-down list in Excel?

Select the cells, go to Data, then Data Validation. Under Allow choose List, then type your options separated by commas or select a range that holds them, and press OK.

How do I add a drop-down list in Google Sheets?

Select the cells, then choose Insert, then Dropdown. Type each option in the panel on the right, or choose Dropdown (from a range) to point it at a list of cells, and click Done.

How do I make a drop-down list that updates automatically?

Keep the options in an Excel Table (Insert, then Table) and point the list at that column, or use a named range that covers the Table column. New items added to the bottom of the Table then appear in the drop-down.

How do I edit or remove a drop-down list?

In Excel, select the cells and open Data Validation again: change the Source, or click Clear All to remove it. In Google Sheets, select the cells, open Data, then Data validation, and edit or delete the rule.

Why is my drop-down list not showing?

Check that In-cell dropdown is ticked in Data Validation, that the sheet is not protected against editing those cells, and that the source range has no blank first cell. In shared workbooks the arrow only shows when the cell is selected.

Want your spreadsheet tidied and set up for you?

Send us your file and tell us which columns should only allow set answers. We clean up the existing entries so they match, add the drop-downs (including linked ones), remove duplicates and send the tidy Excel or Google Sheets file back by email. Up to 500 rows is £15 and up to 10,000 rows is £60, paid by a one-off card payment, with changes included.

Get your spreadsheet sorted · from £15

Written by TCH Works, Harlow, Essex, October 2026.