How to Remove Duplicates in Google Sheets Using Five Easy Ways

Google Sheets can be used for all types of data management tasks. It’s a spreadsheet program, which makes it easy to collaborate, share and store information in one place. This spreadsheet app allows you to create and edit spreadsheets, tables, and charts.

Duplicate rows appear when content entered by different users appears in the same row and column. If you are using a spreadsheet with thousands of rows, this can be quite time-consuming and even impossible.

Duplicate files are a common problem in Google Sheets. Thousands of people share the same spreadsheet with a duplicate file, making it challenging to manage the data. No matter how often you use Google Sheets, chances are you have to deal with duplicated data.

Google Sheets allows users to remove duplicates in a few different ways. This article will teach you five easy ways to remove duplicates in Google Sheets.

Remove duplicates in Google Sheets using PivotTables

PivotTables are one of many tools that you can use to organise and filter your data. They allow for more detailed analysis and visualisation of your data than other tools like charts and tables. When you have a lot of data in your spreadsheet, it is difficult to find duplicates. This is why it is essential to use a Pivot Table to remove duplicates.

This section will discuss removing duplicates in Google Sheets with a Pivot Table. Follow these steps to remove duplicates from your sheet:

1. Open Google Sheets.
2. Highlight your data set.
3. On the ribbon, look for Data, hover over this option and select Pivot Table from the dropdown menu.
4. This new table can be created either in a new or existing sheet. You’ll see the Pivot Table editor on your spreadsheet’s right side.
5. Select the Rows and Columns you want to check for duplications.
6. Make sure you choose the same column above, and it’s set to sum by COUNT or COUNTA.

With this, you can now quickly look for duplicate values in your original sheet and select to delete them.

Remove duplicates in Google Sheets using formulas.

Google Sheets provides several formulas that can assist you with identifying and removing duplicate data. Here are the easiest ways to remove duplicates using formulas.

How to remove duplicates in Google Sheets using the UNIQUE formula

You can use the UNIQUE formula to delete duplicate data in the selected range. Using this method compares each row of data and removes any duplicates. Since it requires only one formula and argument, you can easily remove duplicates from any range. Follow the steps below to delete duplicates using the UNIQUE formula:

1. Open Google Sheets and select a blank cell.
2. Enter the formula: =UNIQUE(x,y), which indicates the cells you need to select.
3. Click Enter.

You now have a second table which shows the duplicates that you ought to delete.

How to remove duplicates in Google Sheets using the QUERY function

You can select specific columns and delete duplicate entries using the Google Sheets QUERY function. This part of the blog covers how to remove duplicates in Google Sheets using the QUERY formula:

1. Open Google Sheets and go to the Data tab on the ribbon.
2. On the dropdown menu, select Named ranges.
3. Give a proper name to your data range and click Done.
4. You can now remove duplicate entries of the data set using the formula shown below:
   =QUERY(x:y, “Select A,B,SUM(C) Group By A,B label SUM(C) ‘name’”)
5. This will remove duplicate entries by grouping the datasets from columns A and B.

Remove duplicates in Google Sheets using Conditional Formatting

The first step in this method is to highlight your duplicates, and the second step is to remove the highlighted duplicates, allowing you to see your duplicate before it is removed. To conduct conditional formatting to remove duplicates in your Google Sheets, follow the steps below:

1. Open your Google Sheet and select the data from which you wish to remove duplicates.
2. Under the Format tab, select Conditional Formatting on the dropdown menu.
3. Under the Format cells option, select Custom formula.
4. Enter the following formula: =COUNTIF($B$2:$B2,B2)>1. This formula checks for duplicates in the selected column for you.
5. Set your Formatting style to highlight your duplicate data.
6. Delete duplicates is optional but is now easier to do. Click on the Filter menu option and select the data you want to sort.
7. Select the Filter icon and the column you want to sort by colour.
8. From the dropdown menu, select Sort by colour, then Fill Color and choose the fill colour you want to appear on top.
9. Highlight the duplicates and select Delete.

Remove duplicates with the Remove Duplicates tool.

Google Sheets’ Remove duplicates tool is the most robust and practical method of removing duplicates. Listed below are guidelines for removing duplicates using the remove duplicate tool.

1. Open your Google Sheet and select the data you wish to remove.
2. Select Data from the menu bar.
3. Select Remove duplicate from the menu options.
4. Duplicated columns can be checked by selecting the columns you want to check. If the rows are entirely identical, you may wish to remove duplicates, or you may wish to remove duplicates based on the address or name of a specific column.

Remove duplicates in Google Sheets using Apps Script.

The Apps Script program can remove duplicates from datasets particular to the Sheet and data range. It works as follows:

1. Get the values from the data range in Sheet1 using Apps Script.
2. Turn the array rows into strings (blocks of text) for comparison.
3. Filter out any duplicate rows.
4. Check whether a de-duplicate sheet exists.
5. If it does, clear out the old data and paste it into the new de-duplicated data.
6. If it does not exist, create a new sheet and paste it into the new de-duplicated data.
7. Add a custom menu to run from the Google Sheets.

This application lets you build minimal mistakes, and it only takes 10-15 minutes to build custom scripts that will remove duplicates in Google Sheets.

Conclusion

Working with large amounts of data in a spreadsheet is bound to have duplicate errors. Fortunately, Google Sheets allow several methods to delete these duplicates. Listed below are the easy ways to delete duplicates in Google Sheets:

• PivotTables can be used to remove duplicates in Google Sheets quickly.
• The UNIQUE formula can delete duplicates from any range.
• QUERY function deletes duplicates in multiple columns.
• Conditional Formatting highlights and removes the duplicates in your data.
• The most practical method of removing duplicates is using the Remove Duplicate Tool in Google Sheets.
• App script can be customised to delete duplicates.

FAQ

How do I quickly delete duplicates in Google Sheets?

The steps below will show you how to quickly remove duplicates on your spreadsheet:

1. Click on “File” then “Make a copy.” This will create a new sheet that has no duplicates.
2. Select all the cells on the sheet with duplicate information (hold shift and click on each cell), then press CMD-D or select “Delete Duplicate Rows” from the Data menu when selecting cells with duplicate information.

Can Google Sheets find duplicates?

Google Sheets allows you to search for duplicates in your spreadsheet.

How can you automatically remove duplicate cells from your Data?

By using the Data class methods, it is possible to remove duplicate cells from your Data. The methods are called setAllUnique() and remove duplicates().