ExcelFunctions.net

Search site:

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:

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.