Let’s say you have to go through a large email list and sort out the necessary email addresses that might prove to be the warm leads you are searching for your business. However, there’s a huge possibility that people might have filled your email forms twice, meaning you can have duplicate addresses that might enlarge your email list unnecessarily. This brings us to the question of how to remove duplicates in Google Sheets.

Learn How To Remove Duplicates In Google Sheets and Ensure Error-Free Data

Having duplicate data increases the risk of errors in it. If users tend to make decisions based on the conclusions derived from such data, it might prove to be a costly mistake while pursuing results. Secondly, it can also mess up with your documentation, workflow, and data analysis.

Google Sheets offers in-built tools for removing the duplicates. Also, there are methods that can help you do the same. Without further ado, let’s have a look at these solutions and make sure our data remains the same.

1. How to Remove duplicates in Google Sheets Using the In-built Tool

The built-in feature is the easiest way to learn how to remove duplicates in Google Sheets.

To start with, navigate to the Data tab and click on the Remove duplicates option.

how to remove duplicates in google sheets
Click on Remove duplicates

In the dialog box that pops up, select which ranges you would want to work with.

Once you have selected the necessary options, click on the Remove duplicates option.

click on remove duplicates
Select the preferences and click

You will get to know how many duplicate entries were removed once you complete this process. In this way, you can have your dataset de-duplicated with just a few simple clicks.

2. Use the UNIQUE formula

You can also learn how to remove duplicates in Google Sheets using the UNIQUE formula. The original data can be kept intact while removing the duplicates. It allows you to find the records that are unique and get rid of the rest. In this method, the rows of your data are compared with each other and remove the rows that are duplicates.

Remove Duplicates From A Single Column

Suppose if you want to remove duplicate emails from a single column, the UNIQUE formula can be used to a significant effect in achieving the desired result.

First and foremost, you have to decide where you want your de-duplicated data to be stored. You can either create a new sheet for the same. If you want to do the sorting on the same sheet, make sure there is enough space between the two types of data.

Now, navigate to your primary data and click on the column in which you want to remove the duplicates. In the column next to which your data ends, type UNIQUE, and select the formula.

Enter UNIQUE formula to remove duplicates in google sheets
Enter UNIQUE formula in a column

Select the column which you want to de-duplicate by clicking on the column letter at the very top. In this case, we have selected column B. You will notice that the UNIQUE formula has automatically added the range after you select the column.

Now, you have to end the parentheses, ), to complete the formula. Your formula will look like this.

= UNIQUE (SHEET 1!B: B)

Select the column and enter the given formula
Select the column and enter the given formula

Now, press the Enter key. The unique records from the selected column will appear, starting from the cell in which you entered the formula.

Press Enter to see the results
Press Enter to see the results

If you wish to copy this data on any other sheet, make sure you copy the column and use the Paste special option to paste the data. Go to the edit tab and select the Paste special option, and then choose Paste values only.

Paste values only in a separate column to remove duplicates in google sheets
Paste values only in a separate column

If you don’t follow the above method, you will end up copying only the formula instead of results. This task might appear difficult for beginners, but once you get the hang of it, there’s no easier way to de-duplicate your entries in Google Sheets.

Delete Duplicates From A Row

The process of removing duplicate rows is similar to that of columns, albeit with just a small change. All you have to do is select the rows you want to de-duplicate, instead of selecting columns.

In this case, you have to change the formula as follows:

=UNIQUE(A1:B16)

Using this formula will help you select the entire table, as shown in the figure below.

Enter the formula given above in a separate column
Enter the formula given above in a separate column

Press the Enter key, and you will see that the de-duplicated results appear in the column.

Use UNIQUE formula to remove duplicates in google sheets
Press Enter key to view the results

You have to copy these results by using the same Paste Special method we learned about in the first part. In this manner, you can remove duplicates from both rows and columns by learning the appropriate use of the UNIQUE formula.

3. How To Remove Duplicates Using Pivot Tables

Pivot tables are essential tools when it comes to de-duplicating your datasets. You can learn how to remove duplicates in Google Sheets using Pivot tables if you are decluttering your data for the very first time.

To begin with, highlight your entire dataset and click on the Data tab. Then, select the Pivot table option. The Pivot table editor will appear on the screen.

