Home » Excel-Formulas » Excel-Duplicates

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 Excel TEXT function. In cell D2, the formula will be:

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:

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 used in the function are relative cell references. Therefore, when the formula is copied down to the following 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 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.