How to Remove Duplicates in Google Sheets – 3 Simple Methods

how-to-remove-duplicates-in-google-sheets

Watch video – How to Remove Duplicates in Google Sheets

If you want to remove duplicates in Google Sheets, you need to use the UNIQUE function. This function extracts all the unique values and puts it in another place on the sheet.

This is very different than the way you can remove duplicates in MS Excel. In Excel, there is an option in the data tab to remove duplicates.

See Also: How to Find and Remove Duplicates in Excel

Method 1: Remove Duplicates in Google Sheets With the UNIQUE Function

Suppose you have a dataset as shown below:

How to Remove Duplicates in Google Sheets - Dataset

In this data, there are repetitions in names (Mark and Brenda).

How to Remove Duplicates in Google Sheets - Dataset highlighted

Now let’s see how you can use the UNIQUE function to remove duplicates in Google Sheets.

Example 1: How to Delete Duplicates in Google Sheets in a Single Column

Let’s say you only want to remove the duplicates from the Name column.

Here are the steps to do this:

  • Select a cell where you want to get the list of unique items/names.
  • Enter the following formula: =UNIQUE(A2:A10)
  • Hit Enter.

This would instantly remove the duplicates and give you a list of unique names.

 

How to Remove Duplicates in Google Sheets - Single Column

Note: If you want to remove the list, delete the first cell, or select the entire range and hit delete. Google Sheets will not let you delete individual cells (other than the first one – which would delete the entire list).

Since this is a formula, it would automatically update if you make any changes in the original list.

UNIQUE function returns a #REF! error if there is already some data in the cells that are supposed to be filled by the unique function.

If you hover the mouse over the cell with error, it will show a message stating the issue.

How to Remove Duplicates in Google Sheets - Error

Handling Leading and Trailing Spaces

Another issue you may face is when there are leading or trailing spaces. For example, if the example below, there is a trailing space in Brenda (in A10). When the UNIQUE function is used on this data set, it considers both the names as unique and returns both the names in the result.

How to Remove Duplicates in Google Sheets - Trailing Space

Here is how to handle this:

  • Go to the cell where you want to get the list of unique items/names.
  • Enter the following function: =UNIQUE(TRIM(A2:A10))
  • While in the eidt mode, press Ctrl + Shift + Enter. It would change the formula to =ArrayFormula(UNIQUE(TRIM(A2:A10))).How to Remove Duplicates in Google Sheets - Using with TRIM
  • Press Enter.

This would automatically account for any leading, trailing, or double spaces and give you the final result after removing these.

Also read: How to Highlight Duplicates In Google Sheets

Example 2: How to Eliminate Duplicates in Google Sheets From Multiple Columns

If you have more than one column, you can use the above method in Google Sheets to remove duplicate rows and get unique data.

Suppose you have the data as shown below, here’s how to duplicate values in Google Sheets from two columns:

How to Remove Duplicates in Google Sheets - Dataset highlighted

In this data, there are two duplicate rows (highlighted in orange and green).

Here are the steps to dedupe in Google Sheets for rows:

  • Select a cell where you want to get the list of unique items/names.
  • Enter the following formula: =UNIQUE(A2:B10)
  • Hit Enter.

How to Remove Duplicates in Google Sheets - Single Column

If there are leading, trailing or double spaces, use the following formula:

=ArrayFormula(UNIQUE(trim(A2:B10)))

Method 2: How to Delete Duplicates in Google Sheets Using the Remove Duplicates Tool

Would it surprise you to know that there is a built-in dedicated tool to remove duplicates in Google Sheets? Take a look at the below steps to learn how to use it.

Take this data set,

 

To remove the duplicates, we just have to follow these steps:

  1. Highlight the data set
  2. Click the Data option from the Google Sheets menu
  3. Navigate to Data>Data cleanup>Remove duplicates

  4. If your data has a header row make sure the ‘Data has header row’ checkbox is ticked
  5. Make sure the ‘select all’ checkbox is ticked
  6. Click ‘Remove duplicates’
    As you can see, the duplicates have been removed.

Method 3: Removing Duplicates in Google Sheets With a Third-Party App

There are plenty of Google spreadsheet tools that remove duplicates. But for this example, we will demonstrate the Remove Duplicates add-on from AbleBits.

If you’re unsure how to install add-ons here is a quick guide.

  1. On the Google Sheets menu, navigate to Extensions>add-ons>get add-ons

  2. In the dialog box, type the add-on you’re looking for (remove duplicates in this case)
  3. Select the add-on you want to install
  4. Click install
  5. You may need to add permissions for a new add-on. Enter your email if need be.

How to Get Rid of Duplicates in Google Sheets Using the Remove Duplicates Add-on

  1. Highlight the data set you wish to work with
  2. Navigate to Add-ons>Remove duplicates
  3. Click Find duplicate or unique rows

  4. Make sure the correct range is selected
  5. Make sure Duplicates is highlighted
  6. Decide whether you want to skip empty cells or not and specify whether your data has headers
  7. Select Delete rows within selection

  8. Click finish

As you can see in the last screenshot, this add-on has several other options rather than just deleting the duplicates. These additional options are the only real reason to use this add-on as the built-in options in Google Sheets are much more simple to use.

How to Remove Duplicates in Google Sheets but Keep Their Position

Unfortunately, this is the slowest method as most other ways will delete rows. You can use conditional formatting to highlight duplicates and then manually delete data from the rows instead of the row itself to keep the cell positions the same.

Remove Duplicates Google Sheets FAQ

Can Google Sheets Remove Duplicates?

Yes, you can use the Google Sheet delete duplicates tool. Just navigate to Data>Data cleanup>Remove duplicates.

Does Remove Duplicates Remove the Entire Row?

The remove duplicates tool in Google Sheets does delete rows.

How Do I Remove Duplicates Without Deleting Rows?

You can highlight the duplicate data with conditional formatting and then manually delete the data from the rows.

Can You Remove Duplicates Based on Two Columns?

Yes, you can use the UNIQUE function to compare two columns and delete duplicates. You just have to:

  1. Type in =UNIQUE( into an empty cell
  2. Highlight all the rows you wish to search for duplicates in
  3. Press enter

A new table will from with all the unique values in it.

How Do You Compare 2 Google Sheets Sheets and Remove Duplicates?

You can technically remove duplicates from multiple sheets with complex formulas. But, the simplest way to do so is to simply download an add-on that can do it with a few clicks. To download an add on:

  1. Click Extensions > Add-ons > Get Add-ons in the Sheets menu
  2. Type remove duplicates in the search bar
  3. Select one of the add ons and install it
  4. Launch the add-on through the Extensions menu and follow the on screen prompts to remove the duplicates

Where to From Here?

Now that you know how to remove duplicates in Google Sheets. I hope this helps you clean up your data. Do let me know your thoughts by leaving a comment.

You May Also Like the Following Google Sheets Tutorials:

Sumit
Spreadsheet Expert at Productivity Spot | Website | + posts

Google Sheets and Microsoft Excel Expert.

Popular Posts

1 thought on “How to Remove Duplicates in Google Sheets – 3 Simple Methods”

  1. Duplicate values can be annoying many of the times. As I work on excel regularly and seldom spending find few duplicate cells or vale kills a lot of time. This tutorial is handy to remove all the duplicates at once and be more productive. I love excel tricks. Thanks!

Comments are closed.