How to Highlight Distinct Values in Google Sheets

There are many instances where you’ll want to highlight duplicates in a spreadsheet, and you may already know how to do that. However, sometimes you also need to highlight different values ​​in Google Sheets (unique values). This guide shows two ways to identify distinct values ​​in Google Sheets. Read on to find out more.

What are standalone values?

Unique values ​​are data that stand out in a record in a table as the only instance with that value. For example, if you measured the height of five people, two of whom were 180 cm, two were 165 cm and the last person was 155 cm, the clear value would be 155 cm.

How to mark unique values ​​in Google Sheets

There are two ways you can highlight different values ​​in your spreadsheet. The first is easy to perform and quickly identifies unique values ​​in a new range of cells. The second method requires more work, but is perfect for highlighting different values ​​throughout spreadsheets.

1. The UNIQUE function

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

=UNIQUE(cellrange)

The formula only needs one parameter. It defines the values ​​or addresses for the cells that contain the values. When run, the formula returns all distinct values ​​from the data set and displays them in a different range of cells.

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 with 13 values. We will apply the formula to extract the different values. Here are the steps you need to follow to do this:

1. Click on the cell and write the initial part of the formula, ie = UNIQUE (.
2. Now we need to write the parameter that defines the cell range. In this example it is the area A2:A14.
3. Add a parenthesis.
4. Press Enter or To return.

This formula can be a great option, especially if you have a long list of similar data like names, ID numbers, or addresses. You can also nest other functions inside UNIQUE to get additional functionality, e.g. B. Sorting or numbering of the extracted data.

2. Conditional formatting

Conditional formatting in Google Sheets is a great way to highlight cells that meet a specific condition. These conditions are rules based on matching text or number values. This may be better for users who just want to highlight the unique text and don’t want to create new cells. This may require a bit more effort in your spreadsheet, but the results will more than make up for the effort you will put in.

For this demonstration, we’ll use the same dataset as the previous example. Here are the steps you need to follow to use conditional formatting to highlight different values ​​in your spreadsheet:

1. Click and drag to highlight the cells where you want to apply conditional formatting. The highlighting can be recognized by the thick blue borders around the cells.
2. Click on that format button in the top main bar and click Conditional formatting.
3. A sidebar will open on the right side of the screen. The selected range of cells should already have been added to apply area text box. If not, enter the cell address there.
4. We’re going to use a formula as a formatting rule, so click that Format cells if option and select it Custom formula Possibility.
5. When you select the option, a new text box will appear. We will add the COUNTIF formula there. To do this, enter the first part of the formula, viz =COUNTIF(.
6. For the first parameter we write the range with absolute values, i.e $2: $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 doing this in your own table.)
8. Use parentheses to end the COUNTIF formula.
9. We want to highlight the values ​​that appear less than twice, so in this case we add a <2 according to 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 Finished button to complete the rule.

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

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

You can just copy and paste it into your spreadsheet and change the values ​​in the brackets to match the area you want to highlight.

Things to know about highlighting unique values

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

1. Make sure your searches aren’t missing spaces. Mark each cell you want to cover and try to avoid cells with blank data if possible.
2. Make sure other conditional formatting rules don’t interfere with the new one. If so, make changes or delete the old rule. otherwise, Sheets may return incorrect results.

Potential issues with highlighting unique values ​​in Google Sheets

Even if you follow the steps correctly, Google Sheets often fails to highlight the dates correctly. Here are some reasons why Google Sheets might not properly highlight the different values.

When using the above methods, Google Sheets will look for one I agree match of values. A partial match still means that the values ​​are treated as two separate entities. Since Google Sheets is looking for an exact match, extra spaces (or typos) in the cells could result in an inappropriate value, meaning the value will be highlighted when it shouldn’t.

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

=TRIM(text)

The text parameter defines the string or reference of the string that you want to sanitize.

Incorrect references

When a formula or function doesn’t work properly, the cause may 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 changes the positions of the cell relative to the formula. For example, A2.
2. Absolute reference: In this reference, the cell addresses are kept the same regardless of where the previous value is located. For example, $€2.
3. Mixed reference: This is a combination of the two references above. For example, $A2 or $2.

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

Learn more about data highlighting

Hopefully this guide has taught you everything you need to know about identifying distinct values ​​in Google Sheets. But there is so much more to learn about how to find the right data in Google Sheets, don’t be afraid to learn as much as you can.