How To Remove Duplicates In Google Sheets

What Is A Google Sheet?

Google Sheets is spreadsheet software that is included in Google’s free, web-based Google Docs Editors suite. Google Sites, Google Slides, Google Drawings, Google Docs, Google Forms, and Google Keep are all part of the program.

Google Sheets is available on many platforms such as a web application, a smartphone app for Android, iOS, Windows, BlackBerry, and a Chrome OS desktop application. The software supports Microsoft Excel file formats.

Users can build and edit files online while interacting in real-time with other users. The user’s edits are registered, and a revision history shows the changes.

How To Remove Duplicates In Google Sheets

When some new data set is imported into a spreadsheet, duplicates in google sheets can be created or through a simple human data entry error. To remove duplicates in google, allow the application to delete copies rather than manually searching each line for information added multiple times.

To remove duplicates in Google Sheets, you can use a built-in feature, a function, or a third-party add-in. Continue reading to find out how to remove duplicates in Google Sheets.

1. Built-in feature for “How to Remove Duplicates in Google Sheets.”

• The built-in feature provides the basic functionality to remove duplicates in google cells to remove duplicates. To do so, choose the data you want to keep and then go to “Data” then “Remove Duplicates tool/remove duplicates.”

• Select Data, then “Remove Duplicates” in google sheet.
• You’ll then be able to choose if the data has a header row and confirm the set you’d like to work on

• If you want to remove duplicates, it is removed using the following options.
• After you’ve made your choices, use the remove duplicates to finish the job. It can tell you how many copies have been removed.

2. Delete Duplicates in Google Sheets Using Conditional Formatting

You could come across large spreadsheets in Google Sheets where you have to deal with many duplicate entries. Conditional formatting makes highlighting and deleting duplicates a breeze. We’ll show you how to locate and delete duplicate entries in Google Sheets in this guide.

The Conditional Formatting feature isn’t designed to get rid of duplicates. You can use this feature to make them stand out. As a result, you’ll need to complete two measures to remove it:

• It will help you find duplicates and highlight them.
• Remove any duplicates.

Let’s learn how to highlight duplicates in a single column before learning how to delete duplicate entries from Google Sheets. Follow the steps below.

• Pick a column in your spreadsheet in Google Sheets.
• Select column A then goes to “Format” and then “Conditional formatting.”

• Let’s say you want to make a single column stand out by highlighting duplicates. Choose a column range, such as B1:B.

• Pick “formula is” from the drop-down list under Format guidelines.

=countif(A1:A, A1)>1 is the value for the custom formula.

• Formatting styles are located under Format rules and allow you to change the color of highlighted duplicates. To do so, click the Fill color icon and choose your favorite hue.
• When you’re done, click Done to see the duplicates in a single column highlighted.
• If you need to do it for column C, the formula becomes =countif(C1:C, C1)>1, and so on for the rest of the columns.

Highlight duplicates in multiple columns

• Consider the following scenario: If you want to remove duplicates in two columns or multiple columns:
  • TOPICS (A1:A), WORDS (B1:B), PRICE(C1:C), PAYMENT STATUS (D1:D). 
• Select the data range that will include the columns you want to delete. As a formatting law, choose “Custom formula is” and use the following formula COUNTIF:

=(COUNTIF($A$1:$A,$A1)>1)*

(COUNTIF($B$1:$B,$B1)>1)* 

(COUNTIF($C$1:$C,$C1)>1)* 