remove duplicates in google sheets with pivot table
Select the Pivot table option from the Data tab

Under ROWS, choose the column you want to check for duplicates (e.g. invoice number). Then in VALUES, choose another column (I often use the same one) and make sure it’s set to summarize by COUNT or COUNTA. Now, the table will look like this. You can see that the duplicate values for specific entries will have a count of greater than 1.

Select the preferences in the pivot table editor
Select the preferences in the editor

From here, you can decide on how to proceed with deleting the duplicates. This method is an excellent way to find duplicates and investigate their causes.

4. How To Delete Duplicates In Google Sheets Using Conditional Formatting

If you are looking for an advanced solution to how to remove duplicates in Google Sheets, you can try conditional formatting. To remove duplicates in Google Sheets using this method, a user needs to carry out two steps. First, he or she has to highlight the duplicates and then delete them in the next step. To learn this method, let’s look at how to highlight the duplicates first.

How To Highlight Duplicates In Google Sheets With COUNTIF Command

This method will teach you how to highlight duplicates in Google sheets. But, before we proceed, create a new column next to the data column you want to check for duplicates. Then, type this formula in the B2 cell to highlight the duplicates in column A.

=COUNTIF(A$2:A2, A2)>1

Use COUNTIF formula to remove duplicates in google sheets
Use COUNTIF formula

Here, A$2:A2 is the range of the data you have selected. Now, whenever a value shows up in the sheet for the first time, the result of the formula will be 1, meaning FALSE.

When it shows twice or more, the result of the formula will be 0, meaning TRUE.

View the results and remove duplicates in google sheets
View the results

Now, all you have to do is highlight the rows with the TRUE value and remove them.

How To Remove Duplicates Using IF Formulas

Using the IF formulas might appear like a tricky task at first, but once you get the hold of it, you will be able to remove duplicates in Google sheets in the blink of an eye. The basic concept of this method is to compare rows to each other and assign values to both unique and duplicate entries.

For this task, we will assign 0 to a unique value and 1 to a duplicate value. Suppose row B has the same value as row A, it will be assigned the value 1.

To begin with, add a unique order column; It lets you sort the data by the column of your choice and then return to the original column. A unique order column is one in which you want every entry to be unique. In our case, the Invoice Number column is our unique order column.

Now, select the unique order column and sort it from A-Z. Doing so will bring all the duplicate entries right next to their unique counterparts. This prior organization of data will help you delete the duplicates once they are identified and marked.

Sort data in ascending order
Sort data in ascending order

Now, add two new columns next to the unique order column. Apply color coding so that you can distinguish the newly added columns. Name one of the columns as ‘Duplicates’ and the other as ‘Duplicate Groups.’

Add the columns to the right side of your unique order column
Add the columns to the right side of your unique order column

Now, type the basic formula for checking duplicates in cell B2:

=IF(A4=A3,1,0)

To have a result like this, make sure that you select a pair of the same entries and select the cell in front of the duplicate entry for entering this formula. We have to assign 0 to a unique entry and 1 to a duplicate entry, but if you don’t select the correct cell as mentioned, your results could be reversed.

Enter the formula given above and press Enter
Enter the formula given above and press Enter

Then, in the cell C3 under the column ‘Group Duplicates’, add this formula:

=IF(OR(B3=1, B4=1),1, 0)

Also, make sure you select the correct cell for entering the formula as shown in the figure.

Enter the formula given above and press Enter
Enter the ‘If or’ formula and press Enter

The Duplicate Groups formula identifies the first entry and the subsequent duplicates. This step can also help you identify the source or the reason for duplicate entries.

Now, highlight the data (consisting of 0’s and 1’s) in your two duplicate columns. All the rows that have two 1s in both the Duplicates and Duplicate Groups cells are your duplicate entries which need to be deleted.

remove duplicates google sheets
Delete all the rows that have two 1s

This process can be used to a great effect on email marketing campaigns. If you want to make sure that you don’t send a promotional email to a customer more than once, you can remove the duplicates using this method.

5. Remove Duplicates In Google Sheets Using Add-ons

Using an add-on is the easiest and the fastest method to remove duplicates in Google Sheets. Added to that, you need not worry about manual errors in deleting duplicates as the add-on takes care of it far too well.

To begin with, click on the Add-ons tab and select Get Add-ons.

Get Add-ons to remove duplicates in google sheets
Get Add-ons

Now, search for Remove duplicates and select the one that is offered by Ablebits.com. The add-on is free for 30 days but, after that, users will have to shell out cash for its services.

Search and install Remove Duplicates add-on
Search and install Remove Duplicates add-on

Once you have installed the add-on, go back to the Add-ons tab and click on Remove duplicates. In the drop-down list, select Find duplicates or uniques. This will help you locate the duplicates and unique ones so that you can take action on them.

Find duplicate or unique rows using the add-on
Find duplicate or unique rows using the add-on

Now, a dialog box will appear on your screen. Select the sheet and range in which you want to find duplicates. This is the first step in the entire process. We have chosen the range A1: B26 for this step. Click on Next after you have selected your range.

Select the range of data and click on Next
Select the range of data and click on Next

In the second step, decide the kind of values you want to find. We have selected ‘Uniques’ in this step. Once done with this step, click on Next.

Select the type of data and click on Next
Select the type of data and click on Next

In the third step, you have to confirm a few options as well as select the columns in which you want to find the duplicates. After you have selected your preferences, click on  Next.

Select the columns to search in and click Next
Select the columns to search in and click Next

In the next step, you will be faced with a series of options on what to do with the values you have highlighted in the previous steps. You can fill these value cells with color, copy them to a different location, or delete them as per your preferences. Once you have done it, select Finish and end the process.

Select your preferences and click on Finish
Select your preferences and click on Finish

This is how the final result looks like when you have correctly executed all the mentioned steps in this process.

View the final results
Check the results

Using add-ons to get rid of duplicates is one of the easiest methods to use for beginners. The only problem you might encounter here is after 30 days when the trial of the add-on comes to an end. Then, you can either go for a subscription or any of the remaining methods to remove duplicates.

6. Use the Google Script Editor To Delete Duplicates

When we talk about the Google Script Editor, many might find this task tedious to execute, given that it involves writing a program. Let’s learn how to remove duplicates in Google Sheets using the Script Editor in an easy way.

To start with, go to the Tools menu and select the Script Editor option. This will open up the script editor.

Go to the Tools tab and open the Script editor
Go to the Tools tab and open the Script editor

Now, in the window that appears, delete the empty myFunction () command and paste this program given below:

//Removes duplicate rows from the current sheet.

function removeDuplicates() {

//Get current active Spreadsheet

var sheet = SpreadsheetApp.getActiveSheet();

//Get all values from the spreadsheet’s rows

var data = sheet.getDataRange().getValues();

//Create an array for non-duplicates

var newData = [];

//Iterate through a row’s cells

for (var i in data) {

var row = data[i];

var duplicate = false;

for (var j in newData) {

if (row.join() == newData[j].join()) {

duplicate = true;

}

}

//If not a duplicate, put in newData array

if (!duplicate) {

newData.push(row);

}

}

//Delete the old Sheet and insert the newData array

sheet.clearContents();

sheet.getRange(1, 1, newData.length, newData[0].length).setValues(newData);

}

Once you have pasted the program in the window, it should look like this. Click on the Save button to save it.

Paste the program in the Script editor window
Paste the program in the Script editor window

You will see a dialog box asking you the name for the program. Give it an appropriate name and select OK.

Enter the project name to remove duplicates in google sheets
Enter the project name and click on OK

Now, click on the Run button in the menu bar to run the program.

Click on the Run button to run the program
Run the program

This is how the result will look like when you run the program. The duplicates will be deleted.

View the results after running the program
View the results

Using this program is also a beneficial thing since you can run it over and over for different sheets and remove duplicates from them.

Conclusion

When it comes to managing large datasheets for both professional or personal purposes, users need to make sure they don’t contain duplicate entries. Having duplicate entries can cause users to misinterpret information, which might also lead to erroneous decision-making.

Fortunately, there are several answers on how to remove duplicates in Google Sheets. Users can clear duplicates using add-ons, conditional formatting, or by using a bunch of formulas. These methods ensure that your data is de-duplicated once you execute them.

It must also be noted that you need to save a particular dataset if you feel it is important; after making changes to it, you can retrieve the original data from the previous versions in Google Sheets. The choice of method entirely depends on the convenience of the users.