How to Highlight Duplicate Rows in Excel (With Example)

Often you may want to highlight rows in Excel if each value in the row is a duplicate of another row.

Fortunately this is easy to do using the New Rule feature within the Conditional Formatting options.

The following example shows how to do so.

Example: Highlight Duplicate Rows in Excel

Suppose we have the following dataset in Excel that contains information about various basketball players:

Suppose we would like to highlight the duplicate rows, i.e. the the rows with the exact same values in each column.

To do so, we can highlight the cell range A2:C11 and then click the Conditional Formatting icon on the Home tab along the top ribbon, then click New Rule from the dropdown menu:

In the new window that appears, click Use a formula to determine which cells to format and then type the following formula in:

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

Then click the Format button and choose a color to use for the conditional formatting.

We’ll choose light green:

Lastly, click OK.

Each duplicate row in the dataset will now be highlighted:

Excel highlight duplicate rows

Note: In this example we chose to use a light green fill for the conditional formatting of the cells, but you can choose any fill color you’d like.