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.
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. Common 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.
Finding and Deleting Duplicates in Google Sheets
Since you want Google Sheets to highlight your information automatically, using a particular formula tells Sheets to highlight individually specific information.
There are two ways to force Sheets to highlight copied information: the first highlights all duplicated information for manual confirmation, while the second one copies specific cells to a selected column, allowing you to check for differences and delete data what is not necessary.
If you are seeking to just remove duplicates in Google Sheets, you can use the built-in Remove Duplicates feature, which is Option #1 below.
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. Here is how to do it.
Step 1Highlight the columns you want to check for duplicate data.
Step 2In the menu at the top, select ‘Data,’ and then select ‘Remove duplicates.’
Step 3A 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.’
Step 4Sheets 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 get rid of duplicates, but sometimes you might want to review the copies before removing them.
A great way to do that is with color highlighting.
Highlight Duplicates with Color Highlighting
As far as being able to identify errors in your spreadsheets, using color highlights to spotlight any incorrect information is the best way to go.
Step 1Open your Google Sheets file and select the column or columns you want to sort.
Step 2In the menu bar at the top, select ‘Format.’
Step 3In the dropdown menu, select ‘Conditional Formatting.’
Step 4Select the range you want from the new menu that appears.
Step 5Under “Format Rules,” change the dropdown section titled ‘Format cells if…’ to ‘Custom formula is.’
Step 6Paste the following formula into the box located under the “Custom formula is” option:
Step 7Under the “Formatting style” section, select the ‘Fill color icon’ to highlight your content with a yellow (or any color of your choosing) cell background.
Step 8Click 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 chosen field (A2:B9). Once row 1 was added (A1:B9), it found all duplicates. See the two images below.
Image #1: shows missed duplicates from selecting row 2 cells as the first cells (A2 and B2):
Image #2: shows all duplicates from 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 duplicates you do not need, delete them or do anything you want with them. Finally, you can close the formatting menu, and then restore the standard color to your cells.
Copy Only Unique Cells in Google Sheets
If you prefer to automatically sort your raw data, copying unique cells instead of duplicates is useful for fast sorting and filtering. If you are sure that your information is correct and would prefer to remove the duplicates instead, try the method below.
To copy unique Google Sheets cells, do the following:
- Open the Sheets document you wish to sort and highlight the column you want to edit. This will record the column range for the next step.
- 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:
- Type the cell coordinates of the original column inside the parenthesis, such as:
- Hit ‘enter’ to move your new data to the column you designated earlier.
Once this is complete, you can either check manually or import your data into your working spreadsheet.
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 especially for users who are continually looking for duplicates in their spreadsheets and would rather spend their time doing something else.
Use a PivotTable to Find Duplicate Rows in Sheets
PivotTable is a convenient tool for looking more closely at data. A PivotTable 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, needs to be removed.
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 to 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 how to use Pivot Tables, see this TechJunkie tutorial on how to create, edit, and refresh Pivot Tables in Google Sheets.
Step 1Select all the table data, then go to ‘Data->Pivot Table.’
Step 2Adjust the cell range if needed then hit ‘Create.’
Step 3Select ‘Add’ next to “Rows.” This step will choose the column you want to find any duplicates in. Yes, you read that right. Select your chosen column from the dropdown menu.
If you lose the pivot table editor, click on a populated cell to bring it back.
Step 4Select ‘Add’ next to “Values” and choose the same column as above, but set it to summarize by ‘COUNT’ or ‘COUNTA.’ It should already be set as the default.
Step 5The new pivot table will identify duplicates as shown below.
Step 6If 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.
Step 7The pivot table will change to show the new adjustments.
As you can see, the pivot table method is a little more involved. Still, it gives you a specific report about the location of your duplicate entries, 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 is:
- Several people adding the same customer, invoice, category, item, etc.
- Data imports
- Copy/paste actions
Luckily, identifying, removing, and deleting identical cells of data is 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.