Skip to Content

How To Sort & Filter Duplicates In Google Sheets

Organizing data is the cornerstone of productivity and clarity, especially in the vast realm of digital information. Imagine having a clutter-free virtual workspace where every piece of information is neatly arranged, making your tasks more manageable and your insights sharper. 

Welcome to the world of Google Sheets, where the power to effortlessly sort & filter duplicates in Google Sheets is at your fingertips.

Whether you’re a spreadsheet novice or a seasoned data wrangler, this article will teach you how to sort & filter duplicates in Google Sheets seamlessly. 

After reading today’s guide, you can say goodbye to confusion and inefficiency and say hello to a streamlined data haven where you can confidently make informed decisions.

But before we dive into the nitty-gritty of showing you how to sort & filter duplicates in Google Sheets, let’s go over a scenario section that conveys the message better to readers. 

Scenario/Use Case

Let’s dive into a real-life scenario to better understand the significance of mastering the art of sorting & filtering duplicates in Google Sheets.

Imagine you’re the head organizer for a community fundraising event. You’ve gathered a list of generous donors and their contributions, eager to acknowledge their support. However, as the list grows, so does the potential for errors and duplicate entries. A few donors might have used different variations of their names or contributed multiple times, leading to a messy spreadsheet that’s challenging to decipher.

This is where the magic of sorting & filtering duplicates in Google Sheets comes to the rescue. Instead of manually sifting through rows upon rows of data, you can effortlessly identify and eliminate duplicate entries. 

With a few simple steps, you can ensure that each donor is recognized accurately and your gratitude is extended to everyone who deserves it.

How To Sort And Filter Duplicates In Google Sheets – Practical Examples

Now that we’ve covered a bit of the basics about sorting and filtering duplicates, let’s dive into the exciting part of this guide. We’re about to walk you through the steps, one by one, to show you exactly how to sort & filter duplicates in Google Sheets.

To make things easy to understand, we’ll use a sample data we have put together. This data will help us show you how sorting & filtering works in Google Sheets.

With our data now sorted, let’s get down to business. 

Step 1: Highlight Your Data Set

First, we need to choose the data we want to sort & filter for duplicates. In our example sheets, we’ll simply click and highlight all the information present.

Step 2: Data > Data Cleanup > Remove Duplicates

Now that you’ve highlighted your data in the spreadsheet, let’s move on to the next step. Go to the “Data” menu at the top, and scroll down until you see the “Data cleanup” choice. When you click on that choice, a few more options will appear. Among these options, pick the one that says “Remove duplicates.”

Step 3: Tweak Duplicates Option

After selecting the option for Remove Duplicates, you should see a small pop-up window. Here, you’ll need to make some changes. 

First, check the box for “Data has a header row.” Now, uncheck other boxes, leaving only the option for “Column B “Contribution Amount.” With that done, simply click on Remove duplicates. 

With the option for Remove duplicates selected, you should get a pop-up message like this one: 

If you return to your spreadsheet, you should see that you have successfully sorted & filtered duplicates from your data. 

Here is what ours looks like after sorting and filtering duplicates from our sample sheet. 

From the screenshot above, you can see that we no longer have any duplicates there. Now, that’s how easy it is to sort & filter duplicates in Google Sheets. 

Highlight Duplicates Using Colors For Easy Removal

After learning how to sort & filter duplicates in Google Sheets using the “Remove duplicates” method, let’s explore another clever technique to bring attention to duplicates within your spreadsheet.

But before we jump into that, let’s set the stage.

Organizing duplicated information doesn’t have to be a daunting task. In reality, Google Sheets provides a clever solution that involves using colors to highlight duplicates. This straightforward trick simplifies the process of finding and removing duplicates.

To illustrate how you can sort and filter duplicates in Google Sheets using this approach, we will continue using the same sample data as in our previous example.

Step 1: Select Your Data

You need to highlight the data in your spreadsheet as you did with the previous method we showed you. 

Step 2: Format > Conditional Formatting 

Now that we have our data highlighted, navigate to the Format menu. Scroll all the way down and select the option for Conditional formatting. 

Step 3: Set The Rule 

After choosing the option for Conditional formatting, a panel will pop up on the right side of the screen. Here, you’ll create a rule for highlighting duplicates. Select “Custom formula is”, and in the field below, enter this formula:

=countif(A:A, A1)>1

Once you’re done setting the formula, click on the colour box to choose the highlight colour for duplicates. Pick a colour that stands out and is easily distinguishable. For this example, we will choose green. 

After setting the rule, finish off by clicking Done. If you did everything exactly as we showed you, your spreadsheet should look something like this: 

Looking at the picture, you can easily spot the repeated data marked in green. That’s how you sort & filter duplicates in Google Sheets. It’s as simple as that. 

With the duplicated data now highlighted in your selected colour, you can simply remove the duplicates if you want. 

Final Thoughts 

Think of organizing data like tidying up your room—Google Sheets is your digital cleanup tool. It helps arrange messy information so you can work better. This guide is like a treasure map, showing beginners and pros how to sort & filter data in Google Sheets.

Today’s guide explained each step like a friend showing you a game. First, you pick the messy stuff (data), then you click some buttons to clean up. It’s like turning chaos into order.

And guess what? There’s an even cooler trick. Our guide teaches you how to use colours to highlight repeat names. 

After reading today’s guide, you should be able to sort & filter duplicates in Google Sheets like a pro.

Other Related Google Sheets Tutorials