إنتاجية

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.

Click duplicates button on Google Sheets.

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.

Duplicates found on Google Sheets

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

Duplicates Highlighted Google Sheets

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 Options Google Sheets

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.

Google Sheets Use 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.

Google Sheets Duplicate Conditional Formatting Format Rules

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.

Google Sheets Duplicate Conditional Formatting format rules formula

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

Google Sheets Duplicates Highlighted Conditional Formatting

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.

Subscribe to our newsletter!

Our latest tutorials delivered straight to your inbox

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.

مقالات ذات صلة

اترك تعليقاً

لن يتم نشر عنوان بريدك الإلكتروني. الحقول الإلزامية مشار إليها بـ *

زر الذهاب إلى الأعلى