(COUNTIF($D$1:$D,$D1)>

• The values in the formulas correspond to the columns you choose.
• Duplicates will be highlighted on different conditions:

3. Delete The Duplicates in Google Sheets Using an Add-on

As a rule, Google Sheets comes with an extensive library of different add-ons to address any issue or concern. Both of the duplicate elimination add-ons serve the same function. 

One of the best add-ons for removing duplicate records from your dataset is AbleBits’ Delete Duplicates.

You must first add an add-in to your Google Sheets document before you can use it.

The following functions are also possible with this add-on:

• Fill in the blanks with color.
• Create a column for duplicate records and fill it in.
• Compare and contrast columns or sheets.

The steps to add an add-on to your Google Sheets document are as follows:

• Open the Google Sheets document where the duplicates should be removed.
• Go to the ‘Add-ons’ page.
• In the menu, select the Add-ons option.
• Select “Get Add-ons” from the drop-down menu.

• Check for ‘Remove Duplicate’ in the top-right area of the Add-ons dialogue box that appears.
• In the Add on the search bar, type Remove Duplicates.
• Select the ‘Remove Duplicate’ add-on from the list of available add-ons.
• In the search results, click the Remove Duplicates add-on.

• Click the blue install button on the Remove Duplicates add-on screen.
• Select the BLUE install option.

• It can ask you to confirm your account by logging into your Gmail account in the dialogue box that appears. Click the blue ‘Allow’ button after entering the credentials.

• Enable the add-on to be added to Google Sheets.
• The Remove Duplicate rows add-on should now be installed in your Google Sheets document, and you can begin using it.

The steps for using this add-on to remove duplicate records in Google Sheets are as follows:

• Select the dataset containing the duplicates you want to get rid of.
• In the menu, select the Add-ons option.
• Select Add-ons from the drop-down menu.
• Hover your mouse over the option to “Remove Duplicates.”
• Select ‘Find duplicate or unique rows’ from the drop-down menu. It will bring up the ‘Find duplicate or unique rows’ dialogue box (which can take a few seconds to load).

• Select Find Duplicate or Exclusive Rows from the drop-down menu.

Make sure the correct range is selected in Step 1. You may also choose to make a backup copy of the Google Sheets document by checking the box.

Select the range from which duplicates should be removed.

• Make sure Duplicates were selected in Step 2.
• Select the Duplicates option.

• Choose whether efficiently your data has a header and whether or not you want to skip empty cells.

• Select the ‘Delete rows inside range’ choice in Step 4. The duplicate records would be removed as a result of this.

• Inside the selection, pick Delete rows.

• Finish by pressing the Finish button.
• The steps above will automatically delete cells using the remove duplicates add-on and leave you with only unique records.

Since Google Sheets already has a built-in feature for efficiently removing duplicates, it’s best to use it if at all possible. The only time I’d consider using the add-on is if you need to do more than just remove duplicates.

4. Delete Duplicates using the UNIQUE function

In Google Sheets, the UNIQUE feature returns unique rows from the given source set, discarding duplicates. The UNIQUE function returns rows in the source range in the order in which they first appear. You can access and compare both sets of data using this approach before removing the original duplicates.

• This approach removes duplicates from the data set you to specify.
• When the unique function tries to identify duplicates, it takes into account all of the columns in your data range. If you put it another way, a unique function compares each row of data and eliminates any duplicates (identical to others across the whole row).
• It’s simple to use because it only needs one formula with one argument: the range you want to de-duplicate (remove duplicates from).

• Here we have provided an example of how to use the UNIQUE feature. The feature is located in cell I1 and searches for duplicates in the A1:A15 data set.
• The result is as follows:

• Since the duplicate rows have been eliminated, the table on the right has fewer rows.

5. Delete Duplicates in Google Sheets using Pivot Tables

• For exploratory data processing, pivot tables are handy.
• In Google Sheets, pivot tables are an excellent method for searching for duplicates. They’re incredibly scalable and quick to use, so they’re a perfect place to start if you’re unsure if your data has any copies.
• Build a Pivot Table with your dataset highlighted (under the Data menu).

• The Pivot Table editor appears in a new tab or same tab.

• Select the column you want to look for duplicates under ROWS (e.g., program invoice number). Then, in VALUES, select another column (I usually use the same one) and make sure it’s set to COUNT or COUNTA (if your column contains text), as shown below:

• Using a Pivot Table in Google Sheets, you can highlight duplicates.
• Using a Pivot Table in Google Sheets, highlight duplicates.
• Duplicate values will have a count greater than one, as you can see.

• You can then look up the duplicate values in your original dataset and determine what to do next.
• As you can see, this approach works well when you’re looking for duplicates and want to examine them further.

6. Delete Duplicates In Google Sheets Using Apps Script

How To Remove Duplicates In Google Sheets. 

Creating a small script file that removes duplicate rows from your datasets is relatively easy. The benefit of writing an Apps Script program is that you can run it several times, such as adding new data. This software eliminates duplicates from Sheet 1’s dataset. It’s unique to the Sheet and data set, but it’s simple to build and alter.

• In Google Docs, create a new spreadsheet or use an existing one with duplicates.
• Add a few rows of data (for example, a list of contacts, parts inventory, etc.) and duplicate any of them if the spreadsheet is blank.
• Select Script Editor from the Tools menu.

• Now, search for any duplicate removing script on google or other known websites.
• Copy the respective script to the script editor.
• Click Run after selecting the feature, remove duplicates from the function dropdown list.
• Take a look at the spreadsheet you’ve developed. There should no longer be any duplications.

Conclusion 

After reading this article, “how to remove duplicates in google sheets,” you will perform several functions like Remove any rows that are duplicates. Check to see if there is a de-duplicate sheet. If this is the case, delete the old data and replace it with the newly de-duplicated data. 

Do check out our article on How to Set Up and Use Google Docs Offline

Frequently Asked Questions

Recommended Articles