How to Remove Duplicates in Google Sheets – 3 Simple Methods

I’m here to discuss how to remove duplicates in Google Sheets. This is a common issue. When you have replicated data and want to clean the results, you’ll need to remove duplicates. Below, I’ll show you exactly how to do this.

Watch video – How to Remove Duplicates in Google Sheets

Note that, 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

I’ll discuss multiple methods for removing duplicates in Google Sheets. The first uses the UNIQUE function. Follow along with my real-world example below.

First, suppose you have a dataset as shown below:

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

The goal: Get rid of those duplicates. That will make sure Mark only shows up once and Brenda only shows up once. So here’s how you use the UNIQUE function to remove duplicates in Google Sheets.

The Syntax of the UNIQUE Function

Before we go into each example, let’s discuss the syntax of the UNIQUE function in Google Sheets. That’s important, if you want to understand why it works. It’s also absolutely essential for removing duplicates from a spreadsheet.

Here’s what the UNIQUE function looks like, broken into pieces:

=UNIQUE(range, by_column, exactly_once)

As I discussed in my article on the IF function in Google Sheets, the syntax breakdown helps us understand exactly what to input. The UNIQUE function is no exception. The interesting part here is those two final parts. Both by_column and exactly_once default to “false”. That means you don’t need to fill them out if neither is true.

The most important part of the UNIQUE function’s syntax is “range,” and it needs to be filled out every time. It determines which part of your spreadsheet to evaluate. Check out my example below to see how it works.

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.

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.

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.

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))).
• 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:

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.

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, I will demonstrate the Remove Duplicates add-on from AbleBits. Again, my screenshots show exactly what to do.

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 included several examples above and showed exactly how to use the UNIQUE function to remove extra information. I also discussed the syntax of the UNIQUE function. 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:

• How to Split Text into Columns in Google Sheets (with Examples).
• How to Quickly Transpose Data in Google Sheets.
• Remove Last Character from a String in Google Sheets (or Last N Characters)
• Using FILTER Function in Google Sheets (explained with Examples).
• How to Search in Google Sheets and Highlight the Matching Data
• 101 Google Sheets Keyboard Shortcuts.
• Sorting Data in Google Sheets.