Skip to Content

How To Use The UNIQUE Function In Google Sheets (With Examples)

Imagine a world where you can effortlessly extract distinct values from a long list, eliminate duplicates, and streamline your data analysis with just a few clicks. That’s the power of the UNIQUE function in Google Sheets.

In today’s tutorial, you’ll learn how to use the UNIQUE function in Google Sheets to bring clarity to your data.

But before diving deeper into the topic, let’s quickly understand what the UNIQUE Function is.

What is the UNIQUE Function In Google Sheets? 

The UNIQUE function is a handy tool that helps you clean up your data in Google Sheets. It’s perfect for when you have a long list of information and want to eliminate duplicates. Basically, it only keeps the unique, one-of-a-kind rows and throws out the copies.

Here’s the syntax of this function.


In this formula, “range” is just the place where all your data is hanging out. You’re telling the UNIQUE function, “Hey, go through this list and show me only the special items that don’t repeat.”

Why is this useful? Well, imagine you have a list of things, and some of those things are written down more than once. So the UNIQUE function is like a helpful assistant that says, “I’ll help you see only the stuff that’s different.”

This comes in really handy when you’re dealing with data that has lots of repetitions. For example, think about surveys where people give similar answers.

To better understand how the UNIQUE function works, let’s quickly explore a use-case scenario. 

Scenario/Use Case

Imagine you’re a small business owner, and your spreadsheet holds a sprawling inventory list. As time passes, the list swells with multiple entries for the same products, creating a tangle of confusion and inefficiency. 

The prospect of manually wading through endless rows of data to sift out distinct items is nothing short of daunting, fraught with potential errors. This is where the UNIQUE function in Google Sheets emerges as a beacon of hope, promising to simplify your data management.

As we delve deeper into today’s guide, we will embark on a journey to unravel the mysteries of the UNIQUE function in Google Sheets.

Our mission? To ensure that even beginners can grasp its transformative potential. We will dissect the concept, provide clear and practical examples, and guide you through scenarios that vividly illustrate how this invaluable tool can elevate your data management prowess. 

By the end of our exploration, you won’t just understand the UNIQUE function; you’ll appreciate how it can revolutionize how you interact with data. 

Example 1: Putting The UNIQUE Function to Work – A Practical Use Case

Now that we’ve laid a strong foundation and shared a unique scenario to help you understand how to use the UNIQUE function in Google Sheets, let’s roll up our sleeves and get hands-on with it.

To make things crystal clear and easy to understand, we’ll work with a sample data set. Find it below: 

Before we get down to business, kindly read the following section:

Access Our Sample Google Sheet

To better grasp the UNIQUE function in Google Sheets, you can copy our practice sheet by clicking the link below. Remember that this sample sheet features data for both examples covered in today’s guide.

Click Here To Copy

If you have your own data, that works too.

Step 1: Select a Blank Cell 

First things first, let’s pick an empty cell in our spreadsheet. This is where we want our results generated. For this example, we’ll use cell H2. So, go ahead and click on cell H2 in your spreadsheet.

Step 2: Type In Your UNIQUE Formula

Chosen the cell where you want your result to be generated? Great. Now, let’s jump into the interesting aspect of today’s guide. Navigate to the formula bar and type in the following formula: 

=Unique (B2:D11)

Step 3: Hit Enter

After entering the formula, all we need to do is press the “Enter” button on our keyboard. Google Sheets will return a result that shows only the unique rows from the specified range. It will remove duplicate rows based on all columns (Product Name, Category, and Price). Here’s what the result would look like:

As you can see from the above screenshot, the UNIQUE function has removed the duplicate rows (e.g., the second “Laptop” row and the second “Smartphone” row) and kept only one instance of each unique row in the same order they appeared in the original data range. 

This provides you with a clean list of unique product entries based on the specified columns.

Example 2: Putting The UNIQUE Function to Work – A Practical Use Case

Now, let’s dive into another example to illustrate further how to use the UNIQUE function in Google Sheets. We’ll keep this example simple and straightforward to help you grasp the concept easily.

Imagine you’re a teacher, and you have a list of your students’ names in a Google Sheets document. Over time, you’ve recorded their names for various class activities, and some names appear more than once. You want to create a clean list of unique student names. Here’s how you can do it using the UNIQUE function:

We will use the following sample data for this example: 

Step 1: Choose a Blank Cell

Quickly access your spreadsheet and choose a blank cell. This is where you want your result to be generated. For this example, we will use cell D2. 

Step 2: Enter the UNIQUE Formula

Having selected the cell where you want the result to be generated, head over to the formula bar and type in the following UNIQUE formula: 


Step 4: Press “Enter”

After entering the formula, press “Enter.” Google Sheets will process the data and display a list of unique student names starting from cell D2.

Here is what our spreadsheet now looks like after executing the steps we detailed above: 

From the screenshot above, you can see that we now have a list of unique student names, with any duplicates removed, starting from cell D2 and below. We achieved this by simply leveraging the power of the UNIQUE function in Google Sheets. 

Final Thoughts 

In our Google Sheets adventure, we’ve explored a handy tool called the UNIQUE function. It’s like a data cleaner that tidies up messy information by keeping only the unique stuff and tossing out duplicates.

Remember, whether managing products or tracking student names, the UNIQUE function in Google Sheets is your go-to ally for keeping your data clean and organized. By using this function, you can work more efficiently and keep your spreadsheets in tip-top shape.