Use Excel Formulas to Highlight Duplicate Rows

Data for Excel Duplicate Rows Example

If you have a spreadsheet containing several fields of data (as in the example on the right), you can highlight duplicate rows in your spreadsheet by using Excel Formulas.

To highlight Excel duplicates in the example spreadsheet on the right, first collate the data from columns A - C into column D. This can be done using a formula that is made up of the & operator and the Text function. In cell D2, the formula will be:

=A2 & " " & B2 & " " & TEXT(C2,"dd/mm/yyyy")

Once columns A - C are collated into column D, we need to highlight duplicates in the contents of column D. This can be done using the Countif function. The function to be entered into cell E2 is:

=COUNTIF(D$2:D2, D2)

Note that the above function uses a combination of Absolute and Relative Cell References. The first reference, to cell D2, is an absolute cell reference (shown by the $ sign), while the remaining cell references are relative cell references.

Therefore, when the formula is copied down to the rows below, the initial reference to cell D$2 remains fixed, while the remaining references are adjusted to refer to cells D3, D4, etc. This causes the Countif function to only count cells up to and including those in the current row and therefore causes the formula to only highlight duplicate values in column E.

The functions are shown in cells D2-E11 of the spreadsheet below:

Formulas for Excel Duplicate Rows Example

The results of the formulas are shown in the spreadsheet below. It is seen that the duplicate entry in row 10 has the value "2" in cell E10, showing that this is a duplicate.

As an additional feature, in the spreadsheet below, Conditional Formatting has been used to highlight rows in which the value in column E is greater than 1.

Result of Excel Duplicate Rows Example
Return to the Excel Formulas Page

Return to the Home Page

Valid XHTML 1.0 Transitional Valid CSS!
Disclaimer   Privacy Policy
Copyright © 2008-2013