< Go Back

How to Highlight Duplicate Columns in Excel using a Formula

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

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

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California
This blog is one way to figure it out. A 20 minute chat session with an expert is a better way.Get step-by-step guidance on your question

Leave a Comment

avatar