Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • How to get last row in numeric data in Excel
  • Vlookup Examples in Excel
  • How to use Excel OFFSET function
  • Convert text string to valid reference in Excel using Indirect function
  • How to use Excel MMULT Function

Data Analysis Examples

  • How to Create Thermometer Chart in Excel
  • How To Filter Data in Excel
  • Number and Text Filters Examples in Excel
  • Calculate Conditional Percentile ‘IF’ in table in Excel
  • How To Remove Duplicates In Excel Column Or Row?

Data Validation Examples

  • Excel Data validation must contain specific text
  • Excel Data validation with conditional list
  • Excel Data validation date in next 30 days
  • Excel Data validation allow uppercase only
  • Excel Data validation don’t exceed total

Highlight duplicate columns in Excel

by

This tutorial shows how to  Highlight duplicate columns in Excel using the example below;

Formula

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

Explanation

Excel contains a built-in preset for highlighting duplicate values with conditional formatting, but it only works at the cell level. If you want to find and highlight duplicate columns, you’ll need to use your own formula, as explained below.

Worked Example:   Count if row meets multiple internal criteria in Excel

To highlight duplicate columns, you can use a formula based on the SUMPRODUCT function.  In the example shown, the formula used to highlight duplicate columns is:

=SUMPRODUCT(($B$4:$E$4=B$4)*($B$5:$E$5=B$5)*($B$6:$E$6=B$6))>1

How this formula works

This approach uses SUMPRODUCT to count the occurrence of every value in the table, one row at a time. Only when the same value appears in the same location in all three rows is a count generated. For cell B4, the formula is solved like this:

=SUMPRODUCT(($B$4:$E$4=B$4)*($B$5:$E$5=B$5)*($B$6:$E$6=B$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

Note that row references are fully absolute, while cell references are mixed, with only the row locked.

Worked Example:   Identify Duplicate Values vs Remove Duplicates in Excel

With a helper row

If you don’t mind adding a helper row to your data, you can simplify the conditional formatting formula quite a bit. In a helper row, concatenate all values in the column. Then you can use COUNTIF on that one row to count values that appear more than once, and use the result to trigger conditional formatting in the entire column.

Worked Example:   Get location of value in 2D array in Excel

Post navigation

Previous Post:

Highlight duplicate rows in Excel

Next Post:

Highlight dates that are weekends in Excel

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others
  • How to check cell contains which things in Excel
  • Remove text by position in a cell in Excel
  • RIGHT, RIGHTB functions: Description, Usage, Syntax, Examples and Explanation
  • LEFT, LEFTB functions: Description, Usage, Syntax, Examples and Explanation
  • UNICHAR function: Description, Usage, Syntax, Examples and Explanation
  • Calculate total hours that fall between two times in Excel
  • Basic timesheet formula with breaks in Excel
  • Add business days to date in Excel
  • Add years to date in Excel
  • DAYS function: Description, Usage, Syntax, Examples and Explanation
  • RECEIVED function: Description, Usage, Syntax, Examples and Explanation
  • Calculate loan interest in given year in Excel
  • How to set check register balance in Excel
  • RATE function: Description, Usage, Syntax, Examples and Explanation
  • Tax rate calculation with fixed base in Excel
© 2022 xlsoffice . All Right Reserved. | Teal Smiles