Find Duplicate Rows in Excel

This page describes how to find duplicate rows in Excel. If you want to identify duplicate cells (rather than entire rows of data), you may find the Excel Duplicate Cells page more straightforward.

Note also that this page simply shows how to find the duplicated rows in a spreadsheet.

If you want to remove the repeated occurrences (but not the first occurrence) of a row in your spreadsheet, methods of doing this are described in the Remove Duplicate Rows page.

Original Data for Excel Duplicate Rows Example

In order to illustrate how to find duplicate rows in an Excel spreadsheet, we will use the above example spreadsheet on the right, which has data spanning three columns.

The first step of finding the duplicate rows is to combine the contents of each column into a single column. We will then find the duplicate values within the single combined column using two different methods.

Step 1: Combine Columns

In order to concatenate the data from columns A - C of the example spreadsheet, we use the & operator in column D of the spreadsheet. The formula to be entered into cell D2 is:

=A2 & B2 & C2

Copying this formula down all rows gives the following spreadsheet:

Concatenate Formula for Excel Duplicate Rows Example

Step 2: Highlight Duplicate Rows

Once columns A-C are concatenated into column D, we need to highlight the duplicate cells in column D. This can be done either by using Conditional Formatting or using the Excel Countif Function.

Highlight Duplicate Rows Using Conditional Formatting

The easiest way to highlight the duplicates in column D of the above spreadsheet is to use Conditional Formatting.

To do this:

Excel Conditional Formatting Duplicate Values Option

The resulting spreadsheet, with the duplicates highlighted, is shown below:

Result Showing Duplicate Rows Highlighted Using Conditional Formatting

Highlight Duplicate Rows Using The Countif Function

Warning: This method will only work if the contents of your cells are less than 256 characters in length, as Excel functions cannot handle text strings that are longer than this.

The conditional formatting method described above highlights all rows that occur more than once in the example spreadsheet.

However, sometimes you want to highlight the second (but not the first) occurrence of any duplicate rows. This can be done using the Excel Countif Function, as shown in the example spreadsheet below:

Countif Formulas for Excel Duplicate Rows Example

Using the above example spreadsheet, enter the following Countif function cell E2:

=COUNTIF( D$2:D2, D2 )

Note that this function uses a combination of Absolute and Relative Cell References. Due to this combination of reference styles, as the formula is copied down column E, it becomes,

=COUNTIF( D$2:D3, D3 )
=COUNTIF( D$2:D4, D4 )
=COUNTIF( D$2:D5, D5 )

Therefore, the formula in cell E4 returns the value 1 for the first occurrence of the combined text string "LauraCARTER#31032", but the formula in cell E7 returns the value 2 as it detects the second occurence of this text string.