How to Highlight Duplicates in a Range (Multiple Columns)

You can use one of the following ways to find all instances of the same item in all the columns.

a) Highlight duplicates in Multiple columns including 1st occurrences

If the first instance of an item that appears in the data set more than once is known as a duplicate, use Excel’s built-in rule for duplicates.

You can also create a conditional formatting rule with this formula:

COUNTIF(range, top_cell)>1

b) Highlighting Entire Rows based on Duplicate Values in one Column

If you want to highlight whole rows based on duplicated records in a specific column follow the below steps:

First, select the whole rows, and then create a rule with one of the following formulas:

If you are finding duplicates from A2 to A10.

To highlight duplicate rows excluding 1st occurrences:

=COUTNIF($A$2:$A$2, $A2)>1

To highlight duplicate rows including 1st occurrences:

=COUNTIF($A$2:$A$10,$A2)>1

where A2 is the first cell and the A10 is the last cell in the column that you want to check for duplicates. You should know the use of absolute and relative cell references.

c) Highlighting Consecutive Duplicates Cells in Excel

There might occur some situations where the user does not need to highlight all duplicates in a column but show only consecutive duplicate cells. To do this, select the cells with the data( excluding the column header) and create a conditional formatting rule with one of the following formulas:

To highlight consecutive duplicates without 1st occurrence:

=$A1=$A2

To highlight consecutive duplicates with 1st occurrences:

=OR($A1=$A2, $A2=$A3)

Also Read:

• How to Remove Duplicates in Excel
• Absolute and Relative cell references

Duplicates are those which appear more than one time. Finding and highlighting duplicates in Excel is a common task that helps in data analysis and management. While working with a large Excel worksheet or converting many small worksheets into bigger ones, you may find many duplicates in it.

In this article, we will look at different ways we can find duplicates in Excel easily and quickly.