**How to Highlight Duplicate Columns in Excel using a Formula**

Sometimes you get stuck in a spreadsheet that has hundreds of columns and you want to highlight duplicate cells or columns. To highlight duplicate cells, Excel has the built-in conditional formatting function, but to highlight duplicate columns you need to create a formula. In this article, we are going to show you an easy way to find and highlight duplicate columns.

**Basic Formula**

`=SUMPRODUCT ((row1=ref1) * (row2=ref2) * (row3=ref3)) >1`

**Explanation of the Formula**

The SUMPRODUCT function can be used with conditional formatting to highlight duplicate columns. The values of the duplicate column will return as TRUE, otherwise FALSE if those columns are not duplicates.

**Example**

To check the formula, see the below example. We will apply the same formula twice-once in a conditional formatting and after that in a cell to check it the formula is true.

**The formula in Conditional Formatting **

Select all columns where you want to check for duplicates and apply the formula with the new rule conditional formatting as shown in figure 1. The formula applied in the New Rule formatting is:

`=SUMPRODUCT(($C$4:$F$4=C$4) * ($C$5:$F$5=C$5)*($C$6:$F$6=C$6))>1`

*Figure 1. Example of Highlight Duplicate Columns Formula in Conditional Formatting *

In the figure attached, we have applied the formula to highlight duplicate cells with conditional formatting. So, it checks all the values and highlights the duplicate columns.

**Use Formula in a Cell **

Now we will apply the same formula in a cell to check if it returns TRUE. The formula applied in C9 is:

`=SUMPRODUCT(($C$4:$F$4=C$4) * ($C$5:$F$5=C$5)*($C$6:$F$6=C$6))>1`

Figure 2 shows that duplicate columns Return TRUE after applying the above formula in C9.

*Figure 2. Example of Highlight Duplicate Columns Using Formula*

**How This Formula Actually Works**

SUMPRODUCT is used to check the existence of every single value in the table in this approach. SO, it works when a similar value is located in the same location in all the selected rows. The formula in C4 will be solved like this:

`=SUMPRODUCT (($C$4:$F$4=C$4)* ($C$5:$F$5=C$5) * ($C$6:$F$6=C$6))>1`

`=SUMPRODUCT(({1,1,1,1})*({1,0,1,0})*({1,0,1,0}))>1`

`=SUMPRODUCT ({1,0,1,0})>1`

`=2>1`

`=TRUE`

*IMPORTANT NOTE*

*IMPORTANT NOTE*

The row references in this spreadsheet are absolute, however, the cell references are mixed up along with a locked row.

## Leave a Comment