Excel guide · 2 min read

How to find duplicates in Excel

Select the column, then go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values and click OK. Every value that appears more than once turns red. To flag them with a formula instead, use =IF(COUNTIF($A$2:$A$9,A2)>1,"Duplicate","").

Sample data

Every formula on this page was calculated live against this sheet.

	A	B	C
1	Invoice	Vendor	Amount
2	INV-501	Office Depot	312.4
3	INV-502	Staples	89.99
4	INV-503	Uline	1240
5	INV-502	Staples	89.99
6	INV-504	Grainger	455
7	INV-505	Office Depot	76.2
8	INV-503	Uline	1240
9	INV-506	Staples	210

Method 1: Highlight duplicates with conditional formatting

This is the quickest visual check, and it does not change your data. It highlights both copies of every duplicate, so INV-502 and INV-503 light up twice each.

Select the cells to check, for example A2:A9.
Go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
Leave the first box on Duplicate and choose a colour.
Click OK.
To filter to the highlighted rows, turn on Data > Filter, click the column arrow, and choose Filter by Color.

Method 2: Count each value with COUNTIF

COUNTIF tells you how many times a value appears. Anything above 1 is a duplicate. Lock the range with $ signs so it stays put when you fill the formula down.

fx=COUNTIF($A$2:$A$9,A5)

Result: 2

Returns 2, because INV-502 appears twice.

Flag duplicates with a readable label

Wrap the count in IF to get a clear label you can sort and filter on. Fill it down next to your data.

fx=IF(COUNTIF($A$2:$A$9,A2)>1,"Duplicate","")

Result:

Returns blank for INV-501 because it appears only once.

Flag only the second and later copies

Often you want to keep the first invoice and mark the repeats. Use an expanding range: the first reference is locked, the second moves as you fill down. The first INV-503 (row 4) counts 1, but the one in row 8 counts 2.

fx=IF(COUNTIF($A$2:A8,A8)>1,"Repeat","First")

Result: Repeat

Returns "Repeat" for row 8, the second INV-503.

Find duplicate rows across several columns

To match on more than one column, use COUNTIFS with a pair of range and value for each column. This flags a row only when invoice, vendor and amount all repeat.

Use $A$2:$A$9,A2,$B$2:$B$9,B2,$C$2:$C$9,C2 as the arguments and fill down.

fx=COUNTIFS($A$2:$A$9,A4,$B$2:$B$9,B4,$C$2:$C$9,C4)

Result: 2

Returns 2. Row 4 has an exact twin in row 8.

Common mistakes

Forgetting the $ signs

Without $A$2:$A$9 the range slides down as you fill, so later rows check the wrong cells. Press F4 to lock it.

Hidden spaces hide duplicates

"INV-502 " and "INV-502" look the same but do not match. Clean with TRIM first.

Conditional formatting on the whole sheet

Applying it to entire columns of large files slows Excel. Select just the data range.

Expecting the Duplicate Values rule to find duplicate rows

It compares single cells across the whole selection, so selecting A2:C9 flags any repeated value anywhere. Use COUNTIFS to check whole rows.

FAQ

How do I find duplicates in two columns?

To check whether values in one column appear in another, use =COUNTIF($B$2:$B$100,A2)>0. The compare two columns guide covers this in detail.

How do I highlight duplicates without the first one?

Use a conditional formatting formula rule: Home > Conditional Formatting > New Rule > "Use a formula", then enter =COUNTIF($A$2:A2,A2)>1 with A2 as the first selected cell.

Is the duplicate check case-sensitive?

No. Both COUNTIF and the Duplicate Values rule treat "inv-502" and "INV-502" as the same value.

How do I remove the duplicates once I find them?

Use Data > Remove Duplicates, or sort by your "Repeat" flag and delete those rows. Remove Duplicates keeps the first occurrence.

Functions used

COUNTIF Counts cells that match
COUNTIFS Counts with several conditions
IF Picks a result from a test
TRIM Removes extra spaces

Keep learning

Updated 2026-10-31