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 recorded more than once. This leads to data duplication or redundancy. In this guide, I will show you how to remove duplicate data from Google Sheets.

I have mentioned a few tricks to highlight and get rid of similar information from the Google Sheet. There is a function that is native to Sheets. People commonly use this to find and remove repetitive information. Also, there is a dedicated in-built add-on that helps to remove duplicate data in Sheets. Every option I have mentioned is free to use.

Google Sheets: How to Highlight and Remove Duplicates

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
    Remove duplicates from google Sheets database
  • Then click the checkbox Data Has Header Row
  • Also, click on the checkbox Select All
    remove duplicates click data has header row
  • Finally, click on Remove Duplicates. You will see the below message of the removal of duplicate data.
    remove duplicates from Google Sheets

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)
    Use unique function to remove duplicates from Google Sheets
  • 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
    get add-ons
  • Then in the search-box type Remove Duplicates
  • In the results, click on the Remove Duplicates from AbleBits
    remove duplicates add-on
  • Then click Install and use the same Gmail account that you use to work on Google Sheets to give the add-on permissions.
    Install Remove duplicates add-on

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.
    create backup copy of the sheet
  • Then click Next
  • In the 2nd step, click on the radio button Duplicates and click Next
    Find duplicates in the worksheet
  • 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
    remove duplicates within selection
  • You will see a message that the duplicate row in the dataset has been removed. That’s it.
    duplicate row deleted

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,

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.