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

Data Analysis

  • Conditional Formatting Icon Sets Examples in Excel
  • How to Create Column Chart in Excel
  • How to count table columns in Excel
  • How to create Gauge Chart in Excel
  • How to Create Gantt Chart in Excel

References

  • Perform case-sensitive Lookup in Excel
  • How to get first row number in range in Excel
  • Count rows that contain specific values in Excel
  • How to retrieve first match between two ranges in Excel
  • Last row number in range

Data Validations

  • Excel Data validation no punctuation
  • Excel Data validation must begin with
  • Excel Data validation must contain specific text
  • Excel Data validation only dates between
  • Excel Data validation number multiple 100

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.

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.

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.

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:

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

  • SWITCH function example in Excel
  • OR function Examples in Excel
  • Invoice status with nested if in Excel
  • How to use Excel XOR Function
  • NOT function: Description, Usage, Syntax, Examples and Explanation

Date Time

  • Add years to date in Excel
  • Display Date is same month in Excel
  • Calculate number of hours between two times in Excel
  • Convert Excel time to decimal hours in Excel
  • Get days, hours, and minutes between dates in Excel

Grouping

  • Group arbitrary text values in Excel
  • Map inputs to arbitrary values in Excel
  • Group numbers at uneven intervals in Excel
  • How to randomly assign data to groups in Excel
  • If cell contains one of many things in Excel

General

  • How to calculate percentage discount in Excel
  • Basic numeric sort formula in Excel
  • How to generate random number between two numbers in Excel
  • How to calculate percentage of total in Excel
  • AutoRecover file that was never saved in Excel
© 2025 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning