11

How To Highlight Duplicates in Google Sheets

Posted by Robert Hayes on October 14, 2019

Microsoft Excel has dominated the spreadsheet market for many years. Business and home users both rely on Excel to organize and analyze their data. Whether for checklists, budgets, financial records, email lists, or any other type of tabular data, Excel makes it easy to organize your information.

There were alternatives at lower prices, and even in some cases for free—but if you wanted the most powerful spreadsheet program imaginable, you’d want to use Microsoft Excel.

In 2006, however, another alternative began to take form. That’s when Google rolled out Sheets, as part of its web-based office suite for text, spreadsheets, and presentations.

The development process continued for years, and now Sheets – while still not a pound-for-pound match for Excel – has a large percentage of Excel’s standard functionality. Sheets doesn’t cost its users hundreds of dollars to purchase – in fact, it’s completely free.

While it isn’t a perfect product, anyone looking for a solid, consumer-level spreadsheet tool really doesn’t have to look farther than Google Sheets. Sheets cannot do everything that Excel can do, but odds are you don’t actually need it to do everything Excel can do. If Sheets does everything you need it to do, then you don’t need to buy Excel.

However, the truth is that Sheets still has many limitations and some things that are trivial in Excel are a little more work in Sheets. You’ve probably run into a problem where you accidentally add duplicate entries to your spreadsheet. After all, the more data you add, the more likely you are to accidentally insert duplicated data into a spreadsheet, which can throw off the dataset you’ve been working so hard to put together.

That’s bad enough, but the worst part comes later on when you’re trying to look over your work. Since spreadsheets can be lengthy documents, it becomes difficult to spot and remove duplicates as time goes by, leading to calculation errors in your work without an obvious source for where the problem is coming from.  In this article, I’m going to show you a few different ways to find duplicates in Google Sheets, then highlight or delete duplicates.

Fortunately, we’ve found several different methods to highlight duplicates inside of Google Sheets. It’s important that our method not automatically remove duplicate information, as not all duplicates are errors. But if it’s just highlighted, then you can determine which ones don’t need to be there yourself, without needing to comb through the entire spreadsheet.

Finding and Highlighting Duplicates in Google Sheets

Since we want Google Sheets to automatically highlight our information for us, 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 duplicartes then remove them, you can use the built-in Remove Duplicates feature in Sheets.

Use Sheets’ Remove Duplicates Feature

Whether you are trying to find duplicates in one column, two columns, or a whole worksheet, the Remove Duplicates built-in feature is a good choice.

First, highlight the columns you want to check for duplicate data, then select Remove Duplicates from the Data pulldown menu.highlight columns in Sheets to find duplicates

Second, a dialogue box will pop up. Check “data has header row” if there’s a row with headers for the columns, make sure the checkbox is checked next to columns you want to check or “Select All,” then click Remove Duplicates.

confirm columns in Sheet to check for duplicates

Third, confirm the remove duplicates by reviewing how many duplicates “now found and removed” and how many records remained after Sheets removed the duplicates.

confirm how many Sheets duplicates removed

Using Google Sheets’ built-in Find and Remove duplicates feature is the most straightforward way to find and remove duplicates but sometimes you might want to review the duplicates before removing them. For more on

A great way to do that is with color highlighting.

Highlight Sheets Duplicate 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 most obvious, most visible way to go. By highlighting information, it’s easy to identify the mistakes very quickly, since you can simply run down the list of content that you need to identify. We’ll be using a red highlight in this step to identify our duplicate content since red catches the eye (especially compared to the usual white and grey color scheme of Sheets) and is the universal color for error messages.

Start by opening up the Sheets file you want to check. You’ll want to make sure your information is well organized by both column and row, in order to easily check the content in your document; no need to make your own work more difficult.

Now, highlight the column you want to sort at the top of the document and select Format in the top menu of your document. Select “Conditional Formatting” from the list of options in this menu, and an alternate menu will appear on the right side of the display. In this menu, select the range by using the corresponding letter and number combinations (for example, A1 to A76).

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

Once you’ve selected your content, change “Format cells if” to “Custom formula is” in the dropdown menu and type or paste the following without quotes: “=countif(A:A,A1)>1” into the box beneath your menu. This will complete the formula for your content.

Once this is set, change the formatting style to highlight your content with a red cell background, and click the done icon on your menu. 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. Close the formatting menu and you can restore the normal color to your cells.

Copy Only Unique Cells in Google Sheets

Alternatively, if you’d rather automatically sort your raw data, copying only unique cells instead of your duplicate cells can be really 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

As with the last step, start by opening the document you wish to sort inside of Google Sheets. Highlight the column you wish to edit. Once you’ve highlighted the cell, click on an empty cell in the top of an empty column in order to make sure that your information is moved to the side of the chart. This column will be used to display the results of your sort and formula below.

