Highlight Duplicates in Excel Without False Hits

Select your range, then Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. That is the built-in answer and it takes six seconds. It is also the wrong tool for most real files, because it compares one cell against every other cell in the selection. On a contact list that means every repeated first name, every repeated company, and every repeated city lights up in the same colour as an actual duplicate record. The rule you probably want is a formula rule using COUNTIFS across the columns that define a record, applied to whole rows. And before either of them will tell you the truth, the trailing spaces have to go, because a single invisible space at the end of an email address makes two identical records read as unique.

The order that works is diagnosis, then formatting. Most instructions for this feature reverse it.

What does Excel's Duplicate Values rule compare?

Individual cells. Nothing else.

When you apply the preset, Excel takes every cell in the selected range, compares its value against every other cell in that same range, and formats any value that appears more than once. It does not know your data has rows. It does not know which column is a key and which is a description. If you select a block four columns wide, a first name in column A can match a company name in column D and both will be highlighted.

Microsoft's guide to conditional formatting puts the preset on the Home tab, in the Styles group, under the Conditional Formatting arrow, then Highlight Cells Rules. The path is the same on Windows and on Mac. In Excel for the web the same menu exists, and the formula-based route is Home > Styles > Conditional Formatting > New Rule with Formula chosen from the rule type list. The web version also requires your formula to evaluate to TRUE or FALSE, which matters later.

Two behaviours are worth knowing up front. The preset is not case sensitive, so smith and Smith are treated as the same value. And Microsoft notes that it cannot highlight duplicates inside the Values area of a PivotTable, so run it on the source data instead.

Why does it flag rows that aren't duplicates?

Because repetition is normal in real data, and the rule cannot tell normal repetition from a genuine duplicate record.

Think about what actually repeats in a customer list. First names repeat constantly. Companies repeat whenever two contacts work at the same place. Cities, states, plan tiers, account managers and status values repeat by design. None of that means a row is a duplicate. It means the column has a small number of possible values, which is what most columns are for.

Here is a twelve row contact list. Nothing is wrong with it that you can see.

Row	First name	Last name	Email	Company
2	Dana	Okonkwo	dana@brightpine.example	Brightpine Studio
3	James	Ferreira	james.f@northloop.example	Northloop Supply
4	Priya	Raman	priya@harborlane.example	Harbor Lane Foods
5	James	Whitlock	j.whitlock@corvin.example	Corvin Legal
6	Sarah	Delgado	sarah.d@mapleworks.example	Mapleworks
7	Tomas	Bergqvist	tomas@northloop.example	Northloop Supply
8	Lena	Vasquez	lena@rilkeco.example	Rilke and Co
9	Priya	Raman	Priya@harborlane.example	Harbor Lane Foods
10	Marcus	Adeyemi	marcus@clearfen.example	Clearfen Group
11	Sarah	Nkemelu	sarah.n@ostrom.example	Ostrom Dental
12	Ines	Kovac	ines@brightpine.example	Brightpine Studio
13	Ruben	Lindqvist	ruben@vellum.example	Vellum Press

One detail is invisible on screen and it is the whole story: the email in row 9 is stored as Priya@harborlane.example with a capital P and a trailing space. Row 4 and row 9 are the same person, entered twice.

Now select A2:D13 and apply Duplicate Values. Excel lights up fourteen cells across seven values:

Highlighted value	Cells	Is it a duplicate record?
James	A3, A5	No. Two different people.
Sarah	A6, A11	No. Two different people.
Northloop Supply	D3, D7	No. Two colleagues.
Brightpine Studio	D2, D12	No. Two colleagues.
Priya	A4, A9	Part of the real one.
Raman	B4, B9	Part of the real one.
Harbor Lane Foods	D4, D9	Part of the real one.

Four of the seven highlighted values have nothing to do with a duplicate record. They are the same colour as the three that do, so the highlighting carries no signal you can act on. Worse, the one column that would settle the question, the email address, is not highlighted at all. The capital P would not have mattered, because the preset ignores case. The trailing space does matter, so Excel reads two different strings and reports both as unique.

That is the failure mode in full. A rule that produces four confident false hits and stays silent on the column that actually identifies the record.

How do you highlight duplicate rows rather than cells?

You stop asking "does this value repeat" and start asking "does this combination of values repeat". That is a formula rule.

