Flag Duplicate Rows Across Multiple Columns

Excel Formulas › Auditing & Error-Proofing

All versions

Conditional formatting catches duplicates in one column. To find rows that repeat across several columns at once, COUNTIFS is the tool.

Quick formula: Flag any row whose combination of columns appears more than once:
=IF(COUNTIFS(A:A,A2,B:B,B2,C:C,C2)>1,"Duplicate","")

Drag down the helper column; every repeated row gets labelled.

Functions used (tap for the full reference guide):

COUNTIFSIF

The example

An order list where the same customer, date, and amount should never appear twice.

ABCD
1CustomerDateAmountFlag
2Acme06/01$200
3Bolt06/02$150
4Acme06/01$200Duplicate
5Acme06/01$200Duplicate

The formula

COUNTIFS counts rows matching all the listed column values at once:

=IF(COUNTIFS($A$2:$A$5,A2,$B$2:$B$5,B2,$C$2:$C$5,C2)>1,"Duplicate","") // more than one match means this combination repeats

How it works

Why this works:

  1. COUNTIFS counts rows where column A equals this row's A and column B equals this row's B and column C equals this row's C.
  2. A unique row matches only itself, so the count is 1.
  3. A repeated combination matches 2 or more rows, so the count is greater than 1.
  4. IF turns any count above 1 into the word "Duplicate"; otherwise it leaves the cell blank.

Use absolute ranges ($A$2:$A$5) so the formula keeps the same window as you fill it down.

Try it: interactive demo

Interactive

Set how many times a row's combination appears. Anything above 1 is flagged.

Variations

Mark only the second-and-later copies

Keep the first occurrence and flag the rest by counting from the top of the list only.

=IF(COUNTIFS($A$2:A2,A2,$B$2:B2,B2,$C$2:C2,C2)>1,"Duplicate","Keep")

Count how many true duplicate rows exist

Wrap the helper column in COUNTIF, or use SUMPRODUCT on the flag.

=COUNTIF(D2:D5,"Duplicate")

Pitfalls & errors

Trailing spaces and different capitalisation break the match. Run TRIM and consider UPPER on text columns first.

COUNTIFS treats the displayed value, so 200 and $200 stored as text can mismatch a numeric 200. Keep column types consistent.

Practice workbook

📊
Download the free Flag Duplicate Rows Across Multiple Columns practice workbook
Edit the yellow cells; the Flag column re-evaluates which rows repeat across all three columns.

Frequently asked questions

How many columns can COUNTIFS compare?
Up to 127 criteria pairs, far more than most duplicate checks need. Just add more range/criteria pairs.
Can I highlight the rows instead of labelling them?
Yes — use the same COUNTIFS expression as a conditional-formatting rule with a fill colour instead of wrapping it in IF.
Why are identical-looking rows not flagged?
Usually invisible differences: extra spaces, text-vs-number formatting, or different date serials. Clean the columns first with TRIM.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Count unique values

Function references: COUNTIFSIF