When you begin consolidating data for analysis, you may find yourself spending more time removing duplicates, especially if you have a large amount of data. Fortunately, Google Sheets has quick ways to remove duplicate entries in your spreadsheets.
Remove Duplicates Using Built-In Remove Duplicates Tool
Google Sheets has a built-in Remove Duplicates tool. You can use it to remove duplicates from either a portion of the spreadsheet or the whole spreadsheet. Select the portion of the spreadsheet that you want to be scanned by the built-in function, and then do the following:
Step 1: Click Data on the menu, and then scroll down the drop-down box to select Remove duplicates.
Step 2: A small window will appear. If the selected data has a header row, tick the checkbox before Data has header row. Make sure the checkbox before Select all is also ticked. This ensures that the removed rows are the exact copy of other rows. Click Remove duplicates.
Step 3: Another window will appear, showing the number of rows that are duplicates of other rows.
Remove Duplicates Using UNIQUE Function
But sometimes you want to keep the original entries and filter out the duplicates, copying the unique entries to another sheet. You can use the UNIQUE function. The format of the function is simple:
The function only requires you to find the bounds of the portion of the sheet. For our example, we created another sheet TX_2 that will only contain unique values from sheet TX.
To use the UNIQUE function, we go to the other sheet, select a cell where the copy begins, and then type the following:
The TX! before the bounds of the sheet refer to the sheet where the data is located. The result is as follows:
One downside of this method is that you cannot delete entries on the new sheet, as they depend on the entries from the original sheet. Additionally, when you delete entries on the old sheet, it will also be deleted on the new sheet. To solve this, you can convert the results into static values.
Remove Duplicates Using Add-Ons
One add-on you can use is the Remove Duplicates by Ablebits. It allows you more versatility in removing duplicate entries in your sheets. Once you have installed it, do the following:
Step 1: Click Add-ons on the menu, and then click Remove Duplicates, and then click Find duplicate or unique rows.
Step 2: A window will pop-up. Select the portion of the sheet you want to be filtered here. Then click Next.
Step 3: The add-on allows you to either look for duplicates or for unique occurrences. Select Duplicates then click Next.
Step 4: Make sure Skip empty cells and My table has headers are selected. You can also change which columns are scanned. When you are done, click Next.
Step 5: You can now select the action to do once duplicates were found. Since we want the duplicates removed, select Delete rows within selection. Click Finish.
Step 6: The list of removed rows are displayed after the process. Click Close.