The UNIQUE function in Google Sheets is useful if you want to extract unique values from a given range.
Meaning, the UNIQUE function lets you extract unique and unique distinct values either in rows or columns from a range that repeats information.
Table of Contents
The rules for using the UNIQUE function in Google Sheets are as follows:
- The second and third arguments of the UNIQUE function are optional.
- If no values are passed to the second and third argument, the UNIQUE function will consider FALSE values as arguments.
- The UNIQUE function returns arrays of information
- The UNIQUE function will return a #REF! Error if the expected range of results isn’t clear for any values.
- The UNIQUE function will consider blank values in the given range.
Let’s take an example.
Avery would like to know the list of her unique customers that appear in the customer’s log for April. See her table below:
Avery has been an avid fan of Google Sheet functions. She knows a simple trick to be able to extract the unique values from her list. See the results below:
Avery used the UNIQUE function above to extract unique customer’s names from her list in the customer’s log.
She now realized that she needs to improve her marketing strategy to be able to increase her clients.
Let’s have another example!
Jaden wants to know the range of age of his students for the upcoming school year. He sent them a Google Form which contains questions about their basic information.
He pulled the list of the students’ answers for the age field:
Jaden has about more than 40 students and it would take so much of his time to manually check each student’s age to get the age range.
This is when the UNIQUE function will be handy. Jaden used the UNIQUE function and he was able to pull the unique ages from the list.
Finally, he was able to find out that the age range of his students for this year would be from 13 years old – 19 years old.
Watch out for a more advanced tutorial and examples on how you can use the UNIQUE function in the coming weeks. Be sure to subscribe to be notified.
Perfect! Let’s begin getting to know more about our UNIQUE function in Google Sheets.
The Anatomy of the UNIQUE Function
So the syntax (the way we write) the UNIQUE function is as follows:
=UNIQUE(array, [by_col], [exactly_once])
Let’s dissect this thing and understand what each of these terms means:
- = the equal sign is just how we start any function in Google Sheets. It is how Google Sheets understand that we are asking it to either do computation or use a function.
- UNIQUE() this is our UNIQUE function. It returns an array of unique values.
- array is a range or array from which to extract unique values.
- [by_col] is an optional argument that tells how to compare values. It can be either by rows or columns. FALSE (default) to compare rows and TRUE to compare columns.
- [exactly_once] is an optional argument that tells the method of extraction. FALSE (default) to extract all unique values and TRUE to extract values that occur once.
A Real Example of Using UNIQUE Function
Let’s take a look at the list of Avery’s customers below to see how the UNIQUE function is used in Google Sheets.
Notice in the formula used that there’s only 1 argument passed to our UNIQUE function, which is the range of information. The function treated the second and third arguments as the FALSE default values.
This means that, in our example above, the UNIQUE function is comparing rows and is looking for all unique values.
Hence, the resulting array in column C is the list of all unique names that appear in Column A.
What happens when we will pass TRUE to our third argument?
Let’s find out!
In cell G2, we will type in the following syntax:
See the results below:
On the same list of customers, the UNIQUE function above returned a different result.
Why is this so?
Well, that’s because we instructed our UNIQUE function above to extract the values from our list that only occur once by passing TRUE to our third argument.
This means that the four resulting names are the only names that occur once on the list. Others may have appeared twice or more.
This scenario is rarely used as we always want to extract all the unique values from our given list.
So, you will often see that most UNIQUE function users only provide the first argument.
You may make a copy of the spreadsheet using the link I have attached below.
How to Use UNIQUE Function in Google Sheets
- Click on any cell to make it the active cell. For this guide, I will be selecting C2, where I want to show the resulting array.
- Next, type the equal sign ‘=‘ to begin the function and then follow it with the name of the function, which is our ‘unique‘ (or ‘UNIQUE‘, not case sensitive like our other functions).
- Type open parenthesis ‘(‘ or simply hit Tab key to let you use that function.
- Now the exciting part! Let’s give our function its argument, the array. Type in ‘A2:A25’ or simply click cell A2 and drag it until A25.
- Since our names are distributed in rows and we are looking for all the unique values from the list, we no longer need to provide the second and third arguments.
- Hit your Enter or Tab key. Cell C2:C12 will now show you all the unique names that appear in our list or the return value of the UNIQUE function.
- Now, let’s try to extract the names that appear only once in the list.
- In cell G2, repeat steps 1 to 4.
- To let the Google sheet know that we’re done typing our first argument, we should now type in the delimiter or the character that separates each argument on a function. In this case, type comma (,).
- Now, our UNIQUE function is expecting us to pass the second argument. Since we are comparing values in rows, we can either pass the FALSE value or leave it blank. In this case, I’ll go ahead and leave it blank. To let our function know that we are leaving it blank, we can now proceed with the next delimiter, which is the comma (,).
- Let’s provide the third argument. Since we are now extracting the only names that appear once from the list, type in TRUE.
- Finally, hit your Enter or Tab key. Cell G2 will now show you the names that appear only once from our list or the return value of the UNIQUE function.
That’s pretty much it. You can now use the UNIQUE function in Google Sheets together with the other numerous Google Sheets formulas to create even more powerful formulas that can make your life much easier.