Google Sheets: How to Highlight and Remove Duplicates

Data redundancy is a common problem in database applications like Excel and Google Sheets. During manual data entry in the worksheet, some info may get...

Swayam Prakash

Last updated on April 6, 2023

Swayam Prakash

Author

Swayam is a professional tech blogger with Masters Degree in Computer Applications and also has experience with Android development. He is a staunch admirer of Stock Android OS.Apart from tech blogging, he loves gaming, traveling and playing/teaching guitar.

Highlight and Remove Duplicates from Google Sheets

You can use any of the methods I have mentioned below.

Remove Duplicates Tool

This is very easy. Suppose I have a list of bikes and their engine displacement. If there is one bike and its displacement details repeated twice, then this tool will remove one of it

Select the dataset in the Google Sheet. You may notice the data collection has two values from each data set repeated.

Click on the option Data in the menu bar > scroll down to Remove Duplicates

Then click the checkbox Data Has Header Row

Also, click on the checkbox Select All

Finally, click on Remove Duplicates. You will see the below message of the removal of duplicate data.

If you ask me, this above method is the easiest method to get the job done in removing duplicate entries in the Google Sheet. However, there is a catch. If the duplicate entry has a spelling error or you have up put any space, then the two duplicate values will not match. Hence, the Remove Duplicates tool will not work.

Remove Duplicates Using the UNIQUE Function

Google Sheets has another easy function that can help weed out the duplicate entries in the worksheet.

Simply put the cursor on any blank cell
Type this formula =UNIQUE(A2:B10)
Press Enter

A2 and B10 are just examples. If you notice in the screenshot above, these cell numbers are the initial cell of one dataset and the final cell of the second dataset. You can accordingly mention the cell number in the formula as per your Google Sheet database.

Then after pressing enter the redundant value will be omitted and a new list will show up. This list won’t have any duplicate entries. See, how simple was that.?

Using Add-Ons to Remove Duplicates in Google Sheets

In Google sheets, you can also use add-ons to remove duplicate entries from the worksheet. First, you have to install the Add-on. It is free to download.

Open Google Sheets
In the menu bar, click on Add-ons
Then select Get Add-ons
Then in the search-box type Remove Duplicates
In the results, click on the Remove Duplicates from AbleBits
Then click Install and use the same Gmail account that you use to work on Google Sheets to give the add-on permissions.

Now, let’s see how to use the add-ons. It’s pretty straight-forward.

Select the dataset in the sheet
Then in the menu bar click on Add-On
Under that expand to Remove duplicates > click Find Duplicate or Unique Rows
Now you have to go through 4-steps.
In the 1st, click on the checkbox that says Create A Backup of the sheet and check that the range of the dataset has been mentioned correctly.
Then click Next
In the 2nd step, click on the radio button Duplicates and click Next
Next, in the 3rd step select the checkboxes Skip Empty Cells, My table has headers and Match case
In the last and 4th step select the radio button Delete Rows Within Selection.
Then click on Finish
You will see a message that the duplicate row in the dataset has been removed. That’s it.

So, that’s how you remove duplicates from Google Sheets using various functions and add-ons. All the methods are quite easy to carry out. I hope that this guide was informative.

More on Google Sheets,