ExcelFunctions.net

Search Site:

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.

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.

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:

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.

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:

- Highlight the range of cells to be formatted (cells D2 - D10 in the example spreadsheet);
Select the Excel Conditional Formatting drop-down menu from the Home tab at the top of your Excel workbook.

Within this menu:

- Select the
option and from the secondary menu that appears, select the__H__ighlight Cells Rulesoption;__D__uplicate Values... A 'Duplicate Values' dialog box will pop up. Ensure that the drop down menu in the left hand side of this dialog box shows the value 'Duplicate'.

Click**OK**.

- Select the

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

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:

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.