How to Find and Remove Duplicates in Google Sheets

If you are a regular Google Sheets user, you have probably experienced a problem where you accidentally added duplicate entries into your spreadsheet. This situation can throw off the dataset you worked so hard to create. You may not know that the instance occurred, especially when your PC goes haywire or when you bump the trackpad on your laptop.

It is easy to miss something when there is massive data in your spreadsheet. Typical results include calculation errors and duplicate cells that are difficult to identify when searching for the source of the problem.

Fortunately, several different methods are available to highlight duplicates inside your spreadsheets.

• Use the built-in Remove Duplicates Feature.
• Use highlighting to find duplicates.
• Copy all the non-duplicated cells and move them to a new sheet.
• Use a third-party duplicate finder.
• Create a Pivot Table that counts duplicates on an individual basis.

The above processes make it easier to find those duplicate entries so that you can delete them or ignore them if necessary. Here are your options.

Use Google Sheets Remove Duplicates Feature

Whether you are trying to find duplicates in one column, two columns, or an entire worksheet, the “Remove Duplicates” feature accurately removes cells that have the same data. However, be aware that it removes all duplicates, even if they are associated.

1. Highlight the columns you want to check for duplicate data.
   
2. In the menu at the top, select “Data -> Data cleanup,” and then choose “Remove duplicates.”
   
   
3. A dialogue popup will appear. Mark the boxes next to each column in the list you want to check, or you can check off the “Select All” box and then click “Remove duplicates.”
   
4. Google Sheets displays how many copies were found and removed so that you can ensure the process worked as intended.
   

Using Google Sheets’ built-in “Remove Duplicates” feature is the most straightforward way to eliminate duplicate entries, but sometimes, you might want to review the copies before removing them. A great way to do that is with color highlighting.

Highlight Duplicates using Colors for Easy Removal

When it comes to identifying errors in your spreadsheets, using highlights to spotlight any incorrect information is another good way to go.

1. Open your “Google Sheets file.” and highlight the “column or columns” you want to edit.
   
2. In the menu bar at the top, select “Format.”
   
3. In the drop-down menu, select Conditional Formatting.
   
4. Select the “range” you want from the new menu that appears.
   
5. Under “Format rules,” change the drop-down section titled “Format cells if…” to “Custom formula is.”
   
   
   
6. Paste the following “formula” into the box located under the “Custom formula is” option:
   =countif(A:A,A1)>1.
   
7. Under the “Formatting style” section, select the “Fill color icon” to highlight your content with a yellow (or any color of your choosing) cell background.
   
8. Click “Done” to save changes, review your duplicates, and delete those you find as legitimate copies.

Your spreadsheet highlights your duplicate cells in the color you chose, so now you can scan the selection for any authentic duplicates.

Note: It is best to highlight a column’s cells starting with row 1 rather than choosing cells in the middle. The formula does not like using row 2 as the first column. It missed a couple of duplicates in the selected field (A2:B9). Once row 1 was added (A1:B9), it found all copies. See the two images below.

Image #1: shows missed duplicates when selecting row 2 cells as the first cells (A2 and B2):



Image #2: shows all duplicates while selecting row 1 cells as the first cells (A1 and B1):

Make sure any duplicates are correct, as some copies are not associated. They can be the same number for two different accounts, users, employees, etc. Once you confirm the copycat cells that you do not need, delete them. Finally, you can close the formatting menu and restore the standard color to your cells.

Copy Unique Cells in Google Sheets for Easy Removal of Duplicates

If you prefer to sort your raw data automatically, it is best to copy all non-duplicated cells. This process delivers faster sorting and filtering. If you are sure that your information is correct and would prefer to remove the duplicates instead, try the method below.

1. Open the “Sheets document” you wish to sort. Highlight the “column” you want to edit. This process records the column range for the next step.
   
2. Click on a “blank column” where you want the unique entries to appear. While highlighted (do not click a cell), paste the following formula: =UNIQUE(). It should naturally get placed in the top cell.
   
3. Type the “cell coordinates” of the original column inside the parenthesis, such as: (A3:A9).
   
4. Press “Enter” to move your new data to the earlier designated column.
   

Once complete, you can check entries manually or import your data into your working spreadsheet.

Use a Third-Party Add-On to Find and Remove Duplicates in Sheets

There are online plugins available for use with Google Sheets. You will find the add-ons in the Google Workspace Marketplace, including tools for removing duplicate entries automatically.

Remove Duplicates by Ablebits

A suitable tool known as Remove Duplicates by Ablebits allows you to quickly find duplicates throughout an entire sheet of information or up to two columns.

You can move, delete, and highlight results. The tool includes two wizard setups: find and delete duplicates and find non-duplicated cells or qualities within your document. These two options make it easy to track the information as you go.

Overall, using a wizard tool to find your information might be worth it in the long run. This option is primarily for users continually looking for duplicates in their spreadsheets and who would rather spend time doing something else.

Use a Pivot Table to Find Duplicate Rows in Sheets

A pivot table is a convenient tool for looking more closely at data. A pivot table does not automatically delete duplicate cells or rows; it shows which columns have duplicates. You can manually look at your data to see if you need to remove anything.

Creating a pivot table is more involved than the other methods shown in this article. You have to add a pivot table for each column to ensure the results are uniform and that they accurately identify duplicates.

Note: If you don’t have column names in your spreadsheet, pivot tables will not work accurately to find duplicates. Try adding a new row temporarily, and then name the columns using that new row.

Here are the steps to use a pivot table to identify duplicate cells or rows.

1. Select “all the table data,” then go to “Data -> Pivot table.”
   
2. Adjust the cell range if needed, then select “Create.”
   
3. Choose “Add,” which is next to “Rows.” This step chooses the column for finding duplicates. Yes, you read that right. Select your desired column from the drop-down menu. If you lose the pivot table editor, click on a populated cell to bring it back.
   
4. Now, select “Add” next to “Values” and choose the “same column as above,” but set it to summarize by “COUNT” or “COUNTA.” It should already be the default.
   
5. The new pivot table identifies all duplicates, as shown below.
   
6. If you want to view a different column, you can redo the steps above (to preserve your previous results) or reopen the pivot table editor and change the existing table.
   
7. The pivot table changes to show the new adjustments.
   

As previously mentioned, the pivot table method is a little more involved, especially since you need to adjust the formula if your data contains three or more identical cells. Still, it gives you a specific report about your duplicate entries’ locations, which can be helpful for data analysis. For more information on pivot tables, see the Alphr tutorial on creating, editing, and refreshing pivot tables in Google Sheets.

Overall, a duplicate cell in Google Sheets can cause problems with your data if you’re not careful, especially when sorting financial information into a helpful spreadsheet.

The most common causes for duplicates are:

• Several people added the same customer, invoice, category, item, etc.
• Data imports get added to already-present data a second time.
• Copy/paste actions added duplicate entries.

Luckily, identifying, removing, and deleting identical data cells is surprisingly easy in Google Sheets, which is positive if you are continually dealing with spreadsheets in your day-to-day workflow. If you are looking for something to sort content quicker, you can always use an add-on like “Remove Duplicates” by Ablebits to ensure your information is well-sorted and organized.

Do you have any experience, tips, or questions about using any of the methods mentioned in this article? Let us know in the comment section below.