Select A2:D13, then Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, and enter:

=COUNTIFS($C$2:$C$13,$C2)>1

Applied to the contact list above, that rule formats the entire row of any record whose email appears more than once in the list. Pick your key column first. If the file has a real identifier, an email, an order number, an SKU, use that one column and stop. Most people reach for a multi-column rule when a single good key was sitting right there.

When no single column is a key, name the combination that makes a record unique:

=COUNTIFS($A$2:$A$13,$A2,$B$2:$B$13,$B2,$D$2:$D$13,$D2)>1

COUNTIFS and COUNTIF count cells that meet criteria, and COUNTIFS takes several range and criteria pairs that all have to match. A count greater than one means this row's combination appears somewhere else too.

The dollar signs are where most rules break, so be deliberate about them.

• $A$2:$A$13 is fully absolute. Every cell in the range checks the same twelve rows. Drop these dollar signs and the range slides as the rule fills across, which produces highlighting that looks random.
• $A2 is mixed. The column is locked so the test always reads column A no matter which of the four columns the rule is currently evaluating. The row is free so each row tests its own values.
• Write the formula from the perspective of the top-left cell of your selection. Excel applies what you type to that cell and adjusts the relative parts for everything else. If your selection starts at A2, every relative row number in the formula is 2.

Get one dollar sign wrong and the rule still runs. It just highlights the wrong cells silently, which is why people conclude conditional formatting is unreliable.

Run the email version on the sample list and it highlights nothing at all. The trailing space in row 9 defeats it. The rule is correct, the data is not, and a rule cannot tell you which of the two it is looking at.

The name and company version does catch rows 4 and 9, because those three columns happen to be clean. Treat that as luck rather than method. The same stray space in a surname would have hidden the pair just as completely, and you would have had no way to know.

Why do trailing spaces and case break the match?

Because a comparison is a string comparison, and "priya@harborlane.example" is not "priya@harborlane.example ".

Whitespace arrives in nearly every file that came out of another system. CSV exports pad fields to a fixed width. Text pasted from a web page or a PDF carries non-breaking spaces. People type a space before tabbing out of a cell. The column looks perfectly consistent and behaves as though it holds two separate values.

Case is the opposite trap, and it catches people out in the other direction. Neither the Duplicate Values preset nor COUNTIF is case sensitive, so Priya@ and priya@ match. That is usually what you want with emails and names. It is not what you want when you are auditing product codes where AB100 and ab100 are genuinely different parts.

Three fixes, in the order I would try them.

Trim the values in the file. =TRIM(A2) strips leading, trailing and repeated internal spaces. Do this once, paste the result back as values, and every rule downstream starts working. It is the only fix that improves the file rather than working around it.

Trim inside the rule. If you cannot alter the data, compare trimmed values with SUMPRODUCT instead, since COUNTIFS cannot apply a function to its range:

=SUMPRODUCT((TRIM($C$2:$C$13)=TRIM($C2))*1)>1

Handle the space that TRIM will not touch. TRIM only removes the standard space, character 32. Text pasted from the web often contains a non-breaking space, character 160, which looks identical and survives TRIM. Wrap it first: =TRIM(SUBSTITUTE(A2,CHAR(160)," ")).

For a case sensitive comparison, EXACT is the function you need, since it compares two strings and returns TRUE only when the casing matches too. Counting with it looks like this:

=SUMPRODUCT(--EXACT($C$2:$C$13,$C2))>1

The double minus converts the TRUE and FALSE results into ones and zeros so SUMPRODUCT can add them up.

Should you highlight or filter?

Highlighting is a diagnosis tool. It is excellent when you have a few hundred rows, you want to see where the duplicates sit relative to everything else, and you are still deciding what they mean.

It stops being the right tool in three situations.

Too many rows. Conditional formatting rules recalculate, and a SUMPRODUCT rule across tens of thousands of rows will make the file sluggish to scroll. Add a helper column with the same COUNTIFS formula instead, calculate it once, then sort or filter on it. A helper column is also the only version you can hand to someone else as evidence, because a colour cannot be filtered on reliably and cannot be summed.

Too many hits. If a third of the file lights up, colour is not communicating anything. Filter to the affected rows and look at them as a list.

You already know what you want to do. If the decision is made and the duplicates are going, highlighting them first is a detour. That is a different job with its own risks, and it is covered in removing duplicates without losing data.

