How To Highlight Duplicates in Google Sheets
If you’re a regular Google Sheets user, you’ve probably run into a problem where you accidentally add duplicate entries to your spreadsheet. This can throw off the dataset you’ve been working so hard to put together.
Since spreadsheets can be lengthy documents, it becomes difficult to spot and remove duplicates, leading to calculation errors in your work without an obvious source for where the problem is coming from.
Fortunately, we’ve found several different methods to highlight duplicates inside of Google Sheets. In this article, I’m going to show you a few different ways to find duplicates in Google Sheets, then highlight or delete copies.
Finding and Highlighting Duplicates in Google Sheets
Since we want Google Sheets to highlight our information for us automatically, we’ll be using a formula to tell Sheets to bring forward and highlight specific, unique information.
There are actually two ways to force Sheets to highlight copied information: the first highlights all duplicated information for manual confirmation, while the second will copy unique cells to a selected column, allowing you to check for differences and delete where necessary.
If you are just seeking to find duplicates then remove them, you can use the built-in Remove Duplicates feature in Sheets.
Use the Remove Duplicates Feature
Whether you are trying to find duplicates in one column, two columns, or a whole worksheet, the Remove Duplicates feature can get the job done.
- Highlight the columns you want to check for duplicate data.
- Select Remove Duplicates from the Data drop-down menu.
- A dialogue box will appear. Check “Data has header row” if there’s a row with headers for the columns. Make sure the checkbox is selected next to columns you want to check or choose “Select All.”
- Click Remove Duplicates.
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 get rid of duplicates, but sometimes you might want to review the duplicates 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 goes, using color highlights to spotlight any information that has been inputted incorrectly is the best way to go.
- Open your Sheets file.
- Select the column or columns you want to sort.
- Select Format from the toolbar.
- Select Conditional Formatting.
- Select the range you from the new menu that appears.
- Change Format cells if to Custom formula is in the drop-down menu and paste the following formula into the box below the menu:
- Change the formatting style to highlight your content with a yellow (or any color of your choosing) cell background.
- Click done.
Your spreadsheet will now highlight your duplicate cells in red, and you can scan the selection for any duplicates.
Make sure that any existing duplicates are correct, then delete the ones that aren’t or whatever else you wanted to do with duplicates. Finally, you can close the formatting menu, and you can restore the standard color to your cells.
Copy Only Unique Cells in Google Sheets
Alternatively, if you’d rather automatically sort your raw data, copying unique cells instead of your duplicate cells can be useful for fast sorting and filtering. If you’re sure that your information is correct and you’d rather just outright remove the duplicate information you don’t need, you should try this method instead.
To do this, follow these steps:
- Open the Sheets document you wish to sort.
- Highlight the column you want to edit.
- After highlighting a cell, click on an empty cell at the top of an empty column to make sure that your information is moved to the side of the chart.
- Paste the following formula in the formula input box at the top of the document:
=UNIQUE(). Type the cell coordinates inside the parenthesis (e.g.
- 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
We should note that there are several plugins available for use with Google Sheets online through the Chrome Web Store, including a tool for removing duplicate data entries automatically from the document.
Remove Duplicates is an aptly-named tool offered by developer ablebits that 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 that allow you to both find and delete duplicates or unique qualities from your document, making 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 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. Using a PivotTable will not automatically delete the duplicate rows; rather, it will provide a breakdown of which rows have duplicates, so you can manually look at your data and see what, if anything, needs to be removed.
Creating a PivotTable is a little more involved than the other methods I’ve shown you in this article, but it’s worth knowing how to do, and I will walk you through it.
For more about how to use Pivot Tables, a very powerful spreadsheet feature, see this TechJunkie tutorial on how to create, edit, and refresh Pivot Tables in Google Sheets.
- Select all the table data, then go to Data->Pivot Table.
- Adjust the data range as needed.
- Hit Create.
- Select Add next to the Rows category.
- Add the row Title.
- Next to Values, select Add.
- Select Title and accept the COUNTA function as the default.
As soon as we make these selections, the PivotTable automatically updates, and now we can see the final result.
Note that the COUNTA column has the number of times that each title appears. There is a 1 for most of the titles, but Axis & Allies and Castle Risk both show 2.
This means that there are two instances of each of those titles in the chart. As you can see, the PivotTable method is a little more involved, but it also gives you a specific report about where your duplicates are to be found, which can be very useful for doing data analysis.
Keeping a duplicate cell in your files can cause some serious problems with your data if you aren’t careful, especially when trying to sort financial information into a useful spreadsheet.
Luckily, identifying, removing, and deleting identical cells of data is surprisingly easy in Google Sheets, something that’s a positive if you’re constantly dealing with spreadsheets in your day-to-day workflow. And if you’re looking for something that makes it a little easier to sort your content, you can always use an add-on like Remove Duplicates to make sure your information is well-sorted and well organized.