How To Remove Duplicates In Google Sheets? (5 Solutions)

If you work with a huge amount of data, it’s highly likely that you have duplicate values in your worksheet.

It’s nothing to worry about because you can remove them quickly, but did you know there are many ways to do it?

You can use the “Remove Duplicates” feature under the “Data Cleanup” option.

You can also dedupe with “UNIQUE” and “QUERY” functions.

You can even use a 3rd party extension to remove all the duplicates from your sheet.

Let’s now focus on the best option to make your Google Sheet free from duplicates.

How To Delete Duplicates In Google Sheets? – 5 Working Methods That I Use

If you are wondering how to find and delete duplicates in Google Sheets, you need to know that there are at least 15 different methods to dedupe.

In this article, I’ll focus on the five ones I like the most and are suitable for newbies.

Method 1: Remove Duplicates Using The “Data Cleanup” Feature

This is the easiest and probably fastest way to eliminate duplicates in Google Sheets. This is what you need to do:

Step 1: Click any of the cells in your sheet and click on the Data option in the header menu.

Step 2: Hover your mouse overData Cleanupand click on the Remove Duplicates option.

Remove Duplicates option under data menu

Step 3: Once you get a selection menu, tick theSelect all option to check the entire sheet.

Step 4: Alternatively, you can choose any column (if you need to remove duplicates from specific columns).

The screenshot shows where to find remove duplicates button on the popup to confirm the removal

Step 5: Click on the Remove duplicates button.

Step 6: Wait until you get a summary of deduping and click on the “OK” button.

The screenshot shows a summary popup after the duplicated data is removed. It says how many duplicated rows were found and removed and how many unique ones remained.

Beneficial For: This method will be a great solution if you use Google Sheets for invoicing or bookkeeping, where you must delete duplicates in an entire worksheet.

Method 2: Get Rid Of Duplicates Using The “UNIQUE” Function

By using the “UNIQUE” function, you can easily remove duplicates from a selected data range and make it easier to work on large data sheets.

Here are the steps you need to follow to dedupe with this method:

Step 1: Select a free cell to input the function

Step 2: Type in the function command =UNIQUE(your_data_range)

In the example below, we will select all entries from B6 to G24. That’s why our formula will look like this: =UNIQUE(B6:G24)

The screenshot shows the use of the UNIQUE function where the B6:G24 data range was selected

Step 3: Once executed, you can see your entire data range but with fewer rows. The new data will start from the place where you entered your function. (In my case, it started from K6)

Remove Duplicates Using The UNIQUE Function results

Step 4: Select the new unique columns and replace your previous data range containing duplicates.

This method is particularly helpful for working with a specific data range.

It will not be a good option if you want to dedupe the whole sheet, as it will create a new data range.

TIP: If you want to work on specific data rather than an entire sheet, you can filter data in Google Sheets.

Method 3: Remove Duplicates Using The “QUERY” Function

We will now move to a more advanced function to eliminate duplicates in Google Sheets and use the QUERY function.

But first, we will name our data range, so it’s easier to reference it in our formula.

(You can read this article if you want to learn how to use named ranges in Google Sheets)

Step 1: Press the Ctrl + Abuttons (For Windows) or “Cmd + A” buttons (For Mac). (Alternatively, you can “Shift + Click” to select a specific range of data in your sheet)

Step 2: Click on the Dataoption on the header menu and find the “Named ranges” menu item.

Named Ranges option under data

Step 3: Give it a suitable name once the naming panel appears on the right toolbar. (I’m using “GSdata” in my case)

adding name ranges

Step 4: Save the named range by clicking the “Done” button.

Step 5: Now, click on an empty cell. (In my case, it is E1)

Step 6: Use the “GROUP BY” clause and create a function on that cell by using the “=QUERY(<named range>, “Select A,B,SUM(C) Group By A,B label SUM(C) ‘<the name you want to use in the last column’ “) formula.

QUERY formula

Step 8: You can now see a new data range starting from your function cell (In my case E1), but with lesser rows.

QUERY formula

My Formula: In my case, I’m using “GSdata” as the range name and “Total LatS” in the last column. So, the formula will be =QUERY(GSdata, “Select A,B,SUM(C) Group By A,B label SUM(C) 'Total LatS')

Explanation Of My Execution:

  • I have selected the first two columns (A & B) to remove duplicates from them and then give the total output value in the C column that I named “Total Output” in the formula.
  • If you want to select more columns, adjust the formula accordingly.
  • You can also use different outputs by replacing “SUM” with other functions, such as “AVERAGE” or “COUNT.”

Method 4: Eliminate Duplicates Using Pivot Tables

Removing duplicates in Google Sheets is also possible by using pivot tables. They are very flexible and quite simple to use. Here’s what you need to do:

Step 1: Select the data range you want to work with and click on the “Insert” option in the header menu.

click Pivot Table under insert in google sheets

Step 2: Click on the Pivot Table option.

Step 3: Once you get a new pivot table option, select New Sheet and click on the Create button.

create Pivot Table in new sheet

Step 4: After being redirected to a new page, navigate to the “Pivot table editor.”

Step 5: Click the “Add” button next to the Rows tag.

add Rows under pivot table editor in sheets

Step 6: Select the desired column name from the drop-down menu.

