How to Find and Remove Duplicates in Google Sheets

If you're a regular Google Sheets user, you've probably encountered a problem when you accidentally added duplicate entries in your spreadsheet. This situation can disrupt the data set you have worked so hard to gather. You may not know the instance has occurred, especially when your PC goes haywire or when you hit your laptop's trackpad.

Either way, it's all too easy to miss something when there's a huge amount of data in your spreadsheet. Typical results include calculation errors and duplicate cells that are difficult to identify when tracking down the source of the problem.

Fortunately, there are several different methods to highlight duplicates in your spreadsheets.

• Use the built-in duplicate removal feature.
• Use highlighting to find duplicates.
• Copy single 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 these duplicate entries so that you can delete or ignore them if they match but are not duplicates. Here are your options.

Use the Google Sheets Remove Duplicate Feature

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

1. Highlight the columns for which you want to find duplicate data.

   

2. From the top menu, select “Data,” then choose “Remove Duplicates. »

   

   
   
3. A dialog window will appear. Check the boxes next to each column in the list you want to check, or you can check " Select all, " then click “Remove duplicates.” 

   

4. Google Sheets shows the number of copies found and deleted so you can make sure the process worked as expected.

   

Using Google Sheets' built-in "Find and Remove Duplicates" feature is the easiest way to eliminate duplicates, but sometimes you may want to review copies before deleting them. A great way to do this is to highlight colors.

Highlight duplicates using colors for easy deletion

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

1. Open your Google Sheets file and select the column(s) you want to sort.

   

2. From the menu bar at the top, select « Format. »
   

   

3. From the drop-down menu, select " Conditional formatting. »
   

   

4. Select the desired track from the new menu that appears.

   

   

5. Under "Format Rules", change the drop-down section titled “Format cells if…” à “The personalized formula is. »

   

   
   
6. Paste the following formula in the box below the "Custom formula is" option:

   =countif(A:A,A1)>1.
   

   
   
7. In the “Formatting Style” section, select the “Fill color icon” to highlight your content with a yellow (or color of your choice) cell background.

   

8. Click on " I finished " to save the changes.

Your spreadsheet will now highlight your duplicate cells in the color you chose, and you can scan the selection for any duplicates.

Notes: It is better to highlight cells in a column starting with row 1 than to select cells in the middle. The formula doesn't like to use row 2 as the first column. Some duplicates are missing in the selected field (A2:B9). Once row 1 was added (A1:B9), all duplicates were detected. See the two images below.

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

Image n°2: shows all the duplicates by selecting the cells of row 1 as the first cells (A1 and B1):

Make sure all existing duplicates are correct, as some copies aren't copies at all. They can be the same number for two different accounts, users, employees or anything else. Once you've confirmed the copy cells you don't need, delete them. Finally, you can close the formatting menu and restore your cells to their standard color.

Copy Single Cells in Google Sheets to Easily Remove Duplicates

If you prefer to sort your raw data automatically, it is better to copy all unique cells rather than duplicates. This process offers faster sorting and filtering. If you are sure that your information is correct and you prefer to remove duplicates instead, try the method below.

1. Open the Sheets document you want to sort and highlight the column you want to change. This process will save the column range for the next step.

   

2. After highlighting a column, click on an empty column where you want unique entries to appear. Paste the following formula into the formula input box at the top of the document: =UNIQUE()

   

3. Enter the original column cell coordinates in parentheses, such as: (A3:A9).

   

4. Hit " Enter " to move your new data to the column you designated earlier.

   

Once this is complete, you can either check the 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'll find the add-ons in the Google Workspace Marketplace, including tools to automatically remove duplicate entries.

Remove Duplicates by Ablebits

As a suitable tool known as Remove Duplicates by Ablebits, you can quickly find duplicates in an entire sheet of information or by searching up to two columns at a time.

You can move, delete and highlight results. The tool includes two wizard configurations: find and remove duplicates and find unique cells or qualities in your document. Both of these options make it easy to track information as it happens.

Overall, using an assistant to find your information can be worth it in the long run. This statement is mainly for users who continually find duplicates in their spreadsheets and prefer to spend their time doing other things.

Use a PivotTable to find duplicate rows in sheets

A pivot table is a handy tool for taking a closer look at data. A pivot table does not automatically delete duplicate cells or rows; it provides a breakdown of columns that have duplicates so you can manually review your data and see what, if anything, you need to remove.

Creating a pivot table is a little more complex than the other methods presented in this article. You should add a PivotTable for each column to ensure consistent results and accurately identify duplicates.

Notes: If you don't have column names in your spreadsheet, PivotTables won't work accurately to find duplicates. Try temporarily adding a new line, then name the columns using that new line.

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

1. Select all the data in the table, then go to “Data -> Pivot Table. »

   

2. Adjust the cell range if necessary, then press " To create. »

   

3. Select " Add " next to "Lines". This step will choose the column to find the duplicates. Yes, you read that right. Select the desired column from the drop-down menu. If you lose the pivot table editor, click on a filled cell to bring it back.

   

4. Select " Add " next to "Values" and choose the same column as above, but set it to be summarized by "COUNT" or "COUNTA". This should already be the default.

   

5. The new pivot table will identify duplicates as shown below.

   

6. Si tu want to see another column, you can redo the steps above (to keep your previous results) or reopen the PivotTable Editor and then edit the existing table.

   

7. The pivot table will change to show the new adjustments.

   

As mentioned earlier, the pivot table method is a bit more complex. Nevertheless, it provides you with a specific report on where your duplicate entries are located, which can be very helpful in analyzing the data. For more information on using PivotTables, check out TechJunkie's tutorial on Creating, Editing, and Refreshing PivotTables in Google Sheets.

Overall, a duplicate cell in Google Sheets can cause problems with your data if you're not careful, especially when trying to sort through financial information in a useful spreadsheet.

The most common causes of duplicates are:

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

Fortunately, identifying, deleting, and deleting cells of identical data is surprisingly easy in Google Sheets, which is a positive if you continually use spreadsheets in your daily workflow. If you're looking for something to better sort your content, you can always use an add-on like Remove Duplicates by Ablebits to make sure your information is well sorted and organized.

pack

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