How To Highlight/Find and Remove Duplicates in Google Sheets

If you are a regular Google Sheets user, you have probably run into a problem where you accidentally add duplicate entries into your spreadsheet. This situation can throw off the dataset you have been working so hard to put together.

How To Highlight/Find and Remove Duplicates in Google Sheets

Furthermore, 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 all too easy to miss something when there is a massive amount of 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, there are several different methods available to highlight duplicates inside of your spreadsheets. This article shows you a few different ways to find duplicates in Google Sheets and explains how to highlight or delete copies. You will also see a method that identifies unique cells instead. Here are your options.

Option #1: 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 gets the job done.

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

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

Option #2: Highlight Duplicates with Color Highlighting for Easy Removal

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

  1. Open your Google Sheets file and select the column or columns you want to sort.
  2. In the menu bar at the top, select “Format.”
  3. In the dropdown menu, select “Conditional Formatting.”
  4. Select the range you want from the new menu that appears.
  5. Under “Format Rules,” change the dropdown 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 on “Done” to save changes.

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

Note: It is best to highlight a column’s cells starting with row 1 rather than choosing cells in the middle. The formula did 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 duplications. 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 that any existing duplicates are correct, as some copies are not copies at all. They can be the same number for two different accounts, users, employees, or anything else. Once you confirm the copycat cells that you do not need, delete them or do anything you want. Finally, you can close the formatting menu and restore the standard color to your cells.

Option #3: 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 unique cells rather than duplicates. 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 and highlight the column you want to edit. This process will record the column range for the next step.
  2. After highlighting a column, click on a blank one where you want the unique entries to appear. Paste the following formula in the formula input box at the top of the document:
    =UNIQUE()
  3. Type the cell coordinates of the original column inside the parenthesis, 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 entries manually or import your data into your working spreadsheet.

Option #4: Use an Add-On to Find and Remove Duplicates in Sheets

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

Google Sheets Add-On: Remove Duplicates

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

You can move, delete, and highlight results. The tool includes two wizard setups: find and delete duplicates and find unique 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 statement is primarily for users who are continually looking for duplicates in their spreadsheets and would rather spend their time doing something else.

Option #5: Use a PivotTable to Find Duplicate Rows in Sheets

PivotTable is a convenient tool for looking more closely at data. A pivot table will not automatically delete duplicate cells or rows; it will provide a breakdown of which columns have duplicates so you can manually look at your data and see what, if anything, you need to remove.

Creating a pivot table is a little 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 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.

For more information on using Pivot Tables, see this TechJunkie tutorial on creating, editing, and refreshing Pivot Tables in Google Sheets.

  1. Select all the table data, then go to “Data->Pivot Table.”
  2. Adjust the cell range if needed, then hit “Create.”
  3. Select “Add” next to “Rows.” This step will choose the column for finding duplicates. Yes, you read that right. Select your desired column from the dropdown menu. If you lose the pivot table editor, click on a populated cell to bring it back.
  4. 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 will identify 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 then make changes to the existing table.
  7. The pivot table will change to show the new adjustments.

As previously mentioned, the pivot table method is a little more involved. Still, it gives you a specific report about your duplicate entries’ locations, which can be very useful for doing data analysis.

Final Thoughts on Identifying Duplicates in Google Sheets

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

The most common causes for duplicates are:

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

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

Leave a Reply

Your email address will not be published. Required fields are marked *

Disclaimer: Some pages on this site may include an affiliate link. This does not effect our editorial in any way.