Google Sheets: How to Highlight and Remove Duplicates ?

When people fill entries on your website, they sometimes fill the same form twice thus, creating trouble in sorting form entries. With a large amount of data in Google Sheets, you might have duplicates as well. Finding duplicates can be tricky. Google Sheets provide an in-built feature to get rid of duplicate entries.

How to Remove Duplicates Using Google Sheets Built-In Feature?

Firstly, select the data you would like to use. Now click on ‘Data’ and then ‘Remove Duplicates’.

You will see columns to analyze. Select the columns you would like to move forward with. After selecting, click on the ‘Remove Duplicates’.

Once you have clicked on ‘Remove Duplicates’, you will see the number of duplicates removed.

How to Remove Duplicates from Google Sheets Using a Formula?

Google Sheets allow you to remove duplicates using a formula, UNIQUE. It helps to find the authentic data and remove unnecessary duplicates.

Steps

After removing the duplicates, choose where you want your deduplicated data to live.
Enter the formula (=UNIQUE) right next to the last column in the cell.


Click on the top of the column.
Now select the column (Example B column) from which you want to remove duplicates.
You will notice that the formula entered adds the range for you.
Your formula looks like =UNIQUE(Sheet1!B: B)
After entering the formula, the unique records from the chosen column will appear.

Now copy and paste the data in Google Sheets anywhere you like.
Click on the ‘Edit’ option and then, ‘Paste Special’ > Paste Values Only. Or else. You may end up copying the formula despite the results.

How to Remove Duplicate Rows from Within a Sheet?

Removing duplicate rows from within a sheet is the same and the difference is only the number of cells selected. The same process needs to be followed except for step 3, which chooses the rows from within a sheet to remove duplicates.


To involve particular rows that are not next to each other, choose every row with the help of ctrl on Windows and command button on a Mac.

How to Remove Duplicates from Google Sheets Using an Add-on?

There are many issues with duplicates other than just deleting them like recognizing duplicates, deleting both examples of duplicated data, comparing data across sheets, ignoring a header row, copying uniques to another location, removing an entire row where there is duplicate data, and ignoring letter casing.
To install the add-on, click on ‘Add-ons’ from the Google Sheets toolbar and then select ‘Get add-ons’.
In case, you are using many accounts altogether then, install the add-on individually for each account.

How to install the add-on?

Firstly, install the add-on. Then, click on the Add-ons from the Google Sheets toolbar and choose to Get add-ons.  

Search for and pick the add-on called “Remove duplicates”. This add-on will automatically be added to your account.

Now click on “Continue” and get started.
Click on the ‘Add-ons’ and then on the ‘Remove Duplicates’.

When you select the first option ‘ Find duplicate or unique rows’. You will be able to either find duplicates/unique entries and then move further.
Select the columns/cells you want to search. The add-on will start working by auto-detecting the range. You can now select your sheet and range and then click on the ‘Next’ button.

Now choose the type of data you want to find. You can simply click on the Uniques to find duplicates. Then, click on the ‘Next’ button.

Step 3 of Find duplicate or unique rows will ask you to confirm a few details. All you have to do is select columns to search in and choose from the three options: Skip empty cells, My table has headers & Match case. After selecting your choices, click on the ‘Next’ button.

Step 4 helps choose what to do with the found values. The most useful ones are filled with color, copy to another location, and clear values or delete rows within the selection. After choosing from the options, click on the ‘Finish’ button.

How to compare columns or sheets?

If you select the second option, Remove duplicates> Compare columns or sheets.

Firstly, you will be asked to select the main sheet. If only one sheet is available, then choose your range, which can either be a complete column or another set of data.

Now, you have to choose the sheet/column/table that includes your second data set. Then, click on the ‘Next’ button.

Step three helps choose the type of data you want to find. Two options are available: Duplicates values & Unique values. Choose one of the options depending on your need. Then, click on the ‘Next’ button.

Now, you can select the columns to compare. For example, selecting Table 1 columns & Table 2 columns.

Lastly, you can choose what to do with the found values and then, click on the ‘Finish’ button.

Google Sheets is a web-based application that helps people to create, upgrade & adjust data in real-time. To know more about how to find/delete/remove functions in Google Sheets, you can have a look at our Google Sheets integrations. With Appy Pie Connect, you can easily integrate Google Sheets with 150+ popular apps in minutes without having to write even a single line of code. After the integration setup is done, Appy Pie’s Connect will keep track of the changes made in your Google Sheets and easily pass the important data on to the other you use.