How to Highlight Distinct Values in Google Sheets
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.
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:
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 knowhow the UNIQUE formula works in Google Sheets, let’s look at how you’re able to 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:
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’re able to 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 Sheetsis 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:
The entire formula we entered into the custom formula box for the example above is:
it’s possible to 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:
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 anexactmatch 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.
it’s possible to 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 theTRIM function in Google Sheetsis:
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:
To usethe COUNTIF functionto highlight distinct values in Google Sheets, youmustuse 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 could.
Want to highlight cells with duplicate data in your spreadsheets? Here’s how you can do it with conditional formatting in Google Sheets.
I gripped my chair the entire time—and then kept thinking about it when the screen turned off.
These are the best free movies I found on Tubi, but there are heaps more for you to search through.
Some subscriptions are worth the recurring cost, but not these ones.
Lose your laptop without this feature, and you’ll wish you had turned it on.
Freeing up vital memory on Windows only takes a moment, and your computer will feel much faster once you’re done.
