There are many instances where you may want to highlight duplicates in a spreadsheet, and you may already know how to do this. However, sometimes, you may need to highlight individual values in Google Sheets (unique values). This guide will show you two ways to identify distinct values in Google Sheets. For more information, read.
What are specific values?
Unique values are data that stand out in a spreadsheet as the only instance containing that value in the data set. For example, if you measured the height of five people, two of them were 180 cm tall, two were 165 cm tall, and the last person was 155 cm tall, then the typical value would be 155 cm.
How to highlight specific values in Google Sheets
You can highlight distinct values in your spreadsheet in two ways. 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 individual values throughout the spreadsheet.
1. Unique Functions
One way to extract unique values in a dataset is to use the UNIQUE function. Before we look at how to use a formula in your spreadsheet, let’s take a look at the formula itself. Here is the syntax of the UNIQUE formula in Google Sheets.
The formula requires only one parameter. It defines values or addresses for cells containing values. When executed, the formula will return all distinct values from the dataset and display them in a separate cell range.
Now that we know how the UNIQUE formula works in Google Sheets, let’s take a look at how you can use it in your own spreadsheet. For this example, we have a column with 13 values. We are going to apply the formula to find the specific value. To do this you have to follow the steps given here.
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 inside UNIQUE to get additional functionality, such as sorting or serializing the extracted data.
2. Conditional Formatting
Conditional Formatting in Google Sheets is a great way to highlight cells that meet a specified condition. These conditions are rules based on matching text or numeric values. This may be better for users who just want to highlight specific text and don’t want to create new cells. It may take a little more effort to do this in your spreadsheet, but the results will more than compensate for the effort you put in.
For this demonstration, we’ll use the same dataset we used for the previous example. To use conditional formatting to highlight individual values in your spreadsheet, follow these steps.
You can simply copy-paste this into your spreadsheet and change the values inside the brackets to match the range you want to highlight.
Things to know when highlighting specific values
Make sure you don’t have any spaces in your searches. Highlight each cell you want to cover, and try to avoid cells with empty data where possible.
Make sure that other conditional formatting rules do not interfere with the new one. if they are, alter or remove the old Testament; Otherwise, Leaflet may give you incorrect results.
Potential issues when highlighting distinct values in Google Sheets
Even if you perform the steps correctly, Google Sheets can often fail to highlight the data properly. Here are some reasons why Google Sheets may not be highlighting distinct values properly.
extra space in cells
When using the methods mentioned above, Google Sheets will look for an exact match of the values. A partial match would still mean that the values are treated as two different entities. Since Google Sheets will be looking for an exact match, if there are extra spaces (or typos) in the cells, it can lead to an inappropriate value, meaning the value will be highlighted when it shouldn’t be.
Learn more about data highlighting
Hopefully, this guide taught you everything you needed to know about identifying distinct values in Google Sheets. But, there’s a lot more to learn about finding the right data in Google Sheets, so don’t shy away from learning as much as you can.
