PWSHub News

2 Ways to Highlight and Remove Duplicates in Google Sheets - Make Tech Easier

Laptop With Open Spreadsheet

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.

Remove Google Sheets duplicates using the data option.

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:


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.


Related stories
2 weeks ago - Are you ready to dive into the world of Python back-end development? Whether you’re a beginner looking to learn the basics or an experienced developer wanting to upgrade your skills, this handbook is your ultimate guide. Imagine being...
3 weeks ago - Javascript has become a very popular programming language thanks to its growing use in frontend and backend development. And as devs build JavaScript applications for different companies and organizations, the size and complexity of...
3 weeks ago - Implement Google Consent mode v2 with iubenda to remove all the blind spots in your conversion data. Privacy laws are getting complicated worldwide,
8 hours ago - Digital designer Yichan Wang has put together this collection of strategies and selling points to help you encourage and advocate for accessibility in your place of work, including useful scripts you can use as starting points.
Other stories
2 hours ago - Using voice memos to brainstorm ideas is quick, and easier than ever thanks to AI-powered apps. Here's how to get started.
2 hours ago - Mistral Medium only came out two months ago, and now it's followed by Mistral Large. Like Medium, this new model is currently only available via their API. It scores well …
2 hours ago - Have you started learning React, only to face bugs that made you contemplate a career in goat herding? Don't worry – we've all been there. In this guide, you'll join me on a quest through the quirky wonders of React. I'll help you...
2 hours ago - The Geolocation API is a standard API implemented in browsers to retrieve the location of the people who are interacting with a web application. This API enable users to send their location to a web application to enable relevant...
2 hours ago - Explore the intricacies of reading and writing files in various formats, handling directories, mastering error-handling techniques, and harnessing the efficiency of streams for powerful, performance-boosting file operations.
3 hours ago - WordPress is a popular content management system (CMS) known for its flexibility and user-friendly interface. But its built-in editor is not ideal for collaborative editing. Many publishers who work with writers need collaborative writing...