How to Remove Duplicates in Google Sheets – 3 Simple Methods

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

Watch video – 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:

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: How to Eliminate Duplicates in Google Sheets With a Third-Party App

There are a number of add ons for Google sheets that can take care of this for you. 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.

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.

Where to From Here?

Now that you know how to remove duplicates in Googe 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:

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.