Filter Unique Values Using the Filter Menu in Google Sheets

Have you ever wanted to effortlessly filter out duplicate values in Google Sheets? Well, good news! With the help of the Filter Menu, you can easily filter by unique values and keep your data clean. It’s like having a magic wand to tidy up your spreadsheet!

Advantages of Using the Filter Menu

When you filter your data using the Filter Menu Command interface, you gain some amazing advantages. Firstly, you can make edits directly on the filtered data, saving you time and effort. There’s no need to copy and paste data onto another sheet or range.

Now, let’s dive into the details of how to filter unique values using the Filter Menu in Google Sheets.

Filtering Unique Values – The Custom Formula Way

To accomplish this, I’ll show you how to use a custom formula with a sorted list as an example. Take a look at the sample data below:

As you can see, there are some books that appear more than once in column B. Our goal is to filter column B and display only the unique book titles.

Here’s how to achieve that using the Filter Menu in Google Sheets:

How to use a custom formula in the Filter menu

1. Select the columns A and B that contain your data.
2. Click on the “Data” menu and choose “Create a filter”.
3. Now, click on the drop-down menu in cell B1 to access the filter options.
4. Apply the following custom formula in the Filter Menu’s custom formula field:

=COUNTIF(B2:B,B2:B)=1

That’s it! You’re done! By using this custom formula, you’ll now have a unique filter applied to your data, showing only the distinct values in column B.

“But wait,” you might be wondering, “can I use this formula on unsorted data as well?” Absolutely! The beauty of this method is that it works equally well on both sorted and unsorted data, without any changes needed in the formula. However, I still recommend sorting your data before applying the formula. Here’s why:

Let’s say you have a list in column A like the one below:

1 Agatha Christie
2 Agatha Christie
3 Agatha Christie
4 Helen Keller
5 Helen Keller
6 Helen Keller

Using the custom filter method described earlier, only rows 3 and 6 would be displayed, as they are the last rows in each group. However, if your data is unsorted, the formula would pick the values in rows 5 and 6, which are the last rows in the unsorted group.

To better visualize this, take a look at the following example of unsorted data with a unique filter applied:

As you can see, the result is unique, but the order of the rows might not be as pleasing. Sorting your data before applying the unique filter ensures a more organized and coherent result.

So, the next time you find yourself needing to filter by unique values in Google Sheets, remember to use the Filter Menu and sort your data beforehand for the best results. Happy filtering!

Stay up-to-date with the latest Google Sheets tips and tricks on Crawlan.com.