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 discussed in the Remove Duplicate Rows page.


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 duplicates in the contents of column D. This can be done either by using Conditional Formatting or using the Excel Countif Function.


Highlight Duplicate Rows Using Conditional Formatting

If you have a recent version of Excel (2007 or later) 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:

Highlight Excel Duplicate Rows Using Conditional Formatting


Highlight Duplicate Rows Using The Countif Function

Warning: The following method will not work if the contents of your combined cells are over 255 characters long, as Excel functions are limited to text strings that are less than 256 characters in length.

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

In the above example spreadsheet, the Countif function that is used to identify the duplicate rows, is entered into column E of the spreadsheet. As shown in the formula bar, the function entered into cell E2 is:

=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 )
etc.

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.