Are you tired of dealing with duplicate values in your Google Sheets? Say no more! In this ultimate guide, we’ll introduce you to the powerful and versatile UNIQUE function in Google Sheets. Whether you’re a beginner or an advanced user, this guide will help you master the UNIQUE function and take your data analysis skills to the next level. So let’s dive in!
Example of the UNIQUE Formula
Before we get started, it’s important to note that the UNIQUE function only works for columns, not rows. Now, let’s walk through an example to understand how the UNIQUE function works.
Imagine you have a column of values and you want to filter out the duplicate values to find the unique ones. Let’s apply the UNIQUE function to the column titled “States”:
In this example, the range of values under the “States” column is A2:A11. To apply the UNIQUE function, simply use the following formula:
=UNIQUE(A2:A11)And voila! Here’s the result:
Syntax of the UNIQUE Function
The syntax of the UNIQUE function is quite simple:
=UNIQUE(range)Where range is the column you want to filter for unique values.
Google Sheets UNIQUE with Multiple Columns
Did you know that you can apply the UNIQUE function to a range containing multiple columns? Yes, you heard it right! The UNIQUE function can filter out duplicate rows where all the columns in a row are identical to another row. Let’s explore this with an example.
Consider the following array of values:
We’ll use the same example from the previous section. To apply the UNIQUE function to the entire range, use the following formula:
=UNIQUE(F2:G11)And here’s the result:
As expected, even though the first column contains duplicate values, the second column does not. Therefore, Google Sheets considers them as unique.
Now, let’s tweak the input a bit:
As you can see, there are a couple of duplicates: the entry for New York, New York, and Los Angeles, California. Let’s apply the UNIQUE function to the range:
=UNIQUE(L2:M11)And here’s the result:
Amazing, right? The UNIQUE function effectively filters out the duplicate rows, leaving us with only the unique values.
Using UNIQUE with the QUERY function
It turns out you can apply the UNIQUE function to the results of the QUERY function as well. Specifically, you can nest the QUERY function within the UNIQUE function. Let’s reproduce the results from the previous sections using the combination of UNIQUE and QUERY.
For a single column, use the following formula:
=UNIQUE(QUERY(A16:B25, "select A"))And here’s the result:
To apply the UNIQUE and QUERY functions to multiple columns, use this formula:
=UNIQUE(QUERY(F16:G25, "select F, G"))And here’s the result:
We hope this article has shed light on the powerful UNIQUE function in Google Sheets and given you a better understanding of how to use it effectively. If you found this guide helpful, you might also enjoy our articles on calculating time in Google Sheets and using the ISBLANK function in Google Sheets.
To optimize your workflow and improve your data analysis skills, we recommend checking out our guide on extracting a domain from an email and trying our software for tracking invoice due dates.
For more helpful guides and tips, visit Crawlan.com.
Now that you’ve unlocked the secrets of the UNIQUE function, go forth and conquer your data with confidence! Happy Sheets-ing, my fellow data enthusiasts!
