Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • Error Bars in Excel
  • Conditional Formatting Icon Sets Examples in Excel
  • How to Create One and Two Variable Data Tables in Excel
  • Everything about Charts in Excel
  • What-If Analysis: Scenarios and Goal Seek in Excel

References

  • Find Closest Match in Excel Using INDEX, MATCH, ABS and MIN functions
  • Two-column Lookup in Excel
  • Extract data with helper column in Excel
  • Perform case-sensitive Lookup in Excel
  • How to use Excel MATCH Function

Data Validations

  • Excel Data validation exists in list
  • Excel Data validation no punctuation
  • Excel Data validation must begin with
  • Excel Data validation don’t exceed total
  • Prevent invalid data entering in specific cells

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.

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.

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.

Post navigation

Previous Post:

COSH function: Description, Usage, Syntax, Examples and Explanation

Next Post:

Excel Data validation require unique number

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

Logical Functions

  • IF with wildcards in Excel
  • How to use Excel NOT Function
  • Extract multiple matches into separate rows in Excel
  • TRUE function: Description, Usage, Syntax, Examples and Explanation
  • Complete List of Excel Logical Functions, References and Examples

Date Time

  • Add workdays to date custom weekends in Excel
  • Add business days to date in Excel
  • Two ways to sum time over 30 minutes in Excel
  • Get first day of previous month in Excel
  • NETWORKDAYS function: Description, Usage, Syntax, Examples and Explanation

Grouping

  • Group times into 3 hour buckets in Excel
  • How to randomly assign data to groups in Excel
  • Categorize text with keywords in Excel
  • Map text to numbers in Excel
  • Running count group by n size in Excel

General

  • List worksheet index numbers in Excel
  • List sheet names with formula in Excel
  • How to fill cell ranges with random text values in Excel
  • Count cells that do not contain errors in Excel
  • How to Delete Cells, Row and Rows in Excel
© 2025 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning