Business & Productivity · Guide
How to find and highlight duplicates in Excel
Conditional formatting shows duplicates in one click, COUNTIF counts them, and a helper column finds duplicates across several columns at once. Look before you delete.
The Nextversity teamBusiness & Productivity schoolUpdated August 10, 20266 min read
On this page
The short answer
Select your data, then go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values, pick a color and click OK. Every repeated value turns that color, and nothing is deleted.
That is the fastest way to see the problem. To measure it, add a COUNTIF column. To handle duplicates that only count as duplicates across several columns, build a helper column first. All three are below.
Look before you delete. Highlighting is reversible, removing duplicates is not, and the gap between the two is where most spreadsheet accidents live.
Method 1: highlight them
- Select the range. One column, several columns, or the whole sheet.
- Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
- Choose a fill color and click OK.
Two things to know about how Excel decides. It compares per cell, not per row, so a highlighted cell means that value appears twice somewhere in your selection, not that the whole row is a repeat. And it ignores capitals, so Smith and SMITH are the same value.
To clear the highlight later: Conditional Formatting > Clear Rules > Clear Rules from Entire Sheet.
Method 2: count them with COUNTIF
Highlighting shows you where. COUNTIF tells you how many, which is what you want when someone asks how bad the problem is.
In a spare column beside your data:
=COUNTIF($A$2:$A$500,A2)
Fill it down. Every 1 is unique, every 2 or more is a duplicate, and the number is the count of copies. The dollar signs matter: they lock the range so it does not slide down as you fill.
Two variations worth keeping:
- Flag only the repeats after the first:
=COUNTIF($A$2:A2,A2)>1returns TRUE for the second and later copies, which is exactly the set Remove Duplicates would delete. - Count unique values in a range:
=SUMPRODUCT(1/COUNTIF(A2:A500,A2:A500)), as long as there are no blanks in the range.
Method 3: duplicates across several columns
Often "duplicate" means the same first name and last name and date. Excel's built-in tools compare a single value, so give them a single value to compare.
In a helper column: =A2&"|"&B2&"|"&C2
The pipe separator is not decoration. Without it, AB + C and A + BC both produce ABC and you get false matches. Now run conditional formatting or COUNTIF on the helper column and you are comparing whole records.
If your data is a proper table (Ctrl+T), the helper column formula fills down on its own as rows are added.
The two reasons Excel misses obvious duplicates
Trailing spaces. "chris@work.com " and "chris@work.com" are different strings. Fix a whole column with =TRIM(A2) in a helper column, then paste the result back as values. TRIM removes leading, trailing and repeated inner spaces.
Numbers stored as text. If some order numbers are text and others are numbers, they never match. Text sits left in its cell by default, numbers sit right, so a quick scan usually reveals it. Select the column, then Data > Text to Columns > Finish converts it in one pass.
Both problems come from exported data, which is where most duplicate hunts start. Microsoft's Excel help covers the functions in detail, and the same cleaning habits apply if you work in Google Sheets.
Deciding what to do next
Finding duplicates is the easy half. Deciding what they mean is the work:
- Genuine repeats from a double export: safe to remove.
- Same person, different details: the row you keep depends on which record is current, so sort by date before removing anything.
- Legitimate repeats: a customer with three orders is not a duplicate customer. Compare the right columns, not all of them.
If your answer to "which one do we keep" is a shrug, stop and ask someone. Spreadsheets are very good at doing exactly what you told them.
Where the real time goes
If duplicates keep appearing, the fix is usually upstream: validation on entry, one source of truth, and a summary that recalculates instead of being rebuilt. That is course territory rather than one-tip territory. The Excel certificate covers the cleaning and reporting side, Excel data analysis goes further into pivot tables and lookups, and one subscription opens both plus the rest of the Business & Productivity school.
Until then: highlight, count, then delete. In that order.
Questions people ask
How do I highlight duplicates in Excel?
Select the range, then Home, Conditional Formatting, Highlight Cells Rules, Duplicate Values. Pick a color and click OK. Every value that appears more than once in the selection turns that color.
How do I count how many times a value appears?
Use COUNTIF. In a spare column next to your data, enter =COUNTIF($A$2:$A$500,A2) and fill it down. Any result above 1 is a duplicate, and the number tells you how many copies exist.
How do I find duplicates across two columns?
Join the columns in a helper column with =A2&"|"&B2, then run COUNTIF or conditional formatting on that helper. The separator matters, so that abc and d does not collide with ab and cd.
Why does Excel say values are duplicates when they look different?
Excel ignores capitals, so Smith and SMITH match. It also compares displayed numbers loosely in some cases. If case matters, use EXACT or SUMPRODUCT rather than COUNTIF.
Why is Excel missing duplicates that clearly match?
Almost always trailing spaces or numbers stored as text. Clean the column first with =TRIM(A2) and check whether values sit on the left of their cells, which is the sign of text pretending to be a number.