Jawadul Islam Chowdhury
When working with a large amount of data in Google Sheets, it is common to see the same data entered several times. Finding unique data manually, one by one is tedious. We can use functions or features of Google Sheets to find unique values easily. In this article, I will show you how to find unique values in Google Sheets.
5 Simple Ways to Find Unique Values in Google Sheets
Unique values represent data that appears only once in the dataset. We can use formulas such as UNIQUE, COUNTIF, ARRAYFORMULA, or other functions. Moreover, we can use Google Sheets features such as Remove duplicates and Conditional formatting to find unique values in Google Sheets.
Let’s assume you have a dataset of first names and last names. Now you need to find the unique values present in the dataset.
1. Using Remove Duplicates Feature
One of the easy ways to find unique values is to remove the duplicates. Using a Google Sheet feature named Remove duplicates we can easily remove the duplicate entries and list the unique values. This feature keeps the first appearance of the duplicate entries and deletes the subsequent appearances. Follow the below steps to find unique values using the Remove duplicates feature.
📌 Steps:
- First of all, select the whole dataset and then select options Data >> Data cleanup >> Remove duplicates.
- Then, a Remove duplicates pop-up box will appear. Select the following options.
- Now, a dialogue box like the below image will be shown. Click OK.
- Finally, the output will look like the following image.
Read More: How to Remove Unique Values in Google Sheets (2 Suitable Ways)
2. Applying UNIQUE Function
The UNIQUE function returns the values that are not duplicate. This function keeps the first appearance of the duplicate entries while removing the subsequent appearances. Furthermore, we can eliminate the appearance of duplicate entries entirely. Follow the below steps to find unique values using the UNIQUE function. Here are the steps to find unique values by applying the UNIQUE function.
📌 Steps:
- Initially, select cell B5 and type the formula below.
=UNIQUE(Dataset!B5:C15)
- After that, press Enter, and the output of unique values will be shown. The first appearance of the duplicate entries is kept here, and the subsequent appearances are deleted.
- Now, to find only the values that appeared once in the dataset, type the following formula and press Enter. This formula will not return any results for values with duplicate entries.
=UNIQUE(Dataset!B5:C15,"",TRUE)
Read More: How to Use Pivot Table to Count Unique Values in Google Sheets
3. Employing COUNTIF Function
You can count the appearance of the values using the COUNTIF function and then you can use the Filter option in Google Sheets to find the unique values of the dataset. Follow the below steps to find unique values employing the COUNTIF function.
📌 Steps:
- First of all, you need to create a helper column named Occurrence.
- After that, select cell D5 and enter the following formula to count the appearance of the data entries. Use the fill-handle tool to copy the formula to the whole column.
=COUNTIF($B$5:$B5,$B5)
- After that, select the Occurrence column and from the menu bar select options Data >> Create a filter.
- Then, click the filter icon in the Occurrence cell and select the following options.
- Finally, the output of unique values will be listed in the table.
Read More: Use COUNTIF Function to Count Unique Values in Google Sheets
4. Using ARRAYFORMULA
You use the ARRAYFORMULA function to remove the data of names that appeared more than once in the dataset. Follow the below steps to find unique values using the ARRAYFORMULA.
📌 Steps:
- In the beginning, create a column named Occurrence like the earlier method. After that, select a cell in the column and enter the following formula.
=ARRAYFORMULA(IF(C5:C<>"",COUNTIFS(C5:C,C5:C,ROW(C5:C),"<="&ROW(C5:C)),))
Formula Breakdown
- COUNTIFS(C5:C, C5:C, ROW(C5:C), “<=”&ROW(C5:C)):
The COUNTIFS function will initially search for the value of cell C5 in the C5:C range. Then it will check whether the ROW(C5:C) or row number of the C5 cell is less than or equal to the row number returned by ROW (C5:C). It will count the value if the cell value meets the conditions. It will return 1 in this case.
- IF(C5:C<>””,COUNTIFS(C5:C,C5:C,ROW(C5:C),”<=”&ROW(C5:C)),):
And finally, IF(C5:C<>””,) will result in 1 if the value in the C5 cell is not zero.
- Here, the ARRAYFORMULA function is used to get the output values as an array.
- Then select the Occurrence column to create a filter by selecting the option Data >> Create a filter.
- From the filter options select the options according to the following image.
- In the end, the final output will appear like the following image.
5. Applying Conditional Formatting
Applying the conditional formatting option in Google we can format the unique values and then using the filter option we can find them easily. To apply the conditional formatting option to find the unique values, follow the steps below.
📌 Steps:
- Initially, select the whole dataset and from the menu bar select Format >> Conditional formatting.
- A pop-up box will appear. Select the following options and enter the formula.
=COUNTIF($B$5:$B5,$B5)<2
- After the last step, the unique cells will be colored green.
- Now, from the top menu select options Data >> Create a filter.
- Then select the following options.
- Finally, the output will look like the following image.
Things to Remember
- Insert functions properly.
- When using formulas, make sure to properly select ranges and cells.
Conclusion
Hopefully, you can now find unique values in Google Sheets after going throw the article. If you have any queries or suggestions please comment below. Visit the OfficeWheel website for more Google Sheets-related helpful articles.
Related Articles
- How to Get Unique Values Without Blanks in Google Sheets
- Filter Unique Values in Google Sheets (5 Simple Ways)
- How to Filter Unique Rows in Google Sheets (4 Easy Ways)
- Apply QUERY for Unique Rows in Google Sheets (4 Ways)
- How to Highlight Unique Values in Google Sheets (9 Useful Ways)
- Count Unique Values in Multiple Columns in Google Sheets
