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

Data Analysis

  • How to calculate current stock or inventory in Excel
  • How to create dynamic reference table name in Excel
  • Excel Line Chart
  • Conditional Formatting New Rule with Formulas in Excel
  • How to count table rows in Excel

References

  • Two-column Lookup in Excel
  • How to use Excel ROW Function
  • How to get last row in mixed data with blanks in Excel
  • How to get first column number in range in Excel
  • How to use Excel VLOOKUP Function

Data Validations

  • Excel Data validation with conditional list
  • Excel Data validation don’t exceed total
  • Excel Data validation only dates between
  • How To Create Drop-down List in Excel
  • Excel Data validation specific characters only

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 .

Worked Example:   Basic numeric sort formula in Excel

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.

Worked Example:   Conditional Formatting Color Scales Examples in Excel

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.

Worked Example:   Conditional Formatting Icon Sets Examples in Excel

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:

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

  • SWITCH function example in Excel
  • Excel If, Nested If, And/Or Criteria Examples
  • Check multiple cells are equal in Excel
  • How to use Excel OR Function
  • How to use Excel XOR Function

Date Time

  • How to calculate months between dates in Excel
  • Calculate series of dates by workdays in Excel
  • How to get workdays between dates in Excel
  • Convert decimal minutes to Excel time
  • Convert text timestamp into time in Excel

Grouping

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

General

  • Basic text sort formula in Excel
  • How to get original number from percent change in Excel
  • How to get original price from percentage discount in Excel
  • How to get amount with percentage in Excel
  • Hide and Unhide Columns or Rows in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning