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

Data Analysis

  • Remove Duplicates Example in Excel
  • How To Perform and Interpret Regression Analysis in Excel
  • Everything about Charts in Excel
  • How to create dynamic reference table name in Excel
  • How To Insert and Customize Sparklines in Excel

References

  • Offset in Excel
  • INDEX function: Description, Usage, Syntax, Examples and Explanation
  • How to get relative row numbers in a range in Excel
  • How to get address of last cell in range in Excel
  • How to use Excel OFFSET function

Data Validations

  • Excel Data validation require unique number
  • Excel Data validation date in specific year
  • Excel Data validation no punctuation
  • Excel Data validation must not contain
  • Excel Data validation unique values only

Highlight duplicate rows in Excel

by

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

Formula

=COUNTIFS(A:A,$A1,B:B,$B1,C:C,$C1)

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 highlight entire rows that are duplicates you’ll need to use your own formula, as explained below.

If you want to highlight duplicate rows in an unsorted set of data, and you don’t want to add a helper column, you can use a formula that uses the COUNTIFS function to count duplicated values in each column of the data.

For example, if you have values in the cells B4:D11, and want to highlight entire duplicate rows, you can use rather ugly formula:

=COUNTIFS($B$4:$B$11,$B4,$C$4:$C$11,$C4,$D$4:$D$11,$D4)>1

Named ranges for a cleaner syntax

The reason the above formula is so ugly is that we need to fully lock each column range, then used a mixed reference to test each cell in each column. If you create named ranges for each column in the data: col_a, col_b, and col_c, the formula can be written with a much cleaner syntax:

=COUNTIFS(col_b,$B4,col_c,$C4,col_d,$D4)>1

How this formula works

In the formula, COUNTIFS counts the number of times each value in a cell appears in its “parent” column. By definition, each value must appear at least once, so when the count > 1, the value must be a duplicate. The references are carefully locked so the formula will return true only when all 3 cells in a row appear more than once in their respective columns.

Worked Example:   Summary count of non-blank categories in Excel

The helper column option “cheats” by combining all values in a row together in single cell using concatenation. Then COUNTIF simply counts the number of times this concatenated value appears in column D.

Worked Example:   How to retrieve first match between two ranges in Excel

Helper column + concatenation

If you don’t mind adding a helper column to your data, you can simplify the conditional formatting formula quite a bit. In a helper column, concatenate values from all columns. For example, add a formula in column E that looks like this:

=B4&C4&D4

Then use the following formula in the conditional formatting rule:

=COUNTIF($E$4:$E$11,$E4)>1

This is a much simpler rule, and you can hide the helper column if you like.

Worked Example:   Conditional formatting highlight target percentage in Excel

If you have a really large number of columns, you can use the TEXTJOIN function (Excel 2016 365) to perform concatenation using a range:

=TEXTJOIN(",",TRUE,A1:Z1)

You can then use COUNTIF as above.

SUMPRODUCT

If you’re using a version of Excel before 2007, you can use SUMPRODUCT like this:

=SUMPRODUCT((col_b=$B4)*(col_c=$C4)*(col_d=$D4))>1

Post navigation

Previous Post:

VLOOKUP without #N/A error 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

  • IF with wildcards in Excel
  • Complete List of Excel Logical Functions, References and Examples
  • FALSE function: Description, Usage, Syntax, Examples and Explanation
  • SWITCH function: Description, Usage, Syntax, Examples and Explanation
  • OR function Examples in Excel

Date Time

  • Custom weekday abbreviation in Excel
  • Get project midpoint in Excel
  • Convert Excel time to decimal hours in Excel
  • Convert Excel time to decimal minutes
  • Get first day of month in Excel

Grouping

  • Group arbitrary text values in Excel
  • How to randomly assign people to groups in Excel
  • Categorize text with keywords in Excel
  • Calculate conditional mode with criteria in Excel
  • Group times into unequal buckets in Excel

General

  • How to get Excel workbook path only
  • Flash Fill in Excel
  • Using Existing Templates in Excel
  • Automatically fill series of cells in Excel using AutoFill
  • Zoom Worksheet in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning