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.

  1. Highlight the columns you want to check for duplicate data.
  2. Select Remove Duplicates from the Data drop-down menu.highlight columns in Sheets to find duplicates
  3. 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.”
  4. Click Remove Duplicates.

confirm columns in Sheet to check for duplicates

Sheets will tell you how many copies were found and removed so that you can be sure the process worked as intended.

confirm how many Sheets duplicates removed

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.

  1. Open your Sheets file.
  2. Select the column or columns you want to sort.
  3. Select Format from the toolbar.
  4. Select Conditional Formatting.
  5. Select the range you from the new menu that appears.
  6. Change Format cells if to Custom formula is in the drop-down menu and paste the following formula into the box below the menu: =countif(A:A,A1)>1.
  7. Change the formatting style to highlight your content with a yellow (or any color of your choosing) cell background.
  8. Click done.

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

highlight duplicates in Google Sheets

 

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.

how-to-highlight-duplicates-in-google-sheets-3

To do this, follow these steps:

  1. Open the Sheets document you wish to sort.
  2. Highlight the column you want to edit.
  3. 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.
  4. 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. (A1:A75)).
  5. Hit enter to move your new data to the column you designated earlier.

how-to-highlight-duplicates-in-google-sheets-4

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.

  1. Select all the table data, then go to Data->Pivot Table.
  2. Adjust the data range as needed.
  3. Hit Create.
  4.  Select Add next to the Rows category.
  5. Add the row Title.
  6. Next to Values, select Add.
  7. 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.

Final Thoughts

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.

11 thoughts on “How To Highlight Duplicates in Google Sheets”

Avatar WattsUp says:
I have tried this dozens of times, and it will not work. The strange thing is, I have the same formula in another sheet and it works perfectly. If anyone has an idea I would be really thankful.
Avatar Matthew Hiller says:
This works, with one exception. It is highlighting the cell under the duplicate, not the duplicate cell. What am I doing wrong?
Avatar Claire says:
If your range does not start at A1 (eg. A3:A100) your formula must be

=countif(A:A,A3)>1

Notice the A3 in that countif and not A1. That second parameter in countif should be the starting point of the range.

Avatar Jimmie says:
This formula did absolutely nothing. I have duplicate names and it didn’t highlight them at all.
Avatar Robert says:
I just checked the formula and it worked perfectly. Are you sure you selected the right column and put the correct formula in?
Avatar Employable Helix says:
Works for me. As Claire said in July 2019 above, in order for mine to work correctly, I had to set the range within the COUNTIF to be large and start at least one column to the left of my data:

The range of my data is B7:C16
The range within my COUNTIF is A:Z
(column A being at least one column to the left of my data, which starts in column B)

Specifically:
Apply to range: B7:C16
Custom formula is:
=countif(A:Z,B7)>1

Thank you, Claire!

Avatar Shannon says:
Did not work on a list of email addresses. Bummer.
Avatar Randy Barrett says:
This did not work. It just highlighted everything in the column. I verified this by filtering by work ticket #. Only one entry was present, but it was highlighted. I need just the work ticket entry that are duplicates to be highlighted. This way when I enter a work ticket # in the column that I’ve already entered it will highlight and let me know that I’ve already entered the information.
Avatar Laura says:
How do you search for same reference number on two seperate google sheets?
Avatar Michael says:
@Josh

I was having the same issue this evening – all but the last duplicate was highlighted. For me it worked for me to simply reduce the range reduction of the formula:

Your example: Apply to range A4:A1003 – Custom formula is =countif(a4:a1003,a4:1003)>1

Use instead: Apply to range A4:A1003 – Custom formula is =countif(a:a,a4)>1

Then even the latest duplicate should be highlighted according to your settings.

Cheers

Avatar Jordan says:
@Josh, actually the correct way to do this is to make the custom formula:
=countif(A$4:A,A4)>1 and set the range to A4:A — adding the $ before the 4 means that the range to check doesn’t get updated as you go. (The formula in custom formula is re-evaluated as if you were doing copy paste.)
Avatar Mihai says:
This worked perfectly!

I was having an issue with the previous formula where it would highlight all the duplicates except for the last one. As if it was checking in one direction (down the column) so when it got to the last one there were no more duplicates to check against. This fixed the issue!

Avatar Christoffer Henriksen says:
Is there a formula that is the exact opposite of UNIQUE?

So it would list all duplicates instead of all those that are unique?

Avatar Christoffer Henriksen says:
Can you help me create it or just help me out in doing so?
Avatar Mohsin says:
This system works, but when a single letter or even a comma change it not detect, also if a column is changed the information storing that not detecting. Need to stay in the same column with same text/character. Anyway, thank you, at least I got a chance to check my sheet.
Avatar Cameron says:
This was very helpful, but is there any way that I can make it so that every duplicate item is highlighted a different color (randomly, not assigned)? ie: Every cell that says ‘Google’ in my column is colored red, but every cell that says ‘Sheets’ is colored blue, and so on and so forth.
If not, thank you anyway! This article has been a big help already.
Avatar Josh says:
just have to create a second conditional formatting rule for each criteria, multiple rules can be assigned to the same range
Avatar Colleen says:
Hello, I understand how to have duplicates on for a selected range. What I want- google sheets to highlight new data that I enter in that is a duplicate. Right now, it is only highlighting duplicates for the current set of data, but if a new cell is entered it does not highlight that as a duplicate. I need it to be actively highlighting duplicates as they are added. Thank you I appreciate the help!
Avatar Syed Tabassum Ali says:
Hey Colleen,
You just need to change the range in the formula or you can set a range of whole column in advance if you need to enter more values. Because the formula will only work with the provided range.
Avatar Josh says:
Syed,
I am not sure if I am reading Colleens question properly, but it sounds like the issue I am having.

So I have data validation setup for cells A4:A1003, it gives me a drop down menu with a list of options I can choose from. The equation “=countif(a:a,a1)>1”, was not working properly for me, it was highlighting random cells in the column based on a duplicate, (generally 4 cells below where the duplicate entry was made) I changed the equation to “=countif(a4:a1003,a4:1003)>1” and it seems to work properly, only highlighting the actual duplicates. One problem, it highlights the original or 1st cell that contains the duplicated information, not the new cell or 2nd entry made. So if I am 50 cells down in a column I wouldn’t know there was a duplicate unless I scroll back up in the sheet and see the highlighted cell. I can not seem to find a way to make the 2nd duplicated entry be the cell that gets highlighted instead of the 1st entry.

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.