How to Use Google Sheets Unique Values Function?

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.

unique function with 2 column

Let us see how to use the UNIQUE() function to retrieve the values.

  1. In a blank cell of the area in which you want the results to be populated.
  2. Enter the function as shown in the cell B13 as below.
ZOer4g3FU41iZ 5mgD Ar1ZJiOzQjX5FFNNE56panSp0bTCjfKQCrffQ55GvhH1b5dsMTnMhewc4jE7W
  1. We have selected the column range C1:D11 to find out the unique values.
  2. Press ENTER to get the results.
Columns
  1. You will see 7 rows are unique out of 11 rows. 
  2. To further filter the unique score, you can put the UNIQUE() function for scores.
  3. To do so, choose the reference range of the Score column.
unique score
  1. Put the function with reference cell range and ENTER.
  2. The results are shown as below.
function on sheets
  1. You have filtered unique values of the score (results are D14:D18).
  2.  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.
filtered unique values
  1. ENTER. The results will be sorted in the ascending order of value.
ascending order of value
  1. 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.

dataset

To perform analysis, it is recommended to convert the dataset to vertical view. 

  1. Use the TRANSPOSE() function to change it to vertical view. 
TRANSPOSE function
  1. ENTER. You will see the data in vertical view.
transpose function 1
  1. You can remove the horizontal view or move the transposed view to another sheet.
  2. Put the UNIQUE() function on the Price Range column.
unique function range
  1. ENTER. The unique ranges will be shown.
unique ranges

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. 

UNIQUE with Single Column
  1. Choose the Assignee Real Name column while using the UNIQUE() function.
Unique function
  1. ENTER. You will see the unique team members that are assigned one or more tickets.
function on google sheets
  1. If the dataset is appended by more rows, you can simply refresh the results with updated values.
dataset for unique
  1. Increase the cell reference range and the results will be automatically updated.
reference range
  1. The results are shown with updated values.
  2. Similarly, you can identify the type of status(es) available in the dataset.
  3. Put the UNIQUE() function in the blank cell and type the cell reference of the Status column inside the function.
UNIQUE function in the blank cel
  1. ENTER. The unique values of status(es) will be shown.
values of statuses
  1. 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.

UNIQUE with COUNTIF and SUMIF
  1. To find the count of Box A received in the duration, we will put the formula as below.
find counif
  1. The results of Box A count comes to 2. Further, we will see how to view unique values of all goods received.
  2. Combine the UNIQUE() and COUNTIF functions to retrieve the results as expected.
UNIQUE and COUNTIF
  1. You will see the unique count of boxes received within the specified period.
  2. It is that simple!

You can also retrieve SUM TOTAL of quantity received per box using the UNIQUE() and SUMIF functions. 

  1. First find out the unique range of goods (boxes in this example.)
  2. Then, use the SUMIF() function to specify the range of goods and their quantities.
SUMIF function
  1. The UNIQUE() function identifies the goods to obtain the total quantity per box.
  2. 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.

COUNTUNIQUE
  1. To obtain the count of unique price ranges for the brands available, put the function as shown below.
count of uniqu
  1. ENTER. You will see the 4 brands have a unique price range. The function returns the count of 4.
  2. You can further evaluate the unique values that brands offer. After obtaining the subset of data, you can use it in multiple ways.

Takeaway

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. 

You may also like: