Google Sheets’ unique function works as a query to return a set of unique values from the selected cell range (row(s) or column(s)). Putting the reference range in a column with the unique function will give you the results as expected.
The unique function is very useful when doing data analysis tasks. You may come across the use cases where you need to filter a dataset to remove duplicate records, identify the data with unique values of a property and so on.
Some data examples are given below to explain how the unique formula works.
- To find out the unique score of the exam participants and their classroom.
- To find out the unique price range of mobile sets based on their brands
- To find out the unique assignee’s of the tickets they are assigned, status and type of tickets available
- To find out the quantity of goods received in a week
Example 1 – UNIQUE() function Google Sheets with 2 Columns
This dataset contains exam participants from different classrooms and their scores. We need to find out the unique scores from the classes they belong to.
Let us see how to use the UNIQUE() function to retrieve the values.
- In a blank cell of the area in which you want the results to be populated.
- Enter the function as shown in the cell B13 as below.
- We have selected the column range C1:D11 to find out the unique values.
- Press ENTER to get the results.
- You will see 7 rows are unique out of 11 rows.
- To further filter the unique score, you can put the UNIQUE() function for scores.
- To do so, choose the reference range of the Score column.
- Put the function with reference cell range and ENTER.
- The results are shown as below.
- You have filtered unique values of the score (results are D14:D18).
- Further, the score results can be sorted in the ascending or the descending order. Put the SORT() function by selecting the reference range of D14:D18.
- ENTER. The results will be sorted in the ascending order of value.
- The results values are shown with MIN to MAX range once sorted.
The score analysis can be performed using the UNIQUE() and the SORT() functions.
Example 2 UNIQUE() function Google Sheets with TRANSPOSE()
You have a dataset with row headers and values of mobile phone brands and their price range.
To perform analysis, it is recommended to convert the dataset to vertical view.
- Use the TRANSPOSE() function to change it to vertical view.
- ENTER. You will see the data in vertical view.
- You can remove the horizontal view or move the transposed view to another sheet.
- Put the UNIQUE() function on the Price Range column.
- ENTER. The unique ranges will be shown.
Example 3 – UNIQUE() with Single Column at a time
This dataset contains a list of tickets assigned to the helpdesk team with ticket IDs. We will find out the team members who will need to resolve the tickets.
- Choose the Assignee Real Name column while using the UNIQUE() function.
- ENTER. You will see the unique team members that are assigned one or more tickets.
- If the dataset is appended by more rows, you can simply refresh the results with updated values.
- Increase the cell reference range and the results will be automatically updated.
- The results are shown with updated values.
- Similarly, you can identify the type of status(es) available in the dataset.
- Put the UNIQUE() function in the blank cell and type the cell reference of the Status column inside the function.
- ENTER. The unique values of status(es) will be shown.
- Similarly, you can find out the type of tickets also can be filtered.
Example 4 UNIQUE() with COUNTIF() and SUMIF()
This dataset contains a list of goods received datawise. We will find out the count of each type of good received. This will result in retrieving a single good count. We will extend the function to retrieve the unique values returned for all goods.
- To find the count of Box A received in the duration, we will put the formula as below.
- The results of Box A count comes to 2. Further, we will see how to view unique values of all goods received.
- Combine the UNIQUE() and COUNTIF functions to retrieve the results as expected.
- You will see the unique count of boxes received within the specified period.
- It is that simple!
You can also retrieve SUM TOTAL of quantity received per box using the UNIQUE() and SUMIF functions.
- First find out the unique range of goods (boxes in this example.)
- Then, use the SUMIF() function to specify the range of goods and their quantities.
- The UNIQUE() function identifies the goods to obtain the total quantity per box.
- It’s quite useful.
Example – The COUNTUNIQUE() function
This dataset contains a range of values. To find out the count of unique values/price range, the COUNTUNIQUE() function is used.
- To obtain the count of unique price ranges for the brands available, put the function as shown below.
- ENTER. You will see the 4 brands have a unique price range. The function returns the count of 4.
- You can further evaluate the unique values that brands offer. After obtaining the subset of data, you can use it in multiple ways.
Google sheets are faster in performance and provide a range of financial functions to perform data analysis. Excel users find it so easy to use Google Sheets as all functions are similar. In addition to that, database experts also can easily use the Google Sheets as it provides Query syntax that is very similar to write query statements to retrieve results from the database tables.
The Google Sheets have captured all target segments for the usage – starting from a fresher to database expertise.