How To Find Duplicates in Google Sheets

By

In this tutorial, you will learn how to find duplicates in Google Sheets. The chances of creating duplicates while working with Google Sheets by accident are surprisingly high, and nobody likes it when it happens. However, sometimes you may need a more flexible set of options than simply removing all duplicates.

You may end up removing them ultimately, but it’d be harmless to review those duplicates at least first. This article will tell you how to find and highlight duplicates in Google Sheet. Please ensure that you stay tuned till the end of this article to understand the steps better.

Also Read: How to Crop Google Sheets

Finding Duplicates in Google Sheets

As far as simply removing duplicates in Google sheets is considered, it is a relatively easy task. If you wish to merely find the duplicates in the sheet you’re working with, you have to do so by highlighting them.

In this article, we’ll be going over a quick way using which you can find and highlight duplicate cells in your Google sheet quickly. So without any further delay, let’s begin.

Also Read: How To Set Up a Line Chart in Google Sheets

How to Find Duplicates in Google Sheets

In this section, we’ll be going over a step-by-step process of finding and highlighting duplicates.

Here are the steps you need to follow closely so you can access and highlight duplicates in your Google sheet:

Launch Google Sheets, and find the sheet/document.

You must locate the cells where you wish to find duplicates and highlight them. Remember that these cells can be any cell range, column, and row. Once you’ve located the cells with duplicates, select it.

Find the option of Format from the menu bar at the top of the window, and click on it.

From the drop-down menu, you’ll see after you click on Format, find and navigate to Conditional Formatting, and click on it.

Make sure that the Single Colour tab is selected on the new menu that opens and that the cells displayed under the Apply to Range are the correct ones (the ones we selected).

single color tab - how to find duplicates in google sheets

Also Read: How To Create a Dropdown in Google Sheets

We need to use a special formula that will lead to highlighting the duplicates in this Google sheet we’re working with.

Below the Apply to Range option, you’ll find an option named ‘Format Rules.’ From the drop-down menu under Format Rules, select the option of ‘Custom Formula is‘ that will most probably be at the bottom of the drop-down menu.

Once you click on ‘Custom Formula is,’ you’ll be asked to put a value or a formula in a blank box. It’ll be displayed beneath the drop-down menu from Step 6. Enter the formula mentioned below to proceed: =COUNTIF(A:A,A1)>1

Note: COUNTIF is the function here. A:A denotes the range you need to find duplicates in or the selected range, A1 can supposedly be the criteria, and >1 is more than one.

count if conditional formatting

Also Read: 10 Best Programming Apps To Learn Python

Navigate to ‘Formatting Style.’ Here we’ll be choosing the color we desire the highlights to display in. Click on the Fill Color icon, and choose a color from the palette. This will be the color in which duplicates in the selected cell range will be highlighted. You can also change the font in the cells from here.

Click on ‘Done‘ when you’ve finished choosing a color and font for duplicates you wish to find and highlight, and you’ll be able to see the cells with duplicates highlighted the next moment with the color and font you opted for.

That’s how you can easily find and highlight duplicates in Google sheets if you need to review them. We hope you found the article to be insightful.

Knowing a few tips and tricks while using Google Sheets or MS Excel can significantly help boost your productivity and help you be done with your work faster than ever. Please make sure you share this article with everyone.

Also Read: 50 Best Data Science Memes and Jokes

Frequently Asked Questions (FAQs)

How Many Users Can Edit A Google Sheet At A Particular Time?

Up to 100 users can edit a Google Sheet at a particular time. If more than 100 people are accessing the file at a given time, then only those with specific access to the file will be allowed to make edits and changes to the file.

Is Google Sheets Free Forever?

Google Sheets is free to use for everyone, along with Google Docs, Drive Gmail, Google Slides, and other applications. However, there is no clarity from Google regarding these services being free forever. This can be subject to change, just like Google Photos storage, which was once unlimited for every user.

Other related articles:

Related Posts

Scroll to Top