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.
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,