Finding duplicates is a different job from removing them, and it should almost always come first. Once you can see them, you can decide which copy is right rather than letting sheet order decide for you.
Method 1: highlight them with conditional formatting
Every value that appears more than once is now coloured. This is non-destructive, it updates as you edit, and you can filter by colour to pull the duplicates together.
The limit is the same as everywhere in Excel: it is an exact comparison, so a trailing space hides a duplicate from it.
- 1 Select the column you want to check, for example the email column.
- 2 On the Home tab choose Conditional Formatting, then Highlight Cells Rules, then Duplicate Values.
- 3 Pick a fill colour and click OK.
Method 2: count them with COUNTIF
Highlighting tells you a value repeats. Counting tells you how often, which is what you need when a record appears four times rather than twice.
In a helper column next to your data, with emails in column B:
=COUNTIF($B$2:$B$5000, B2)
Fill it down. Anything above 1 is a duplicate, and you can sort by that column to bring the worst offenders to the top. To check a combination of fields rather than one, use COUNTIFS with each column as its own criteria pair.
Method 3: a normalized helper key
This is the one that finds duplicates the other two miss. Instead of comparing the raw values, build a cleaned key and compare that.
=LOWER(TRIM(SUBSTITUTE(A2, ".", "")))&"|"&LOWER(TRIM(B2))
Now run COUNTIF against the key column instead of the raw column. Case differences, stray spaces and stray punctuation stop hiding matches. On a typical contact export this alone finds duplicates the plain method leaves behind.
What it still cannot catch is a spelling difference: Marcus and Markus produce different keys no matter how you normalize them. For that you need edit distance scoring, which is what deduplication tools use and what a spreadsheet formula cannot do.
Highlight the whole row, not just the cell
The built-in Duplicate Values rule colours the cell. On a wide sheet that leaves you scrolling sideways to see which record it belongs to. A formula rule colours the entire row instead.
- 1 Select the whole data range, from A2 across to the last column and down to the last row.
- 2 Choose Conditional Formatting, then New Rule, then Use a formula to determine which cells to format.
- 3 Enter the formula below. The dollar sign locks the column so the rule reads the same key for every cell in the row.
- 4 Pick a fill and click OK.
=COUNTIF($B$2:$B$5000, $B2)>1
One thing to know before you trust the colour: the rule highlights every copy, including the first one. It tells you a value repeats, not which row was there first, so it is a signal to look rather than a verdict about which row to keep.
Count them in a pivot table when there are a lot
Conditional formatting recalculates constantly, and on a sheet of tens of thousands of rows with several rules on it, scrolling starts to stutter. A pivot table gives you the same answer as a static list.
Put the field you are checking into Rows, put the same field into Values, and set it to Count. You get one line per distinct value with the number of times it appears, sorted highest first. That is the shape you want when you are reporting how bad the file is rather than fixing it row by row.
When the helper column stops being worth it
Helper columns are fine for one check on one file. They get fragile when there are four of them, when the file is 40,000 rows, or when someone else has to understand the sheet next month.
At that point it is worth running the file through a tool that does the normalizing and the fuzzy comparison for you and shows you the clusters it found. This data cleaning tool does that in the browser, and the file stays on your machine. If you have already decided what to remove, the companion post on removing duplicates in Excel covers the destructive step and its traps.