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
- Copy the data first. Remove Duplicates deletes rows permanently — there is no undo after you save and close. Work on a copy.
- Select the range (or click one cell inside the data).
- Go to Data → Remove Duplicates.
- 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.
- 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
- 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.)
- Select the cells where people should choose.
- Go to Data → Data Validation.
- Under Allow, choose List. In Source, select your range (Z1:Z2) or type the values directly:
Yes,No. - 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
=ListNameas 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.