There are many instances where you may want to highlight duplicates in a spreadsheet, and you may already know how to do so. Yet, sometimes, you may also have to highlight distinct values in Google Sheets (unique values). This guide will show two ways to identify distinct values in Google Sheets. Read on to learn more.

What Are Distinct Values?

Distinct values are data that stand out in a data set in a spreadsheet as being the only instance containing that value. For example, if you measured the height of five people, two of them were 180cm, two were 165cm, and the last person was 155cm, the distinct value would be 155cm.

How to Highlight Distinct Values in Google Sheets

There are two ways you can highlight distinct values in your spreadsheet. The first is easy to do and quickly identifies unique values in a new cell range. The second method requires more work, but is perfect for highlighting distinct values across entire spreadsheets.

1. The UNIQUE Function

One way to extract the distinct values in a dataset is by using the UNIQUE function. Before we look at how to use the formula in your spreadsheet, let's take a look at the formula. Here's the syntax for the UNIQUE formula in Google Sheets: 

        =UNIQUE(cellrange)

The formula only requires one parameter. It defines the values or addresses for the cells containing the values. When executed, the formula will return all the distinct values from the dataset and display them in a different cell range.

Now that we know how the UNIQUE formula works in Google Sheets, let's look at how you can use it in your spreadsheet. For this example, we have a column containing 13 values. We are going to apply the formula to extract the distinct values. Here are the steps you need to follow to do so:

The UNIQUE Function to find distinct values
  1. Click the cell and write the starting part of the formula, which is =UNIQUE(.
  2. Now, we need to write the parameter defining the cell range. For this example, it is the range A2:A14.
  3. Add a bracket.
  4. Press Enter or Return.

This formula can be a great option, especially if you have a long list of similar data, such as names, ID numbers, or addresses. You can also nest other functions into UNIQUE to get additional functionality, such as sorting or numbering the extracted data.

2. Conditional Formatting

Conditional formatting in Google Sheets is a great way to highlight cells that fulfill a specified condition. These conditions are rules based on matching text or numerical values. This can be better for users who only wish to highlight the distinct text and not create new cells. This can take a bit more effort to do in your spreadsheet, but the results will more than make up for the effort you're going to put in.

For this demonstration, we will use the same dataset we used for the previous example. Here are the steps you need to do to use conditional formatting to highlight distinct values in your spreadsheet:

Using conditional formatting to identify distinct values
  1. Click and drag to highlight the cells where you wish to perform conditional formatting. The highlight can be seen by the thick blue border around the cells.
  2. Click on the Format button in the main top bar and click on Conditional formatting.
  3. A sidebar will open on the right side of the screen. The selected cell range should already be added to the Apply to range text box. If not, enter the cell address there.
  4. We will use a formula as a formatting rule, so click on the Format cells if option and select the Custom formula option.
  5. Choosing the option will display a new text box. We are going to add the COUNTIF formula there. To do this, enter the first part of the formula, which is =COUNTIF(.
  6. For the first parameter, we will write the range with absolute values, which is $A$2:$A$14.
  7. Put a comma to separate the parameters and write the second parameter, A2, in this case. (Use the starting cell of the column, row, or table if you’re doing it in your own spreadsheet.)
  8. Put a bracket to finish the COUNTIF formula.
  9. We want to highlight the values that occur less than two times, so in this case, we will add a <2 after the COUNTIF formula.
  10. Optionally, you can change the visual elements of the conditionally formatted data in the Formatting style section.
  11. Click on the green Done button to finalize the rule.

The entire formula we entered into the custom formula box for the example above is: 

        =COUNTIF($A$2:$A$14,A2)<2

You can simply copy-paste it into your spreadsheet and change the values inside the brackets to match the range you want to highlight.

Things to Know When Highlighting Distinct Values

Here are a few things to keep in mind when highlighting distinct values in your spreadsheet:

  1. Ensure that there are no missing spaces in your searches. Highlight every cell you want to cover, and try to avoid cells with empty data where possible.
  2. Ensure that other conditional formatting rules don't interfere with the new one. If they are, make changes or delete the old rule; otherwise, Sheets might give you false results.

Potential Problems When Highlighting Distinct Values in Google Sheets

Even if you perform the steps correctly, Google Sheets may often fail to highlight the data properly. Here are some reasons why Google Sheets may not properly highlight the distinct values.

Extra Space in Cells

When using the methods mentioned above, Google Sheets will look for an exact match of the values. A partial match will still mean that the values are considered two separate entities. Since Google Sheets will be looking for an exact match, if there is extra space (or typos) in the cells, it could lead to an unsuitable value which means that the value will be highlighted when it shouldn’t be.

You can use the TRIM and the CLEAN formulas to eliminate this problem. These will help you get rid of the extra unneeded spaces and characters. The formula for the TRIM function in Google Sheets is: 

        =TRIM(text)

The text parameter defines the string or the reference of the string you wish to clean.

Incorrect References

When a formula or function isn't working correctly, the culprit can be an incorrect reference. Before we look at the problem, let's discuss the types of cell references in Google Sheets. These are:

  1. Relative reference: This reference will change the locations of the cell relative to the formula. For example, A2.
  2. Absolute reference: In this reference, the cell addresses are kept the same no matter where the past value's location is. For example, $A$2.
  3. Mixed reference: This is a combination of both of the above references. For example, $A2 or A$2.

To use the COUNTIF function to highlight distinct values in Google Sheets, you must use an absolute reference for the first argument and a relative reference for the second.

Learning More About Highlighting Data

Hopefully, this guide taught you everything you need to know about identifying distinct values in Google Sheets. But, there’s so much more to learn about finding the right data in Google Sheets, don’t shy away from learning all you can.