Author: Oscar Cronquist Article last updated on December 21, 2018

The image above demonstrates a conditional formatting formula that highlights duplicate items based on date. The first instance is not highlighted, only subsequent duplicates are highlighted.

Conditional formatting formula:


Explaining Conditional formatting formula

Step 1 - Concatenate cell values

The cell references $C3 and $D3 are locked to each column, however, they change when CF move to the next row. The ampersand sign concatenates the values in these cells.




and returns


Step 2 - Concatenate values using expanding cell references

These cell references expand as the CF moves to cells below, the formula keeps track of previous values so it can detect duplicate values.




and returns


Step 3 - Compare concatenated values

The equal sign lets you compare values, the result is always TRUE or FALSE.



"40184-1150" = "40184-1150"

and returns TRUE.

Step 4 - Convert boolean values

The SUMPRODUCT function can't work with boolean values, we must convert it (them) to their numerical equivalents. TRUE = 1 and FALSE equals 0 (zero).




and returns 1.

Step 5 - Sum values

The SUMPRODUCT function sums the values in the array in this step.




and returns 1.

Step 6 - Check if duplicate

This steps checks if the sum is larger than 1, if it is then TRUE is returned and the cell is highlighted.




and returns FALSE. Cell C3 is not highlighted.

Get Excel *.xlsx file


Highlight duplicates on same week

Conditional formatting formula:


Highlight duplicates on same month

Conditional formatting formula:


Get excel sample file for this article.

(Excel 97-2003 Workbook *.xls)

Functions in this article:

SUMPRODUCT(array1, array2, )
Returns the sum of the products of the corresponding ranges or arrays

YEAR(serial_number) returns the year of a date, an integer of the range 1900-9999

MONTH(serial_number) returns the month, a number from 1 (January) to 12 (December)

WEEKNUM(serial_number, return_type)
Returns the week number in the year