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:

=SUMPRODUCT(--(\$C3&"-"&\$D3=\$C3:\$C\$3&"-"&\$D3:\$D\$3))>1

### 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.

\$C3&"-"&\$D3

becomes

40184&"-"&1150

and returns

"40184-1150"

#### 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.

\$C3:\$C\$3&"-"&\$D3:\$D\$3

becomes

40184&"-"&1150

and returns

"40184-1150".

#### Step 3 - Compare concatenated values

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

\$C3&"-"&\$D3=\$C3:\$C\$3&"-"&\$D3:\$D\$3

becomes

"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).

--(\$C3&"-"&\$D3=\$C3:\$C\$3&"-"&\$D3:\$D\$3)

becomes

--(TRUE)

and returns 1.

#### Step 5 - Sum values

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

SUMPRODUCT(--(\$C3&"-"&\$D3=\$C3:\$C\$3&"-"&\$D3:\$D\$3))

becomes

SUMPRODUCT(1)

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.

SUMPRODUCT(--(\$C3&"-"&\$D3=\$C3:\$C\$3&"-"&\$D3:\$D\$3))>1

becomes

1>1

and returns FALSE. Cell C3 is not highlighted.

Highlight-duplicates-within-same-date-week-month-year.xlsx

### Highlight duplicates on same week

Conditional formatting formula:

=SUMPRODUCT(--(\$B16&"-"&YEAR(\$C16)&"-"&\$D16=\$B16:\$B\$16&"-"&YEAR(\$C16:\$C\$16)&"-"&\$D16:\$D\$16))>1

### Highlight duplicates on same month

Conditional formatting formula:

=SUMPRODUCT(--(YEAR(\$C29)&"-"&MONTH(\$C29)&"-"&\$D29=YEAR(\$C29:\$C\$29)&"-"&MONTH(\$C29:\$C\$29)&"-"&\$D29:\$D\$29))>1

Highlight-duplicates-within-same-date-week-month-year.xls
(Excel 97-2003 Workbook *.xls)