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

Data Analysis

  • Calculate Conditional Percentile ‘IF’ in table in Excel
  • Move chart to a separate worksheet in Excel
  • Everything about Charts in Excel
  • How To Remove Duplicates In Excel Column Or Row?
  • How To Perform and Interpret Regression Analysis in Excel

References

  • Convert text string to valid reference in Excel using Indirect function
  • How to get last row in numeric data in Excel
  • How to calculate two-way lookup VLOOKUP in Excel Table
  • Lookup entire row in Excel
  • How to use Excel VLOOKUP Function

Data Validations

  • Excel Data validation date in specific year
  • Excel Data validation allow uppercase only
  • Excel Data validation only dates between
  • Excel Data validation must contain specific text
  • Excel Data validation date in next 30 days

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:   How to count specific words in a range 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:   How to count total words in a range 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:   Highlight cells that contain in Excel

Post navigation

Previous Post:

VLOOKUP without #N/A error in Excel

Next Post:

How to Create Gantt Chart 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

Logical Functions

  • How to use Excel OR Function
  • OR function: Description, Usage, Syntax, Examples and Explanation
  • IF with boolean logic in Excel
  • How to use Excel XOR Function
  • IFNA function: Description, Usage, Syntax, Examples and Explanation

Date Time

  • How to calculate project start date based on end date in Excel
  • How to calculate nth day of year in Excel
  • Get fiscal year from date in Excel
  • Get age from birthday in Excel
  • Next biweekly payday from date in Excel

Grouping

  • Group arbitrary text values in Excel
  • Map text to numbers in Excel
  • If cell contains one of many things in Excel
  • Group times into 3 hour buckets in Excel
  • Group numbers with VLOOKUP in Excel

General

  • Check if range contains a value not in another range in Excel
  • Count cells that contain errors in Excel
  • Lock Cells in a Worksheet Excel
  • 231 Keyboard Shortcut Keys In Excel
  • Advanced Number Formats in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning