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.
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:
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:
- 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 Highlight Cells Rules option and from the secondary menu that appears, select the Duplicate Values... option.
-
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 to close the dialog box and apply conditional formatting to duplicate cells.
The resulting spreadsheet, with the duplicates highlighted, is shown below:
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:
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 ) 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.