One more thing worth deciding before you apply anything: conditional formatting is a visual layer that travels with the file. If you send that workbook to a client, they see your yellow cells with no explanation of what yellow means. Either add a note, or clear the rule before you send. The broader question of which conditional formats earn their place in a file someone else reads is covered separately.

What's the Google Sheets equivalent?

There is no Duplicate Values preset in Google Sheets. The built-in condition list covers text, dates, numbers and empty cells, and duplicates are not on it. You go straight to a custom formula.

Select your range, then Format > Conditional formatting, set the "Format cells if" dropdown to "Custom formula is", and enter the formula in the "Value or formula" box. Click Done.

The formulas are the same ones. For a single key column:

=COUNTIF($C$2:$C$13,$C2)>1

For a combination across columns, applied to the whole block:

=COUNTIFS($A$2:$A$13,$A2,$B$2:$B$13,$B2,$D$2:$D$13,$D2)>1

Two Sheets specifics to know. COUNTIF in Sheets is also not case sensitive, so the same caveat applies. And Google's documentation notes that a conditional formatting formula can only reference the same sheet using standard notation, so pulling a range from another tab needs INDIRECT.

Clean the file before you write the rule

Every problem in this post comes from the same place: the values do not say what they look like they say. A rule cannot fix that, it can only inherit it.

That is what SuperSheet's spreadsheet cleaner is for. Drop an .xlsx or .csv on it and you get a diagnosis before anything changes: how many cells carry leading or trailing spaces, which columns hold numbers Excel is treating as text, listed by name, and which headers would be re-cased. You approve the list, or you read it, learn which columns are lying to you, and close the tab. Either way you know where to point a COUNTIFS rule.

It is deliberately narrow about what it touches. It will not unmerge merged cells or split two tables sharing one tab, because those need a decision about structure that only you can make, and a bad guess produces a worse file than leaving it alone. Nothing uploads. Parsing, cleaning and the download all happen in your browser.

Run your contact list through it, then write the rule.

Frequently asked questions

How do I highlight duplicate rows across multiple columns?

Select the whole block, then Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, and use COUNTIFS with one range and criteria pair per key column: =COUNTIFS($A$2:$A$13,$A2,$B$2:$B$13,$B2,$D$2:$D$13,$D2)>1. Lock the ranges with full dollar signs and lock only the column letter on the criteria, as in $A2, so each row tests its own values. Write the formula as it applies to the top-left cell of your selection. Only include the columns that genuinely define a record. Adding a notes column to the combination will make every duplicate disappear.

Why aren't my duplicates being detected?

Almost always whitespace or a type mismatch. A trailing space makes two identical-looking strings different, and TRIM will not remove a non-breaking space pasted from a web page, so try =TRIM(SUBSTITUTE(A2,CHAR(160)," ")). The other common cause is numbers stored as text: an ID sitting as 00412 in one row and the number 412 in another will never match, and the visual clue is that text aligns left in the cell while numbers align right. Dates behave the same way, since a real date and a date typed as text are different things regardless of how they display. Fix the values, then rerun the rule.

Is Excel's duplicate check case sensitive?

No. The Duplicate Values preset treats smith and Smith as the same value, and COUNTIF and COUNTIFS are not case sensitive either. Microsoft states plainly that COUNTIF criteria are not case sensitive, so apples and APPLES match the same cells. For a case sensitive comparison you need EXACT, which compares two text strings and returns TRUE only when the casing matches as well. Count with =SUMPRODUCT(--EXACT($C$2:$C$13,$C2))>1 as a formula rule. Reach for this on product codes and reference numbers, where case carries meaning. For names and emails, the default insensitive behaviour is the one you want.

How do I highlight only the second occurrence?

Use an expanding range so each row only counts the rows above it. Applied to C2:C13 as a formula rule: =COUNTIF($C$2:$C2,$C2)>1. The start of the range is absolute and the end is relative, so in row 2 the rule counts C2:C2, in row 3 it counts C2:C3, and so on. The first appearance of a value counts one and stays unformatted. Every later appearance counts two or more and gets highlighted. This is the version to use when you want to keep the original and mark the copies, and it lines up with how Remove Duplicates behaves, since that command keeps the first occurrence and deletes the rest.