How To Filter Unique Values In Google Sheets

Even though there isn’t a dedicated feature or built-in function that allows you to filter unique values in Google Sheets, we have found a way around it.

And guess what? You don’t need to be a data wizard to do this.

In today’s comprehensive Google Sheets tutorial, we’ll show you how to use the COUNTIF function to filter unique values in Google Sheets.

Our objective with this guide is to hold your hand and walk you through the process step by step.

But before we delve into the practical aspect of today’s tutorial, let’s go over an applicable scenario that will provide better insight into why anyone would want to filter unique values in Google Sheets.

Scenario/Use Case

Imagine you’re planning a grand event, a masquerade ball for your closest friends. Your guest list is extensive, and you’ve collected RSVPs via Google Forms. Now, you have a spreadsheet filled with names, but there’s a hiccup – some guests might have submitted their responses more than once or brought along their significant other, inflating the list with duplicate entries.

This is where Google Sheets steps in as your trusty event planner. By filtering unique values, you can swiftly trim down your list to a manageable size, ensuring that your guest count remains accurate. You’ll easily spot the solo attendees, the mysterious masqueraders, and the potential gatecrashers.

Read on as we guide you through the simple yet magical process of filtering unique values in Google Sheets.

After reading today’s post, you’ll be able to use this skill not only for event planning but also for data analysis, inventory management, and countless other applications.

Let’s jump right into the practical aspect of today’s guide, shall we?

Filtering Unique Values In Google Sheets – Step-By-Step Guide

Now that we’ve talked about what it means to filter unique values in Google Sheets and shared a use case/scenario to help you grasp the idea better, you’re probably excited to try it out yourself. Well, let’s roll up our sleeves and dive right into the hands-on part of this guide.

We’ll take a simple set of data to show you exactly how this works. Think of this data as your playground for learning.

Here is what our sample data looks like:

With our data now sorted out, let’s show you the step-by-step process of filtering unique values in Google Sheets.

Step 1: Select The Data Range

The first thing we would like to do is highlight the range of data for which we would like to filter out unique values. Since our objective with this example is to filter out the names of guests who have entered their RSVP status twice, we will go ahead and highlight the column for Guest Name.

Step 2: Data > Filter Views > Create New Filter View

With the range of data we want to use to filter unique values selected, we now need to navigate to the top of our Google Sheets screen. You’ll find a menu called Data, Click on it.

Once you select the Data menu, you’ll see several options. Look for one called “Filter views” and click on it.

After you’ve clicked on “Filter views,” a few more options will appear. Among these options, you’ll see one that says “Create new filter view.” Click on this option.

Here is what our spreadsheet looks like after creating the new filter view.

Step 3: Setup the Filter Condition

Once you’ve enabled the filter, you’ll notice a small filter icon (funnels) appear in the header row of your selected data range. Click on the filter icon for the column you want to filter for unique values. In our case, it’s the “Guest Name” column.

From the option available, select the option for Filter by condition.

Step 4: Tweak Filter By Condition

After selecting the “Filter by Condition” option, you’ll notice a small box below with the word “None.” Click on the drop-down button next to this option, and then scroll down. Finally, select the “Custom formula is” option.

Step 5: Enter Your COUNTIF Formula

Once you select the option for “Custom formula is,” a box will appear where you can enter your COUNTIF formula.

In our scenario, we want to filter out guests who’ve responded twice to ensure only unique responses remain.

To achieve that, we will enter the following formula in the box provided.

=COUNTIF(A:A, A2)=1

This formula checks if the count of a guest’s name in column A is equal to 1. In simpler terms, it helps Google Sheets identify guests who have responded only once and filters out any duplicates.

Step 6: Apply the Filter Condition

After entering your formula, click the “OK.” Google Sheets will apply the filter based on your COUNTIF condition.

If you follow the steps exactly as we showed you, Google Sheets will automatically filter the unique values in your spreadsheet to meet your criteria. Here, take a look at what our spreadsheet looks like after filtering for unique values.

From the screenshot above, you can see that we’ve successfully filtered our spreadsheet to show only the unique values. This is how you filter unique values in Google Sheets. Easy right?

Final Thoughts

So, there you have it. You’ve ventured into the world of Google Sheets and discovered the magic of filtering unique values. It might sound a bit techy, but fear not – it’s simpler than you think.

Even though Google Sheets doesn’t have a special button for filtering unique values, we’ve shown you a clever workaround. And the best part? You don’t need to be a data wizard to do it.

In this comprehensive Google Sheets tutorial, we walked you through the entire process using a simple example. Before we dived into the practical part, we shared a unique scenario with you.

After reading today’s post, we are optimistic that you’ll be able to filter unique values in Google Sheets with no qualms.