Excel Conditional Formatting Duplicate Rows: Full Guide

How-to basics · Updated November 22, 2026

Yes, you can highlight entire duplicate rows in Excel using conditional formatting with a formula based on the COUNTIFS function. The exact formula is =COUNTIFS($A$2:$A$100,$A2,$B$2:$B$100,$B2,$C$2:$C$100,$C2)>1 applied to the range A2:C100, which highlights any row that appears more than once. This article explains the formula, how to set it up, how to handle blank cells, and common pitfalls.

What “Duplicate Row” Really Means

A duplicate row is not the same as a duplicate cell. When you highlight duplicate cells, Excel compares each cell individually. A duplicate row means every cell in the row matches the corresponding cell in another row. For example, rows 2 and 5 are duplicates if A2=A5, B2=B5, and C2=C5. The COUNTIFS formula checks all columns at once, so it only flags rows where every specified column matches.

How the COUNTIFS Formula Works

COUNTIFS counts rows that meet multiple criteria. In the formula =COUNTIFS($A$2:$A$100,$A2,$B$2:$B$100,$B2,$C$2:$C$100,$C2)>1, each pair (range, criteria) checks one column. The dollar signs lock the range so it doesn’t shift as the formula is applied to other cells. The relative reference (like $A2) changes for each row, so row 3 compares against A3, B3, C3, and so on. If the count is greater than 1, the row appears more than once, so the condition is TRUE and the formatting is applied.

Example: with data in A2:C10, the formula for row 2 is =COUNTIFS($A$2:$A$10,$A2,$B$2:$B$10,$B2,$C$2:$C$10,$C2)>1. If rows 2 and 7 are identical, both rows get highlighted.

Step-by-Step: Highlight Duplicate Rows

  1. Select the range that includes all rows and columns you want to check (e.g., A2:C100). Make sure the active cell is within the range.
  2. Go to Home > Conditional Formatting > New Rule.
  3. Choose Use a formula to determine which cells to format.
  4. In the formula box, enter the COUNTIFS formula with the first row of your selection as the relative reference (e.g., =COUNTIFS($A$2:$A$100,$A2,$B$2:$B$100,$B2,$C$2:$C$100,$C2)>1).
  5. Click Format, choose a fill color, and click OK.
  6. Click OK again to apply the rule.

On a Mac, the steps are identical; the ribbon paths are the same in Excel for Microsoft 365.

Handling Blank Cells and Text vs. Numbers

COUNTIFS treats blank cells as a distinct value. If two rows both have blank cells in the same column, they are considered duplicates. If you want to ignore blank cells, you can add a condition that checks if the cell is not empty, but that complicates the formula. A simpler approach is to fill blanks with a placeholder like “N/A” before applying the rule, or use a helper column that combines all values with a delimiter (e.g., =A2&"|"&B2&"|"&C2) and then highlight duplicates in that helper column using the built-in Duplicate Values rule.

Also note: COUNTIFS is not case-sensitive. “Apple” and “apple” are treated as the same. If you need case-sensitive duplicate detection, you would need a more complex array formula or a helper column using EXACT.

Common Problems and Fixes

  • Only the first cell highlights, not the whole row: This happens when you select only one column before applying the rule. Select the entire row range (A to C) before creating the rule.
  • All rows highlight: This usually means the formula’s relative references are wrong. Ensure the first row in the formula matches the first row of your selection, and that you use absolute references for the range (with $) and relative for the row number (no $ before the row number).
  • Duplicates not found because of extra spaces: Use the TRIM function in a helper column to remove extra spaces, then apply the rule to that helper column.
  • The rule only works on the first occurrence: The formula with >1 highlights all occurrences. If you want to highlight only the second and later occurrences, use >1 combined with a formula that counts occurrences up to the current row, but that’s more advanced.

Alternatives: Helper Column and PivotTable

If you prefer not to use conditional formatting, you can add a helper column with the formula =COUNTIFS($A$2:$A$100,$A2,$B$2:$B$100,$B2,$C$2:$C$100,$C2) and then filter for values greater than 1. This gives you a list of duplicate rows you can manually review. Another option is to use a PivotTable: drag all columns to the Rows area, then set the Values area to Count of any column. Rows with a count greater than 1 are duplicates. This is useful for large datasets because it summarizes duplicates without changing your data.

Performance and Large Datasets

COUNTIFS is efficient for up to tens of thousands of rows, but on very large datasets (100,000+ rows) it can slow down because it recalculates for each cell. To improve performance, limit the range to the actual data (e.g., A2:C1000 instead of entire columns). If you have more than 100,000 rows, consider using Power Query to identify duplicates, which is faster for big data.

Frequently Asked Questions

How do I highlight duplicate rows based on only one column?

Use COUNTIF with a single column: =COUNTIF($A$2:$A$100,$A2)>1. This highlights all rows where the value in column A appears more than once.

Can I highlight duplicate rows but keep the first occurrence unhighlighted?

Yes. Use a formula that counts occurrences up to the current row: =COUNTIFS($A$2:$A2,$A2,$B$2:$B2,$B2,$C$2:$C2,$C2)>1. This highlights only the second and later occurrences.

Why does my formula highlight blank rows as duplicates?

Because COUNTIFS treats blank cells as equal. If your data has blank rows, they will be considered duplicates of each other. To avoid this, add a condition that the first cell is not blank: =AND($A2<>"",COUNTIFS($A$2:$A$100,$A2,$B$2:$B$100,$B2,$C$2:$C$100,$C2)>1).

Does this work in Excel for the web?

Yes, conditional formatting with formulas works in Excel for the web. The steps are the same, but you may need to use the “Format” menu at the top.

How do I remove duplicate rows permanently?

Use the built-in Remove Duplicates feature: select your data, go to Data > Remove Duplicates, choose the columns to check, and click OK. This deletes duplicate rows, keeping the first occurrence.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.