2 Ways to Highlight and Remove Duplicates in Google Sheets

It is extremely useful to know how to highlight and remove duplicates in Google Sheets if you have a Sheet with a large amount of data. The more data you have, the more likely it is that you will end up with duplicates.

So in order to clean up the document, you need to know how to locate and delete those duplicates without spending tedious amounts of time checking each cell manually.

Remove Duplicates in Google Sheets Using the Data Tab

The fastest and easiest way to search for duplicates in a Google Sheet is to use the Data menu. By clicking options in a menu, this way to find and remove duplicates takes literally under 30 seconds.

If you merely want to see if one particular piece of data has a duplicate, click that cell. Then go to Data -> Data clean-up -> Remove duplicates.

You usually don’t have to change any of the boxes shown. Just click the green Remove duplicates button at the bottom.

If the Sheet finds a duplicate, it will tell you. It will also remove that duplicate information for you so you don’t have to locate it and delete it yourself.

However, if you want Sheets to check multiple columns or rows at the same time, select them either by clicking the letter at the top (to choose the column), or clicking the number (to select a row).

Now click the same Remove duplicates button and the same process as before will start. If duplicates are found, they will all automatically be deleted.

Remove Duplicates in Google Sheets With Conditional Formatting

A second slightly more cumbersome way of finding and removing duplicates is by using conditional formatting. This is when you highlight the cells you want to check and you add a Sheets formula.

Make sure first that the cells do not have another conditional formatting formula inserted. Otherwise the two formulas will clash and not work properly.

First, select the column that you want to check, then go to Format -> Conditional formatting.

A box will now open up in the right-hand-side bar. Under the Format rules menu, scroll down to the bottom and select Custom formula is.

A new box will open up under Custom formula is. The code to put in there is:

=COUNTIF($A$1:$A$34,$A1)>1

However, you need to alter that formula if you have different columns and rows highlighted.

Once you entered the formula, all duplicates will be highlighted. You can now delete them the usual way.

If you’re looking for an easy way to remove duplicates from Google Sheets, then the first method is definitely the easiest. But if you are really into Google Sheets functions and formulas, then you may prefer using conditional formatting.

Image credit: Pixabay. All screenshots by Mark O’Neill.

Mark O’Neill is a freelance tech journalist, editor, and bestselling spy fiction author. Originally from Scotland, he now lives in Germany with his wife and his dog.