Step 7: Click on the Valueoption and choose the same or another column.

add value under pivot table in sheets

Step 8: Click on the drop-down menu below the Summarize by option.

Step 9: Select COUNT (if it contains numbers only) or COUNTA (if it contains text).

selecting COUNT under summarize by section

Step 10: Now, navigate to your created pivot table and select the second column (which has COUNT or COUNTA function).

Step 11: All those rows with a value of more than 1 have duplicates (E.G., if it is showing 2, then there are two duplicates etc).

Eliminate Duplicates Using Pivot Tables under GSheets

Step 12: Navigate to the main sheet and delete all the duplicates that you have traced in the pivot table.

Method 5: Remove Duplicates Using TheAblebits Add-on

I’m not a fan of installing addons for every operation you must perform, but the Remove Duplicates by AbleBits addon can help you with more complex examples if you are stuck.

Here are the steps you need to follow to install and use the extension:

Step 1: Open the sheet from which you want to eliminate duplicates and click on the “Extensions” option in the header menu.

Extensions add ons in google sheets

Step 2: Hover your mouse over the Add-ons option and click on the “Get add-ons” option.

Step 3: Once the “Google Workspace Marketplace” launches, type “Remove Duplicates” in the designated search field and hit enter.

Remove Duplicates addon in sheets

Step 4: Click on the “Remove Duplicates” preview; once redirected, click on the “Install” button.

Step 5: On the on-screen prompt, click the “Continue” button and select your Google account to proceed.

Step 6: Click the “Allow” button until it adds to your Google Sheet.

Step 7: Once completed, click on the “Done” button.

Step 8: Now, navigate to your main sheet and select the data range.

Step 9: Click on the “Extensions” and then on the Remove Duplicates option.

Find duplicate or unique rows under remove duplicate in sheets

Step 10: Click on the “Find duplicate or unique rows” option from the new menu.

Select the range with your table under remove duplicate add ons

Step 11: After being redirected to a step-by-step prompt, insert the data range in the “Select the range with your table” option and click the “Next” button. (Tick on the “Create a backup copy of the sheet” option to save a backup file)

SELECT Duplicates under remove duplicates in sheets

Step 12: On the second page, click on the “Duplicates” option and then on the “Next” button.

select Skip empty cells” and “Match Case” option in sheets

Step 13: Tick on the Skip empty cells” and “Match Case option, and then tap on the “Next” button. (You can also tick on the “My table has headers” option if the data range has it.)

click Delete rows within selection under remove duplicate add in sheets

Step 14: On the fourth page, tick the “Delete rows within selection” option and then on the “Finish” button.

Remove Duplicates Using TheAblebits Add-on final result

NOTE: The “Remove Duplicates” add-on by AbleBits is a paid extension that comes with a free trial. You can easily use the trial version for deleting basic duplicates. But you may need to purchase a subscription if you need advanced controls.

How To Remove Duplicates In Google Sheets Mobile App (iPhone, iPad, Android)?

There’s no option to do the “Data Cleanup” when using Google Sheets mobile app. On top of that, you cannot do much if you have iPhone or iPad.

However, there’s good news for you if you own an Android phone, as a workaround will help you. It will use conditional formatting to highlight the duplicated entries that you can delete manually.

Here are the steps to follow:

Step 1: Select the range of cells where you want to look for a duplicate.

Format button sheets app

Step 2: Tap on the Formatbutton and then on theConditional formattingoption.

Conditional formatting on sheets app
  • Step 3: Navigate to the drop-down menu under the “Format cells if…” option (under “Format rules”).
select Custom formula under conditional formatting
  • Step 4: Scroll down the drop-down menu options and tap on the “Custom formula” option.
  • Step 5: To highlight just the column having duplicate, put the “=COUNTIF($A$1:$A1,A1)>1” formula in the “Value or formula” field.
  • Step 6: To highlight both columns and rows, enter the “=COUNTIF($A$1:$A1,$A1)>1” formula.
  • Step 7: Tap on the “Done” button.
Removing Duplicates In Google Sheets Mobile App
  • Step 8: It will automatically highlight the cells containing duplicate values. You can then manually delete those.

Note: Once you format your Google Sheet on your app, you may need to download it on your desktop later to have a bigger view. So, check out our step-by-step guide for saving and downloading Google Sheets on your desktop.

Some Questions You May Have

How to remove duplicate names in Google Sheets?

Using the “Data Cleanup” option is the best way to remove duplicates in Google Sheets for texts and numbers in the data range. Just click on the “Data” option from the header menu, click on the “Data cleanup” option, and select “Remove duplicates.”

How to remove duplicate rows based on one column in Google Sheets?

You can remove duplicate rows in Google Sheets using the “Pivot Table” option in the “Insert” menu. Navigate to the “Rows” tag and click the “Add” button to choose your desired column. Now, insert “COUNT” or “COUNTA” in the “Summarize by” field to execute it.

Final Note

If you are working with a low to limited data range and want to remove all the duplicates, it is better to use the “Data validation” feature to remove those. However, if you need more customizations, such as deleting duplicates from a specific column, using the “Query” function or the pivot table is better.

You can also use different add-ons and plugins, such as Ablebits if you don’t have enough expertise or need to perform more complex operations.

Leave a Reply

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