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

Data Analysis

  • Understanding Pivot Tables in Excel
  • How to do a t-Test in Excel?
  • Chart Axes in Excel
  • Calculate Conditional Percentile ‘IF’ in table in Excel
  • How to Sort by Color in Excel

References

  • Extract data with helper column in Excel
  • How to use Excel ROWS Function
  • How to use Excel LOOKUP Function
  • Perform case-sensitive Lookup in Excel
  • VLOOKUP function: Description, Usage, Syntax, Examples and Explanation

Data Validations

  • Data validation must not exist in list
  • Excel Data validation require unique number
  • Excel Data validation specific characters only
  • Excel Data validation must contain specific text
  • Excel Data validation only dates between

Highlight unique values in Excel

by

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

Formula

=COUNTIF(data,A1)=1

Explanation

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

If you want to highlight cells that contain unique values in a set of data, you can use a formula that returns TRUE when a value appears just once .

For example, if you have values in the cells A1:D10, and want to highlight cells with duplicate values, you can use this formula:

=COUNTIF($A$1:$D$10,A1)=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 A1 in this case.

How this formula works

COUNTIF simply counts the number of times each value appears in the data range. By definition, each value must appear at least once, so when the count equals 1, the value is unique. When the count is 1, the formula returns TRUE and triggers the rule.

Conditional formatting is evaluated for each cell that is applied to. 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 A1 is fully relative. So, the rule is evaluated for each of the 40 cells in A1:D10, and A1 will be updated to a new address 40 times (once per cell) while $A$1:$D$10 remains unchanged.

Named ranges for a cleaner syntax

Another way to lock references is is to use named ranges, since named ranges are automatically absolute. For example, if you name the range A1:D10 “data”, you can rewrite the rule with a cleaner syntax like so:

=COUNTIF(data,A1)=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

  • OR function Examples in Excel
  • XOR function: Description, Usage, Syntax, Examples and Explanation
  • How to use Excel FALSE Function
  • Not Equal To ‘<>‘ operator in Excel
  • Complete List of Excel Logical Functions, References and Examples

Date Time

  • Steps to create Dynamic calendar grid in Excel
  • Display the current date and time in Excel
  • Convert time to time zone in Excel
  • EDATE function: Description, Usage, Syntax, Examples and Explanation
  • Get days, months, and years between dates in Excel

Grouping

  • Group numbers with VLOOKUP in Excel
  • Group numbers at uneven intervals in Excel
  • Running count group by n size in Excel
  • Group times into unequal buckets in Excel
  • Group times into 3 hour buckets in Excel

General

  • How to Insert Cells, Row and Rows in Excel
  • How to get random value from list or table in Excel
  • Cell References: Relative, Absolute and Mixed Referencing Examples
  • Excel Default Templates
  • 231 Keyboard Shortcut Keys In Excel
© 2025 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning