Highlighting Distinct and Unique Values in Google Sheets

We can use custom formula rules to highlight (conditionally format) unique and distinct values in a column in Google Sheets.

Unique values are those that appear only once in a column, while distinct values are obtained after eliminating duplicates.

This terminology may be confusing because we typically refer to the output of the UNIQUE function as unique values. However, the function is capable of returning both unique and distinct values.

Syntax: UNIQUE(range, [by_column], [exactly_once])

The confusion arises from the fact that when you apply UNIQUE to a column with default settings (‘exactly_once’ set to FALSE), it returns all distinct values. However, we generally call the UNIQUE function output to unique values.

Therefore, in tutorials that do not explicitly mention the term “distinct,” we generally refer to the output of the UNIQUE function as unique values.

How to Highlight Distinct Values in Google Sheets

We will use the COUNTIF function to highlight distinct values in a column in Google Sheets.

Assuming we have data in column A starting from cell A2 up to A10 (cell range A2:A10), to highlight distinct values, use the following custom formula rule in conditional formatting:

=COUNTIF($A$2:A2,A2)=1
Highlighting distinct values in Google Sheets

As evident, the formula only highlights the initial occurrence of values within the range.

Steps to Apply the Rule:

  1. To apply, select A2:A10 or from A2 up to the row you want the rule to be applied.
  2. Go to Format > Conditional formatting.
  3. Under “Format rules,” select “Custom formula is.”
  4. Copy and paste the above formula in the field below.
  5. Click Done.

Formula Explanation

The formula (highlight rule) we used in the conditional formatting to highlight distinct values is essentially a COUNTIF function.

Syntax: COUNTIF(range, criterion)

The range to count is $A$2:A2, which means from cell $A$2 to the current row (A2), and the criterion is A2, referring to the value in the current row.

So, in each row, the formula returns the count of the values in that row from the start of the range. If it is equal to 1, the formula highlights the cell.

This is an example of the effective use of absolute and relative cell references in highlighting.

How to Highlight Unique Values in Google Sheets

We can use the UNIQUE function with the parameter exactly_once set to TRUE to extract unique values in a list.

In our sample data, we can use the following formula to extract unique values:

=UNIQUE($A$2:$A$10, ,TRUE)

Note: The range used in UNIQUE is $A$2:$A$10. If you refer to the entire column such as $A$2:$A, use the following formula: =TOCOL(UNIQUE($A$2:$A, ,TRUE), 1). The TOCOL wrapper removes a blank cell from the bottom of the result caused by open-range usage.

Once we have unique values, we need to match the values in the highlight range with this result. For that, we can use the XMATCH function.

Syntax: XMATCH(search_key, lookup_range, [match_mode], [search_mode])

Here is the custom formula rule to highlight truly unique values in our sample data:

=XMATCH(A2, TOCOL(UNIQUE($A$2:$A, ,TRUE), 1))
Highlighting truly unique values in Google Sheets

The rule matches the current row value in the list that contains truly unique values and returns its position in the list. If not found, it returns #N/A. Google Sheets highlights the cell wherever a match occurs.

You can follow the same steps outlined for highlighting distinct values to apply this highlighting rule as well.

Resources

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...

How to Easily Repeat a Sequence of Numbers in Excel

Excel offers multiple ways to accomplish tasks, and the simplicity of each approach depends...

Create a Sequence of Dates at Every Nth Row in Excel (Dynamic Array)

Would you like to create a sequence of dates in every nth row in...

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.