How to Highlight Duplicate Rows in Excel

Method 1. Highlighting Duplicate Rows in One Column with the Built-in Rule in Excel

1.1. Including First Occurrence

This is the sample dataset.

Select the range B5:B14.
Go to the Home tab and click Conditional Formatting in the Style section.
Click Highlight Cell Rules.

Select Duplicate Values.

In the Duplicate Values dialog box, choose Duplicate.
Select a format. Red Text, here.

Click OK to highlight duplicate rows .

1.2. Excluding First Occurrence

Select the range B5:B14.
Choose Home > Conditional Formatting > New Rule.

In New Formatting Rule, select Use a Formula to Determine Which Cells to Format.
Enter this formula Format Values Where This Formula is True.

=COUNTIF($B$5:$B5,$B5)>1

The COUNTIF function counts cells in the $B$5:$B5 range based on the criteria in $B5.

Click Format to select a format style for the highlighted rows.

Here, Bold as the Font style and Red as the Color.

Click OK twice.

The highlighted duplicate rows are showcased without the first occurrence.

Method 2. Inserting the COUNTIFS Function to Highlight Duplicate Rows in Excel

This is the dataset.

Select the dataset and click Home > Conditional Formatting > New Rule.

In New Formatting Rule, select Use a Formula to Determine Which Cells to Format.
Enter this formula in Format Values Where This Formula is True.

=COUNTIFS($B$5:$B$14,$B5,$C$5:$C$14,$C5,$D$5:$D$14,$D5)>1

Select Format.

The COUNTIFS function applies the criteria ($B5, $C5, $D5) across the $B$5:$B$14, $C$5:$C$14 and $D$5:$D$14 ranges and counts cells that meet the criteria.