Remove Duplicates In Google Sheets

Written by Barry Allen

October 5, 2022

Remove Duplicates In Google Sheets

Introduction

Duplicates or duplicate entries are a prevalent problem for many people when it comes to keeping data entries. Consider the following scenario: you have the names and contact information of persons who responded to a survey. There’s a chance you’ll have duplicate records if you list them in a spreadsheet program like Google Sheets. In that instance, one person may have completed the survey multiple times, resulting in Google Sheets listing the entry twice. When it comes to enterprises, such identical listings are much more difficult. Consider what happens if a cash transaction is recorded many times. It would be a problem if you used that data to determine total expenses.
To avoid such issues, make sure the spreadsheet doesn’t have any duplicate records. What is the best way to accomplish this? In this post, you’ll learn how to eliminate duplicates in Google Sheets in different ways. Come on, without further ado, let us take a look at the subject.
If you are looking to remove duplicates in Google sheets, this article is for you.

Steps To Remove Duplicates In Google Sheets

Following are the steps to remove duplicates in Google Sheets:

Method 1: Using the Remove Duplicates Option

Step 1: Take a look at this example (see screenshot below). As you can see, the record “Ajit” has been entered twice. This is a duplicate of the previous record.

Step 2: Select or highlight the rows and columns to eliminate duplicate entries. Now select Data from the menu options. Scroll down, then go to data cleanup and click on the Remove duplicates option. Or instead of data click on help and type remove duplicates.

Step 3: A pop-up box will appear, prompting you to choose which columns to examine. Select the options that best suit your requirements, then click the Remove Duplicates button.

Step 4: All duplicate records would be removed, leaving just unique elements. The amount of duplicate records that were removed will be displayed in Google Sheets.

Step 5: Only one duplicate entry was deleted in our situation (Ajit). The duplicate entry has been eliminated by Google Sheets, as you can see (refer to the screenshot that follows).

Method 2: Remove Duplicates with Formulae

Formula 1: UNIQUE

For instance: =UNIQUE (A2:B6)

Step 1: This would look for duplicate entries in the range of cells specified (A2:B7). Enter the given formula in any empty cell in your spreadsheet. The range of cells you specify will be highlighted in Google Sheets.

Step 2: The unique records where you typed the formula will be listed in Google Sheets. The old data can then be replaced with unique records.

Formula 2: COUNTIF

Step 1: Take a look at the screenshot below, which contains one duplicate entry.

Step 2: Enter the formula =COUNTIF(A$2:A2, A2)>1 in cell C2 of the above screenshot.

When you press the Enter key, the result will now be shown as FALSE.

Step 3: Place the mouse pointer over the little square at the bottom right corner of the selected cell. Instead of your mouse pointer, you will now see a + symbol. Click and hold that box, then drag it up to the cell where the duplicate entries should be found. The formula would be copied to the remaining cells using Google Sheets. Or, if you get the option for autofill as shown above, then select it.

Step 4: When a duplicate entry is found, Google Sheet will automatically append the word “TRUE” in front of it.

NOTE: We have specified as >1 in this condition (greater than 1). As a result, this condition will be TRUE if an entry is found more than once. The result is FALSE everywhere else.

Conclusion

In this article, we have outlined all the steps in order to remove duplicates in Google sheets. If you have any other queries or suggestions, let us know in the comment area.

Read All Google Related How-To & Fixes here.

Author

  • Barry Allen

    Barry Allen is a Technical writer at Layman Solution. He is an expert in writing How To and Fixes articles.

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

Related Articles

Fix Pokemon Go GPS Signal Not Found

Fix Pokemon Go GPS Signal Not Found

Introduction If you enjoy online games, you've probably heard about pokemon go and Pokemon Go is a fairly popular game. When you have a collection of games installed on your phone, you also have a collection of problems. Users of Pokemon are frequently confronted with...

How to Limit Internet Speed Or Bandwidth Of Wi-Fi Users

How to Limit Internet Speed Or Bandwidth Of Wi-Fi Users

Introduction If you want to limit the internet speed or bandwidth of a Wi-Fi network, you'll need some key information to get into the router's admin settings. You'll need to create a new router rule to limit the internet speed for other users. To do so, open the...

How To Slipstream Windows 10 Installation

How To Slipstream Windows 10 Installation

Introduction If you are looking to slipstream Windows 10 installation, then this article is for you. In this article, you will learn How To Slipstream Windows 10 Installation using simple steps. Steps To Slipstream Windows 10 Installation Following are the steps to...

Stay Up to Date With The Latest News & Updates

Access Premium Content

Join Us to get access of our premium content.

Join Our Newsletter

Join Us to get weekly premium content, straight into your inbox.

Follow Us

We are on Social Media! Follow us.