Highlight Duplicate Rows in Excel: 4 Ways

To highlight duplicate rows in Excel, use conditional formatting with a formula that checks the entire row, not just one cell. This article explains four reliable methods—including the fastest for most users—and how to handle common pitfalls like blank cells and case sensitivity.

Formula

The core formula for highlighting duplicate rows is:

=COUNTIFS($A$2:$A$100, $A2, $B$2:$B$100, $B2, $C$2:$C$100, $C2) > 1

Where:

• $A$2:$A$100, $B$2:$B$100, $C$2:$C$100 – the ranges of each column you want to compare (adjust to your data).
• $A2, $B2, $C2 – the first row of your selected range (relative references).
• COUNTIFS – counts how many rows match all the criteria simultaneously.
• > 1 – means the row appears more than once, so it is a duplicate.

The formula works by counting rows where every specified column matches the current row. If the count is greater than 1, the row is a duplicate and gets highlighted.

Worked Example

Suppose you have this data in columns A, B, and C (rows 2-5):

• Row 2: John, 25, New York
• Row 3: Mary, 30, Boston
• Row 4: John, 25, New York
• Row 5: John, 25, Chicago

Using the formula =COUNTIFS($A$2:$A$5, $A2, $B$2:$B$5, $B2, $C$2:$C$5, $C2) > 1:

1. For Row 2 (John, 25, New York): COUNTIFS counts rows that match all three – only Row 2 and Row 4 match, so count = 2 → duplicate.
2. For Row 3 (Mary, 30, Boston): only Row 3 matches, count = 1 → not duplicate.
3. For Row 4 (John, 25, New York): matches Row 2 and Row 4, count = 2 → duplicate.
4. For Row 5 (John, 25, Chicago): only Row 5 matches, count = 1 → not duplicate.

Result: Rows 2 and 4 are highlighted. Notice Row 5 is not highlighted because it differs in the City column, even though John and 25 match.

Method 1: Conditional Formatting with COUNTIFS (Most Common)

This is the standard approach and works in Excel 2010 and later. Steps:

1. Select the range of rows you want to check (e.g., A2:C100). Do not include the header row if it is a label.
2. Go to Home → Conditional Formatting → New Rule.
3. Choose Use a formula to determine which cells to format.
4. Enter the formula: =COUNTIFS($A$2:$A$100, $A2, $B$2:$B$100, $B2, $C$2:$C$100, $C2) > 1 (adjust ranges).
5. Click Format, choose a fill color, and click OK.
6. Click OK to apply.

Important: The formula uses absolute references for the range ($A$2:$A$100) and relative references for the first row ($A2). This ensures the rule checks each row against the entire range.

Method 2: Using a Helper Column (Easier for Beginners)

If formulas confuse you, add a helper column that concatenates all row values into one string, then highlight duplicates of that string. Steps:

1. Insert a new column at the end of your data (e.g., column D).
2. In D2, enter: =A2&B2&C2 (or use =TEXTJOIN(“|”,TRUE,A2:C2) in Excel 2016+).
3. Copy the formula down.
4. Select the helper column range (D2:D100).
5. Use Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values.
6. Choose a format and click OK.

This method is easier to understand and debug, but it adds a column to your sheet. You can hide it after formatting.

Method 3: Highlight Entire Row (Not Just Cells)

To highlight the whole row when a duplicate is found, use the same COUNTIFS formula but apply it to the entire row range. Steps:

1. Select the range including all columns you want highlighted (e.g., A2:C100).
2. Create a new conditional formatting rule with the formula =COUNTIFS($A$2:$A$100, $A2, $B$2:$B$100, $B2, $C$2:$C$100, $C2) > 1.
3. Set the format (e.g., fill color) and apply.

Now the entire row (all selected columns) will be highlighted, not just the duplicate cells. This is often what users mean by “duplicate rows.”

Method 4: Using Power Query (For Large Datasets)

If you have thousands of rows, Power Query (Get & Transform) can identify duplicates more efficiently. Steps:

1. Select your data and go to Data → From Table/Range (create a table if prompted).
2. In Power Query Editor, select all columns.
3. Go to Home → Remove Rows → Remove Duplicates (this removes duplicates, not highlights). To highlight, you need to add an index column and then merge queries – a more advanced technique.
4. For highlighting, you can duplicate the query, remove duplicates, then merge back to the original query on all columns, expanding the index. Rows with a matching index are duplicates.

This method is powerful but has a steeper learning curve. For most users, conditional formatting is sufficient.

Common Mistakes and How to Avoid Them

• Only checking one column: If you use COUNTIF instead of COUNTIFS, you will highlight rows that share just one value (e.g., same name) even if other columns differ. Always use COUNTIFS with all relevant columns.
• Incorrect absolute/relative references: If you use all absolute references (e.g., $A$2), the rule will only check the first row. If you use all relative (A2), the rule will shift incorrectly. Use absolute for ranges, relative for the current row.
• Blank cells: COUNTIFS treats blank cells as equal. If you have blank cells in a column, rows with blanks may be flagged as duplicates even if they are not. Consider using a formula that ignores blanks, or fill blanks with a unique placeholder.
• Case sensitivity: By default, COUNTIFS is not case-sensitive. “john” and “John” are considered duplicates. If you need case-sensitive matching, use a SUMPRODUCT with EXACT function, but that is more complex.
• Hidden rows: Conditional formatting highlights hidden rows too. If you filter data, the highlighting remains but may not be visible. Be aware of this when printing.

Frequently Asked Questions

How do I highlight duplicate rows in Excel without conditional formatting?

Use a helper column with a formula that concatenates all row values, then use the built-in “Duplicate Values” highlighting on that column. Or use Power Query to flag duplicates with an index merge.

Can I highlight duplicate rows based on two columns only?

Yes, just include only those two columns in the COUNTIFS formula. For example, =COUNTIFS($A$2:$A$100, $A2, $B$2:$B$100, $B2) > 1 will highlight rows where both columns A and B match.

How do I highlight duplicate rows but keep the first occurrence?

Use a formula that counts only rows above the current row. For example, =COUNTIFS($A$2:$A2, $A2, $B$2:$B2, $B2) > 1 will highlight only the second and later duplicates, leaving the first occurrence unhighlighted.

Why is my conditional formatting highlighting all rows?

This usually happens when the formula uses all absolute references (e.g., $A$2) instead of a mix. Check that the range references are absolute and the current row references are relative (no $ before the row number). Also ensure the selected range starts at the row where the formula is entered.

Does this work in Google Sheets?

Yes, the same COUNTIFS formula works in Google Sheets. The steps are similar: Format → Conditional formatting → Custom formula is. The syntax is identical.

How do I remove duplicate rows after highlighting?

After highlighting, you can filter by color (right-click a highlighted cell → Filter → Filter by Selected Cell’s Color) and delete the visible rows. Or use the built-in Remove Duplicates tool under Data tab, but that removes duplicates without highlighting first.

Related

• Get Column Totals in Excel: 5 Quick Ways
• Robotaxi AI Obstacle Detection & Driving Decisions
• Select Every Third Row? 3 Quick Ways

in t | Finish time | min | 50 | 0
in d | Distance | km | 10 | 0 | 100000
out pace | Average pace | min/km | t / d | 2
out speed | Average speed | km/h | d / (t / 60) | 2