Remove Duplicates in Excel + Data Validation Dropdowns

Real-world data is messy. The same customer appears three times with slightly different spellings, and a column where people were supposed to pick from a list contains twelve creative variations of “Yes”. Two built-in tools fix this: Remove Duplicates cleans the list you already have, and data validation dropdowns stop the mess from happening again.

Part 1: Remove Duplicates

Remove Duplicates deletes repeated rows, keeping the first occurrence of each unique value.

Step by step

  1. Copy the data first. Remove Duplicates deletes rows permanently — there is no undo after you save and close. Work on a copy.
  2. Select the range (or click one cell inside the data).
  3. Go to Data → Remove Duplicates.
  4. Tick the columns that define a duplicate. Tick one column to dedupe by that column alone; tick several to dedupe only when all of them match.
  5. Click OK. Excel tells you how many duplicates it removed and how many unique rows remain.

What counts as a duplicate?

Excel compares the full cell values in the columns you ticked. “Seoul” and “seoul” are the same for this purpose (the comparison is case-insensitive), but “Seoul” and “Seoul ” (with a trailing space) are different. Run Find & Replace or =TRIM() to clean spaces before deduping, or the duplicates will survive.

The formula alternative: UNIQUE

If you have Microsoft 365 or Excel 2021, the UNIQUE function does the same job without deleting anything: =UNIQUE(A2:A100) spills a clean list next to your data, and it updates automatically when the source changes. Google Sheets has UNIQUE too. Remove Duplicates is a one-off cleanup; UNIQUE is a live clean list.

Part 2: Data validation dropdowns

A dropdown forces everyone to pick from your list, so “Yes”, “yes”, “Y” and “yeah” become one value: “Yes”.

Step by step

  1. Type your allowed values in a small range somewhere — for example, “Yes” and “No” in cells Z1:Z2. (You can hide the column later, or put the list on a separate sheet.)
  2. Select the cells where people should choose.
  3. Go to Data → Data Validation.
  4. Under Allow, choose List. In Source, select your range (Z1:Z2) or type the values directly: Yes,No.
  5. Make sure In-cell dropdown is ticked, then click OK.

Now each cell shows a dropdown arrow. Anything typed that isn’t in the list gets rejected with a warning (you can change this to a gentle warning instead of a hard stop under the Error Alert tab).

Tips that save headaches

  • Lists on another sheet: in modern Excel and Google Sheets this works fine. In very old Excel versions, a validation list had to be on the same sheet unless you used a named range — give the list a name (Formulas → Define Name) and use =ListName as the source.
  • Dependent dropdowns: pick a category in one cell and the next dropdown shows only matching items. This uses INDIRECT and a named range per category — powerful, but build it only after the simple version works.
  • Copying validation: paste special → Validation copies just the dropdown rule to other cells.

Worked example

Before (messy) After Remove Duplicates
mina@example.com mina@example.com
jae@example.com jae@example.com
mina@example.com sora@example.com
sora@example.com
jae@example.com

Five rows become three unique emails. Then a data-validation dropdown on the “Status” column (Active / Inactive) keeps the next entries clean from the start.

Common errors

Duplicates remain after removing. Almost always invisible differences: trailing spaces, non-breaking spaces pasted from the web (use Find & Replace or CLEAN), or numbers stored as text vs real numbers.

The dropdown arrow doesn’t appear. The “In-cell dropdown” box is unticked, or the sheet is protected, or the cell is in a filtered view. Check Data Validation settings first.

“The list source must be a delimited list, or a reference to a single row or column.” Your source range spans more than one row or column. Validation lists must be a single row or a single column.

Validation lost after paste. Pasting values over a validated cell replaces the validation rule too. Paste Special → Values keeps the rule; a normal paste doesn’t.

Practice file

Download the duplicates & dropdowns practice file (.xlsx) — a messy email list ready for Remove Duplicates, a UNIQUE formula version, and a status column with a working data-validation dropdown.

Google Sheets

Remove Duplicates is under Data → Data cleanup → Remove duplicates in Sheets, and dropdowns are under Data → Data validation (with a newer “Dropdown” rule type). UNIQUE works the same in both.

All amounts in this post are in nominal dollars — they are not adjusted for inflation. Example figures are illustrative, not market data.