2026-10-22・Automation / Small business / Excel

Stop typos in your spreadsheet: make a drop-down list with Data Validation (Excel and Google Sheets)

"Paid", "paid", "PAID " and "Payed" look the same to you. To a filter, a SUMIFS formula or a pivot table they are four different things — and your totals quietly go wrong.

The simplest fix is a drop-down list: people pick a value instead of typing it. This post shows how to set one up in Excel (Windows and Mac) and Google Sheets.

What a drop-down list prevents

• Spelling and capitalisation differences ("Paid" vs "paid")
• Extra spaces you can't see
• Values that aren't on the list at all ("Done?", "ask Sam")
• Inconsistent names for the same thing ("Card", "Credit card", "CC")

Good candidates: status (Quoted / Won / Lost), payment method, category, staff name, yes/no columns.

Step 1: keep the list in one place

Put the choices in their own column, ideally on a separate sheet called Lists:

Keeping the list on a sheet (instead of typing it into the rule) means you can add a choice later in one place.

Excel (Windows)

1. Select the cells where people will choose a value (for example C2:C500).
2. Go to Data → Data Validation.
3. Under Allow, choose List.
4. In Source, select your list (for example =Lists!$A$2:$A$4), or type the choices separated by commas.
5. Keep In-cell dropdown ticked and click OK.

Excel (Mac)

The steps are the same: select the cells, then Data → Data Validation, choose List under Allow, and set the Source.

Google Sheets

1. Select the cells.
2. Go to Insert → Dropdown (or Data → Data validation).
3. Choose Dropdown (from a range) and select your list, or Dropdown and type the options.
4. Under Advanced options, choose what happens with invalid data: Show a warning or Reject the input.
5. Click Done.

What happens when someone types something else?

• Excel: on the Error Alert tab of the Data Validation window, the Stop style refuses the value; Warning and Information let it through after a message. Stop is the safest for shared files.
• Google Sheets: Reject the input refuses the value; Show a warning accepts it and marks the cell.

Validation only checks what is typed. Values pasted over the cells can bypass it, so it's worth checking now and then.

Find the entries that don't match

Rules only apply to new entries. To find old mistakes:

• Excel for Windows: Data → Data Validation → Circle Invalid Data draws a circle around cells that break the rule.
• Both: a helper column such as =COUNTIF(Lists!$A$2:$A$4, C2)=0 returns TRUE for values that aren't on the list. Filter it to TRUE and fix them.

Adding a new choice later

If your list is a fixed range, add the new value inside the range (for example insert a row above the last item) so the rule picks it up. In Excel you can also turn the list into a Table (Insert → Table), give its column a name (Formulas → Define Name, referring to the table column), and use that name as the Source (for example =StatusList). The name grows with the table, so new rows are included automatically.

Quick checklist

• ☐ Choices kept on a separate Lists sheet
• ☐ Drop-down applied to the whole column you'll fill in, not just the first rows
• ☐ Invalid entries set to Stop (Excel) or Reject the input (Sheets) for shared files
• ☐ Old entries checked with a COUNTIF helper column (or Circle Invalid Data in Excel for Windows)

Related: How to number your invoices · A simple monthly expense tracker with SUMIFS

Written with the help of AI. Menu names can differ slightly between versions of Excel and Google Sheets.