Now, at the top of the document in the formula input box, type or paste the following without quotes: “=UNIQUE()”. This formula will tell Sheets to copy and display only unique cell inputs, and to ignore any information that copies or duplicates other cells. Within the parenthesis of the formula above, make sure to type the cell coordinates using the standard spreadsheet method (for example, typing (A1:A75) will scan all the information from column A row 1, to column A row 75). Once you’ve entered the new information, 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.

 

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

Use an Add-On to Find and Remove Duplicates in Sheets

We should note that there are a number of 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, just as you can with the formula tasks above, although this process is far more automated than we’ve seen previously, so keep that in mind if you want more control over the process. 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 constantly looking for duplicates in their spreadsheets and would understandably rather spend their time doing something else, but users who only need to check once or twice every few months may be better off just using the formula tools above to manually identify their information.

That said, Remove Duplicates has a solid rating on the Chrome Web Store, with strong reviews and an active development team that responds to critiques and complaints. Overall, it’s a great choice if you’re looking to find a more streamlined way to sort your content and find duplicates within a spreadsheet.

Use the Remove Duplicates Tool to Find and Delete Duplicate Cells In Google Sheets

Remember I talked above about how Google keeps rolling out new features to add to the power of Sheets? Well, since this article was originally written, they’ve actually added a full-featured Remove Duplicates tool to the core package. It’s a very simple tool to use, and I’ll walk you through it.

Let’s take a typical spreadsheet, with a list of board games…and if you read closely, you’ll see that I’ve put a couple of duplicates into the list.

To use the tool, all we need to do is select the data area where we want to de-duplicate. Note that we can select rows or columns freely; if we include the Price column, then the Remove Duplicates function will look at both Title and Price to decide if a row is a duplicate or not.

Once the data area is selected, go to Data->Remove Duplicates. Accept the default values in the dialogs, and voila – the deduplication is done automatically.

 

Use a PivotTable to Find Duplicate Rows in Sheets

Sheets implements a full set of PivotTable functionality, which is a very handy 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, has gone awry.

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.

First, select all the table data, and then go to Data->Pivot Table.

You can adjust the data range here if you wish, as well as decide where the PivotTable should go. I’ll put it into a new sheet and accept the range I already gave it. When we hit “Create”, a blank PivotTable will open at the insertion point…but a blank PivotTable doesn’t do us any good. We have to tell it which information to analyze.

We’re going to select “Add” next to Rows, and add the row “Title”. Then under Values, we’re going to select “Add” and select “Title” again, then accept the COUNTA function as the default. (If we were looking for duplicate numeric data, we would use COUNT; COUNTA is for counting text fields.)

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.

***

Spreadsheets are often quite a bit more complicated than similar documents made in Google Docs or in Microsoft Word. Because they’re built to deal with organized data, it’s important to make sure that your content is accurate at all times. Keeping a duplicate cell in your files can really cause some serious problems with your data if you aren’t careful, especially when trying to sort financial information into a useful spreadsheet.

Compounding matters, trying to find the erroneous cell on your own can be a hassle that’s nearly impossible if the spreadsheet is large. 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. In 2019, there’s no reason to keep a messy, error-filled spreadsheet, so make sure that your data is correct and your information is verified by using the methods outlined above.

Want more information about Google Sheets?

Finding your duplicates is one thing, but you should check our tutorial on how to count the duplicates in Google Sheets.

Statisticians use the absolute value function for many purposes, and we’ll show you how to use absolute value in Google Sheets.

Want to secure your spreadsheets? Here’s how to lock a formula in Google Sheets.

If you want to compare data between columns, see our complete guide to comparing columns in Google Sheets.

Did you know you can keep calendar and timesheet information in Sheets? Just read our tutorial on working with date and time information in Google Sheets.

Do you have any tips or tricks for finding duplicates in Google Sheets? What do you think is the most efficient way to find and remove duplicates in Google Sheets? Please tell us about it in the comments below!

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

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.
Reply
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?
Reply
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.

Reply
Jimmie says:
This formula did absolutely nothing. I have duplicate names and it didn’t highlight them at all.
Reply
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?
Reply
Shannon says:
Did not work on a list of email addresses. Bummer.
Reply
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.
Reply
Laura says:
How do you search for same reference number on two seperate google sheets?
Reply
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

Reply
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.)
Reply
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!

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?

Reply
Christoffer Henriksen says:
Can you help me create it or just help me out in doing so?
Reply
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.
Reply
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.
Reply
Josh says:
just have to create a second conditional formatting rule for each criteria, multiple rules can be assigned to the same range
Reply
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!
Reply
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.
Reply
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.