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

Data Analysis

  • How to calculate correlation coefficient Correlation in Excel
  • How To Create Frequency Distribution in Excel
  • Calculate Conditional Percentile ‘IF’ in table in Excel
  • Conditional Formatting Data bars Examples in Excel
  • Example of COUNTIFS with variable table column in Excel

References

  • How to get relative column numbers in a range in Excel
  • How to get last column number in range in Excel
  • How to use Excel MATCH Function
  • LOOKUP function: Description, Usage, Syntax, Examples and Explanation
  • VLOOKUP function: Description, Usage, Syntax, Examples and Explanation

Data Validations

  • Excel Data validation whole percentage only
  • Excel Data validation allow weekday only
  • Excel Data validation must begin with
  • Excel Data validation allow uppercase only
  • 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:   How to check if cell contains all of many things 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:   Count sold and remaining 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:   Count dates by day of week 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

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

  • Not Equal To ‘<>‘ operator in Excel
  • Excel If, Nested If, And/Or Criteria Examples
  • IF, AND, OR and NOT Functions Examples in Excel
  • How to use Excel FALSE Function
  • SWITCH function example in Excel

Date Time

  • Convert date to month and year in Excel
  • Pad week numbers with zeros in Excel
  • How to calculate project start date based on end date in Excel
  • Add decimal minutes to time in Excel
  • How to calculate nth day of week in month in Excel

Grouping

  • Group arbitrary text values in Excel
  • Group times into 3 hour buckets in Excel
  • Group numbers at uneven intervals in Excel
  • Group numbers with VLOOKUP in Excel
  • Calculate conditional mode with criteria in Excel

General

  • How to calculate percentage discount in Excel
  • How to calculate percent change in Excel
  • With vs Without Array Formula in Excel
  • How to calculate profit margin percentage in Excel
  • Delete Blank Rows at Once in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning