Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • Two-way lookup with VLOOKUP in Excel
  • How to use Excel INDIRECT Function
  • Basic INDEX MATCH approximate in Excel
  • Excel Advanced Lookup using Index and Match Functions
  • How to reference named range different sheet in Excel

Data Analysis Examples

  • Add Outline to Data in Excel
  • Example of COUNTIFS with variable table column in Excel
  • How to Use Solver Tool in Excel
  • What-If Analysis: Scenarios and Goal Seek in Excel
  • Conditional Formatting Color Scales Examples in Excel

Data Validation Examples

  • Excel Data validation must begin with
  • Excel Data validation number multiple 100
  • Excel Data validation must not contain
  • Excel Data validation allow uppercase only
  • Excel Data validation only dates between

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.

Worked Example:   Basic text sort formula in Excel

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.

Worked Example:   If cell begins with x, y, or z in Excel

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.

Worked Example:   Highlight entire rows in Excel

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:

Highlight integers only in Excel

Next Post:

Highlight duplicate rows 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
  • ASC function: Description, Usage, Syntax, Examples and Explanation
  • How to get page from URL in Excel
  • How to check cell contains which things in Excel
  • Complete List of Excel Text Functions References and Examples
  • Extract word that begins with specific character in Excel
  • Pad week numbers with zeros in Excel
  • Calculate date overlap in days in Excel
  • DATE function: Description, Usage, Syntax, Examples and Explanation
  • Roll back weekday to Friday base on a particular date in Excel
  • Convert Unix time stamp to Excel date
  • Calculate periods for annuity in Excel
  • COUPNCD function: Description, Usage, Syntax, Examples and Explanation
  • Tax rate calculation with fixed base in Excel
  • Calculate interest for given period in Excel
  • SLN function: Description, Usage, Syntax, Examples and Explanation
© 2022 xlsoffice . All Right Reserved. | Teal Smiles