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 rightabove), you can use Excel formulas to highlight duplicate rows in your spreadsheet.

To highlight duplicate rows in the example spreadsheet, first use the & operator to collate the data from columns A - C into column D. For example, the formula to be entered into cell D2 is:

=A2 & B2 & C2

Copying this formula down all rows gives the following spreadsheet:

Concatenate Formulas for Excel Duplicate Rows Example
Concatenate Results for Excel Duplicate Rows Example

Tidying up Column D (optional)

In the above results spreadsheet, the dates of birth are shown as numeric values at the end of the combined text string. This is because Excel stores dates as numeric values and it is these underlying values that are displayed in the combined text strings.

As we are simply using the combined text strings to identify duplicate rows, we are not concerned with the appearance of the data in column D and so, for the purpose of this example, we will continue to work with the simple concatenation shown above.

However, if you did want to tidy up the data in column D, you could do this by adding spaces between the fields and using the Text function to display the dates as recognisable dates. The formula in cell D2 would then become:

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

and the result of this formula would be:

Ann SMITH 29/01/1980

Absolute & Relative References in the Countif Function

The Countif function used in this example uses an absolute reference for the first reference to cell D$2 (shown by the $ sign), and relative references for all other cell references.

Therefore, when the formula is copied down the rows, 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. The formula therefore only highlights the duplicate values (i.e. this formula will return the value 1 for the first occurrence of "LauraCARTER30316", but will return the value 2 for the second occurrence of this text string, etc).

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 Countif function used in this example uses a combination of Absolute and Relative Cell References.

The spreadsheet below shows column E populated with the Countif function:

Countif 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 E7, 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