Skip to Content

How to Find Unique Values in Google Sheets (2 Easy Methods)

Navigating the world of data can be daunting, but guess what? Google Sheets is your trusty compass in this digital wilderness. Whether you’re a spreadsheet novice or a seasoned data explorer, the quest to find unique values in Google Sheets is an essential skill that will unlock countless possibilities for your data management. 

In today’s article, we will dive into the techniques and tools that Google Sheets offers to unearth those one-of-a-kind gems in your datasets. 

But before we delve into more detail about how to find unique values in Google Sheets, let’s quickly look at a use/case scenario that conveys the message better.

Scenario/Use Case

Imagine you’re the captain of a cargo ship carrying thousands of crates filled with exotic fruits from around the world. Your cargo manifests contain detailed records of each fruit variety. As you sail through the high seas of data, a challenge arises – your investors need to know how many unique types of fruits you’ve brought on board.

This is where the magic of Google Sheets comes into play. You can use the “Find Unique Values” function to swiftly identify each distinct fruit type among the crates of data. It’s as if you’ve received a magical magnifying glass highlighting the rare gems in your treasure trove. This feature allows you to sift through vast data oceans and easily pick out the unique islands of information.

As you’re about to discover, finding unique values in Google Sheets is not just about organizing fruit varieties; it’s a fundamental skill that can be applied to countless real-world scenarios. Whether tracking sales, managing inventory, or analyzing survey data, Google Sheets can be your trusty first mate, helping you find unique values to steer your data ship in the right direction.

Finding Unique Value In Google Sheets: The Best Approach 

In this beginner-friendly guide, I’ll walk you through two straightforward methods to find unique values in your data using Google Sheets. Whether you’re dealing with data in a single column or across multiple columns, these techniques will help you spot unique values in your dataset without any fuss. 

Method 1: Using the ‘Remove Duplicates’ Tool

The ‘Remove Duplicates‘ tool is like a built-in detective for your data. It automatically identifies and removes duplicated entries, leaving you with a clean list of unique values. This method is handy when you want to ensure that your data contains only distinct items.

Method 2: Using the UNIQUE Function

The UNIQUE function is a powerful tool that works like magic. It extracts unique values from your data, even when you have data across multiple columns. It’s versatile and can be applied to single-column and multi-column datasets, making it a valuable feature for data organization.

By learning these two methods, you’ll be equipped to handle data confidently, ensuring that you’re working with unique values, whether your data is organized in a single column or spread across multiple columns.

Copy Sample Data

If you want to follow along with the step-by-step guide, feel free to copy our sample data below.

Copy Sheet

For those who have their own sheet, you can simply replicate the steps we will show you shortly.

Find Unique Values in Google Sheets Using The Remove Duplicates Tool

Having provided you with some background on the various methods to find unique values in Google Sheets, it’s time to get hands-on. 

Let’s dive into the practical part of finding unique values in Google Sheets. We’ll begin by using a handy tool called “Remove Duplicates.” This tool makes it easy to spot and keep only the distinct values in your data.

For our step-by-step guide, we’ll use the following sample dataset. Think of it as your practice canvas to understand how this works:

With our sample data nicely put together, let’s quickly show you the step-by-step process of finding unique values using the Remove Duplicate tool. 

Step 1: Open Google Sheets

Open your Google Sheets document where you have your data, or create a new one if needed. If you’re following our example, you can simply copy our sample data and follow along. 

Step 2: Select Your Data Range

Once you open your Google Sheets document, select the cells with data you want to find unique values. For this guide, we will only focus on the column containing fruit types. 

After highlighting the cells with data through which we want to find unique values, here is what we have: 

Step 3: Data Menu > Data Cleanup > Remove Duplicates

Now that we’ve selected the data we need to find unique values in Google Sheets, let’s move on to the next step. We’re going to make things even simpler.

What we want to do here is navigate to the Data menu, where you’ll be presented with several options. 

From those options, choose the option for Data cleanup. This will reveal several sub-options. Here, you need to select the option that says “Remove duplicates.”

Step 4: Choose the Column to Check

After selecting the option for Remove duplicates, you should see a dialog box appear. 

In this box, you’ll see a list of columns in your selected range. Make sure the checkbox for the column you want to check for duplicates is selected (in our case, it’s “Fruit Type”).

You can check or uncheck the “Data has header row” option depending on whether your data has a header row or not. In our sample dataset, we have a header row, so this option should be checked.

Once you have made all the changes as we showed you, simply click on the option for Remove duplicates. 

Step 5: Review the Results

With the option for Remove duplicates selected, Google Sheets will process your data, removing any duplicate values from the selected column. You’ll see a message indicating how many duplicate values were removed and how many unique values remain.

After removing the duplicates, here is what our spreadsheet now looks like: 

Looking at the screenshot above, you can see that we have successfully removed any duplicate data, leaving us with only unique values. 

Now, that’s how to find unique values in Google Sheets using the Remove Duplicates tool. This technique can help you keep your data clean and free from repeated entries.

Find Unique Values in Google Sheets Using the UNIQUE Function

In the previous method we just wrapped up, we showed you how to use the Remove Duplicates tool to find unique values in Google Sheets. Now, we want to show you another brilliant method that makes it even easier to find unique values in your spreadsheet. 

We will use the same sample data we used for the previous method to show you how to find unique values in Google Sheets using the Unique Function. 

But before we delve any further, let’s quickly look at the syntax for the Unique function. 

Understanding the Syntax for Unique Function

To use the UNIQUE function, you simply need to follow this format:


The “range” in this formula is the group of data you want to find unique items in.

This “range” can be a collection of either column names or cell locations. When you apply the UNIQUE function, it will display the unique items starting from the cell where you put the formula.

Now that we know precisely what the syntax for the UNIQUE function entails, let’s show you the steps to find unique values using this method. 

Step 1: Select An Empty Cell

First thing first, select an empty cell in your spreadsheet. This is where we want the result of our unique values to be generated. For this particular example, we will select cell E4. 

Step 2: Type in the UNIQUE Formula

With the cell where we want the unique values generated selected, it’s time to enter the UNIQUE formula. To do that, navigate to the formula bar and type in the following formula: 


Here, B4:B14 refers to the range of cells where your data is located.

Step 3: Press Enter

After typing the UNIQUE formula like we described, all you need to do is hit the Enter button on your keyboard. Google Sheets should automatically generate the result for unique values in the selected cell. 

Here is what ours looks like: 

A close look at the screenshot above shows that we have successfully found unique values in Google Sheets using the UNIQUE function. That wasn’t too challenging, was it?

Final Thoughts

Discovering how to find unique values in Google Sheets is a valuable skill for anyone working with data. 

In this guide, we explored two straightforward methods: using the “Remove Duplicates” tool and the powerful UNIQUE function. Whether you’re managing inventory, analyzing survey data, or navigating through the seas of data like a ship captain, these techniques will help you keep your data clean and efficient.

Remember, data is a treasure trove of insights, and finding unique values is like sifting through the gems in your data mine. So, whether you’re new to spreadsheets or a seasoned data explorer, Google Sheets offers versatile tools to make your data work smarter.

For those hungry for more knowledge and eager to explore further, check out our website for more insightful Google Sheets tutorials and tips. We’re here to help you unlock the full potential of your data.