Remove duplicates within a cell google sheets

Watch Video – Remove Duplicates in Google Sheets

Google Sheets is slowly becoming the spreadsheet choice for many people. The ease with which you can collaborate in Google Sheets is way ahead of all the other spreadsheet tools.

Another reason Google Sheets is being used so much is due to the ease of use. The team behind it is constantly adding new features and functionalities that make getting things done easy and fast.

In this tutorial, I will show you a couple of ways to remove duplicates in Google Sheets.

• Delete Duplicates Using the ‘Remove Duplicates’ Tool
• Remove Duplicates using the UNIQUE function
• Remove Duplicate using an Add-on

Delete Duplicates Using the ‘Remove Duplicates’ Tool

Removing duplicates is such a common thing people do in Google Sheets, that there is now a dedicated option to quickly remove the duplicate records.

Suppose you have a dataset as shown below and you want to remove all the duplicate records from this dataset.

Below are the steps to remove duplicates from a dataset in Google Sheets:

1. Select the dataset from which you want to remove the duplicate records
2. Click the Data option in the menu
3. Click on the Remove Duplicates option
4. In the Remove Duplicates dialog box, make sure ‘Data has header row’ is selected [in case your data has the header row].
5. Make sure ‘Select All’ is selected [in the ‘Columns to Analyze’ section]
6. Click on the ‘Remove duplicates’ button.

The above steps would instantly remove all the duplicate records from the dataset and you would get the result as shown below.

When you use the ‘Remove Duplicates’ option to get rid of the duplicate records, it wouldn’t impact the data around it. This means that using it doesn’t remove the rows or delete any cells. It simply deletes the duplicate records from the cells [without disturbing cells around the dataset]

Also read: How to Highlight Duplicates in Google Sheets

Remove Duplicates using the UNIQUE function

Google Sheets also has a function that you can use to remove the duplicate values and keep the unique values only.

It’s the UNIQUE function.

Suppose you have the dataset as shown below and you want to remove all the duplicate records from this dataset:

The below formula would remove all the duplicate records and you will get all the unique ones:

=UNIQUE[A2:B17]

The above formula will give you the result starting from the cell in which you entered the formula.

The result is an array of unique records and you can not delete or edit part of this array result. Nothing will happen in case you try to delete any one cell from the result. And in case you overwrite any cell, the entire result would go away and you will see a #REF! error.

One of the limitations of using the UNIQUE function is that it would consider only those records as duplicate where the entire row content repeat. In case you only want to keep one instance of a country name and delete all the other, UNIQUE will only do it if the rest of the column values for that record are also the same.

In case your data has leading, trailing or extra spaces, the unique function will consider the records as different. In such a case, you can use the below formula:

=ArrayFormula[UNIQUE[TRIM[A2:B17]]]

Also read: How to Keep Leading Zeros in Google Sheets

Remove Duplicate using an Add-on

Google Sheets, as a rule, supports a massive library of various add-ons to take care of every conceivable issue or problem.

The duplicate removal add-ons can all be used for the same purpose. The Remove Duplicates add-on by AbleBits is one of the best add-ons to remove duplicate records from your dataset.

To use an add-in, you first need to add it to your Google Sheets document.

Below are the steps to add an add-on in your Google Sheets document:

1. Open the Google Sheets document in which you want to remove the duplicates
2. Click the ‘Add-ons’ tab
3. Click on ‘Get add-ons’
4. In the Add-ons dialog box that opens, search for ‘Remove Duplicate’ in the field in the top-right
5. In the list of add-ons that are shown, click on the ‘Remove Duplicate’ add-on
6. In the Remove Duplicates add-on screen, click on the blue Install button.
7. In the dialog box that appears, it may ask you to confirm your account by logging in to your Gmail. Enter the credentials and click on the blue ‘Allow’ button.

The above steps would add the Remove Duplicate add-on in your Google Sheets document and now you can start using it.

Below are the steps to use this add-on to delete the duplicate records in Google Sheets:

1. Select the dataset in which you have the duplicates that you want to remove.
2. Click the Add-ons option in the menu
3. Hover the cursor over the ‘Remove Duplicates’ option.
4. Click on ‘Find duplicate or unique rows’. This will open the ‘Find duplicate or unique rows’ dialog box [it may ṭake a few seconds]
5. In Step 1, make sure the correct range is selected. You can also check the box to create a backup copy of the Google sheets document.
6. In Step 2, make sure Duplicates is selected
7. In Step 3, specify whether your data has a header or not and whether you want to skip empty cells or not.
8. In Step 4, select the ‘Delete rows within selection’ option. This will remove the duplicate records.
9. Click on Finish.

The above steps would instantly remove the duplicate records and you will be left with the unique records only.

Since Google Sheets already has a way to quickly remove duplicates using an inbuilt feature, it’s best to use it if possible. The only reason I would recommend using the add-on is when you want to do a lot more than just removing duplicates.

This add-on can also perform the following:

• Color the duplicate records
• Add a column and specify the records that are duplicates
• Compare columns or sheets

You may also like the following Google Sheets tutorials:

• How to Hide Zero Values in Google Sheets
• How to Filter By Color in Google Sheets
• How to Delete Empty Rows in Google Sheets
• How to Count Cells with Specific Text in Google Sheets
• How to Search in Google Sheets
• Conditional Formatting Based on Another Cell

How do you remove duplicates from a single cell?

How do I remove rows with duplicates in one column in Google Sheets?

Is there a formula to remove duplicates in Google Sheets?

How do I find duplicates in Google Sheets in one column?