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

Data Analysis

  • How To Filter Data in Excel
  • Understanding Pivot Tables in Excel
  • Get column name from index in Excel Table
  • How To Remove Duplicates In Excel Column Or Row?
  • Create Scatter Chart in Excel

References

  • How to get last row in text data in Excel
  • How to use Excel ROWS Function
  • Count rows with at least n matching values
  • Extract all partial matches in Excel
  • How to use Excel COLUMN Function

Data Validations

  • Excel Data validation must not contain
  • Excel Data validation date in specific year
  • How To Create Drop-down List in Excel
  • Excel Data validation exists in list
  • Excel Data validation with conditional list

Highlight duplicate values in Excel

by

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

Formula

=COUNTIF(data,A1)>1

Explanation

Note: Excel contains many built-in “presets” for highlighting values with conditional formatting, including a preset to highlight duplicate values. However, if you want more flexibility, you can highlight duplicates with your own formula, as explained in this article.

If you want to highlight cells that contain duplicates in a set of data, you can use a simple formula that returns TRUE when a value appears more than once.

For example, if you want to highlight duplicates in the range B4:G11, you can use this formula:

=COUNTIF($B$4:$G$11,B4)>1

Note: with conditional formatting, it’s important that the formula be entered relative to the “active cell” in the selection, which is assumed to be B4 in this case.

How this formula works

COUNTIF simply counts the number of times each value appears in the range. When the count is more than 1, the formula returns TRUE and triggers the rule.

When you use a formula to apply conditional formatting, the formula is evaluated relative to the active cell in the selection at the time the rule is created. In this case, the range we are using in COUNTIF is locked with an absolute address, but B4 is fully relative. So, the rule is evaluated for each cell in the range, with B4 changing and $B$4:$G$11 remaining unchanged.

A variable number of duplicates + named ranges

Instead of hard-coding the number 1 into the formula you can reference a cell to make the number of duplicates variable.

You can extend this idea and make the formula easier to read by using named ranges. For example, if you name G2 “dups”, and the range B4:G11 “data”, you can rewrite the formula like so:

=COUNTIF(data,B4)>=dups

You can then change the value in G2 to anything you like and the conditional formatting rule will respond instantly, highlighting cell that contain values greater than or equal to the number you put in the named range “dups”.

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

  • AND function: Description, Usage, Syntax, Examples and Explanation
  • Not Equal To ‘<>‘ operator in Excel
  • How to use Excel AND Function
  • How to use Excel OR Function
  • How to use Excel FALSE Function

Date Time

  • Calculate years between dates in Excel
  • How to calculate next scheduled event in Excel
  • Basic Overtime Calculation Formula in Excel
  • Add workdays to date custom weekends in Excel
  • Two ways to sum time over 30 minutes in Excel

Grouping

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

General

  • Basic text sort formula in Excel
  • Currency vs Accounting Format in Excel
  • Count cells that contain errors in Excel
  • How to get original price from percentage discount in Excel
  • Creating and Opening an existing file in Excel
© 2025 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning