Spotting duplicates in an Excel sheet by eye is impossible past about 100 rows. I learned this the hard way auditing a customer list and missing 30 duplicate entries. Excel has 5 different ways to find duplicates and each suits a different situation.
Here's when to use each method, ranked by ease.
Conditional Formatting – the visual method
Fastest way to spot duplicates. Highlight the column you want to check. Click the Home tab. Pick Conditional Formatting then Highlight Cells Rules then Duplicate Values.
Pick a color scheme. Click OK. Every duplicate value in the column gets highlighted. You can now scroll and see them at a glance.
This doesn't remove duplicates – just shows them. Use this when you want to investigate before deleting anything.
Remove Duplicates – delete in one click
If you want to actually delete duplicates, Excel has a built-in feature. Select the data range (or click anywhere in the table). Click the Data tab. Click Remove Duplicates.
A dialog appears asking which columns to check for duplicates. Check the columns that define what makes a row unique. Click OK. Excel deletes duplicates and tells you how many it removed.
Always work on a copy of your data. This permanently deletes rows and there's no undo if you save and close.
COUNTIF formula for flexibility
If you want to know exactly how many times each value appears, use COUNTIF. In an empty column next to your data, type:
=COUNTIF(A:A, A2)
This counts how many times A2's value appears in column A. Drag down to apply to all rows. Values with a count higher than 1 are duplicates.
You can then sort by this count column to group duplicates together. Or filter for counts > 1 to see only the duplicates.
Quick comparison of methods
| Method | Best for | Destructive? |
|---|---|---|
| Conditional Formatting | Visual review | No |
| Remove Duplicates | Cleaning data fast | Yes |
| COUNTIF formula | Counting frequency | No |
| Advanced Filter | Extracting unique values | No (creates copy) |
| Pivot Tables | Detailed analysis | No |
Use Advanced Filter for unique values
If you want a list of unique values without modifying the original data, use Advanced Filter. Select your data. Click Data tab. Click Advanced in the Sort & Filter group.
Pick Copy to another location. Set the Copy To range. Check Unique records only. Click OK.
Excel copies only the unique entries to the new location, leaving the original alone. Useful when you want a deduplicated copy without losing the original.
Pivot Tables for grouped counts
For complex analysis with multiple columns determining uniqueness, Pivot Tables work well. Highlight your data, click Insert then PivotTable.
Drag your key column to the Rows area. Drag the same column to the Values area (it'll auto-set to Count). The pivot shows each unique value and how many times it appears.
This is the most flexible approach for understanding duplicate patterns across multiple criteria.
Find duplicates across multiple columns
If "duplicate" means the same first name AND same last name AND same email, you need a combined check. Create a helper column that concatenates the columns:
=A2&"|"&B2&"|"&C2
Now run any of the methods above on this helper column. The pipe character separates fields so partial matches don't trigger false duplicates.
Handle near-duplicates (slight typos)
Standard Excel doesn't handle near-duplicates like "Smith" vs "Smith " (with trailing space) or "John" vs "Jon". For typo-level fuzzy matching:
- Use TRIM to remove extra spaces – wrap with =TRIM(A2)
- Use UPPER or LOWER to ignore case – =UPPER(A2)
- For complex fuzzy matching, install the Excel Fuzzy Lookup add-in from Microsoft
The combination of TRIM and LOWER catches about 80% of accidental near-duplicates without needing fuzzy matching.
What kind of duplicates are you trying to find? Drop your scenario and I'll point to the most efficient method.