how to check for duplicates in google sheets

How to Check Duplicates in Google Sheets

Whether the duplicates were made in error or are truly part of a data set you are investigating, being able to find duplicates in your spreadsheet is a handy skill to have. On Google Sheets, there are a couple of ways you can use to check for duplicates.

This guide will help you explore the methods available for detecting duplicates on Google Sheets. Let’s go!

1. Using Conditional Formatting to Highlight Duplicates

Conditional Formatting is a wonderful tool to visually point out the data that is most relevant to you. You can also use it to find duplicates! Follow the steps below to learn how to implement this:

how to check for duplicates in google sheets 1

Step 1. On your spreadsheet, select the cells you want to find duplicates in. You can select a range of cells, a column, or a row.

Step 2. With cells selected, go to Format > Conditional Formatting.

Here’s an animated gif for steps 1 and 2:

how to check for duplicates in google sheets

Step 3. Now that the Conditional format rules tab is open, go to the Format rules dropdown and select Custom formula is.

how to check for duplicates in google sheets 2

Step 4. Here we’ll use the =COUNTIF function. On the text box [Value or formula], first type in =COUNTIF and then add an opening parenthesis after it.

Your current formula should look like this:

=COUNTIF(

Step 5. Copy the range under Apply to range. This is the same range of cells you initially selected. Paste it after the opening parenthesis you just typed in. Here’s a gif:

how to check for duplicates in google sheets 1

Step 6. Insert the dollar sign “$” before the characters representing Column number and Row letter. In this example, it’s A4:A29. The end result should look like this: $A$4:$A$29

Here’s a demonstration:

how to check for duplicates in google sheets 2

Step 7. Now, after the last character, add a comma “,” and then add the starting cell of your range again. In our example, it’s A4.
Close this out with a closing parenthesis )
After the parenthesis, type in >1
The completed custom formula should look like this:

See also  The Best Ways On How to Put Images in Google Sheets

=COUNTIF($A$4:$A$29,A4)>1

And, of course, our demonstration gif:

how to check for duplicates in google sheets 3

Step 8. Use the Formatting style options to customize how the duplicate items would look when they’re detected. For example, you can use any Fill Color to highlight duplicates; turn them into bold, italic, underline or even strikethrough text.

how to check for duplicates in google sheets 3

Once you’re done, your cell range should now highlight which cells have duplicates. The highlights/formatting you applied will also automatically disappear when you resolve or remove duplicates.

how to check for duplicates in google sheets 4

2. Using Find (Ctrl+F or Cmd+F) to Quickly Find Duplicates

This method is the most simple, least sophisticated way of finding duplicates in Google Sheets. This is useful if you already have leads on what you suspect might have duplicates or if you’re trying to find multiple appearances of the same data.

Step 1. Invoke the Find tool by pressing the hotkey.
On Windows PC, use Ctrl + F.
On Mac, use Cmd + F.

Step 2. Type in the word or number you’re looking for. This should start highlighting all the instances that match your keyword.

how to check for duplicates in google sheets 5

Sometimes, you’d need to find an exact match. For example, on the gif above, if you typed Peanut Butter, you’d get results for both Peanut Butter and Peanut Butter Chips. To get more refined results for such use cases, proceed to Step 3.

Step 3. (Optional) To find exact matches from a cell, click the three dots icon on the Find box. On the Find and replace dialog, tick the box that says Match entire cell contents.

how to check for duplicates in google sheets 4

3. Using a Formula to Eliminate Duplicates and Leave Only Unique Items

The function =UNIQUE is used to find just the unique values from a range of cells. This is a different approach compared to finding and highlighting repeated values as it removes duplicates automatically.

Step 1. On a separate column, enter the formula =UNIQUE

how to check for duplicates in google sheets 5

Step 2. Select a range of cells such as a column that contains the data you want to trim down to just unique values. Values that are an exact match with each other will be reduced to just one instance on your new column.

how to check for duplicates in google sheets 6

Step 3. Optional. Let’s say you want your Unique column to be useful even when you try to add more items to your main column A. You can edit the range within the formula and remove the row number from the last item just like this:

See also  Top 5 Ways to Format Tables in Google Sheets

Current setup in the example is =UNIQUE(A2:A25)

Remove the 25 before the closing parenthesis. =UNIQUE(A2:A)

What happens is when you add more entries to column A, your unique column B will still filter out any duplicates. This is usually a better approach than having to revise the range each time you make edits.

Conclusion on How to Check Duplicates in Google Sheets

As a recap, we’ve discussed three different methods on how to highlight duplicates or remove repeating entries from your Google Sheets data sets. Now that you’re equipped with this knowledge, you’re bound to make more polished spreadsheets without worrying too much about unwanted duplicates. These methods are also helpful for analyzing data when duplicates are natural parts of your sets.

We hope this guide helps you become more productive in your day-to-day work using Google Sheets!

Supercharge your Google Sheets Productivity with FileDrop

Looking to be more productive and efficient on Google Sheets? Enter FileDrop, a powerful Google Sheets and Docs add-on designed to integrate your data more tightly with your files on Google Drive. You can use FileDrop to insert PDFs, images, and other files to cells using drag and drop to give your spreadsheets more utility and customization.

Here are the core features of FileDrop:

✅Insert PDF, images, files into cells
✅Insert files from your Drive, your local storage, or web links
✅Use powerful optical character recognition tools to convert PDFs and Images to text
✅Extract table data from PDFs
✅Translate OCR text to over 50 languages

To get started being more productive on Google Sheets with FileDrop, install the free add-on using this link.

Get Our Latest Articles

Subscribe to our newsletter below to get amazing new Google Sheets articles, but also tips and tricks.

On the next pageEnter your Google Sheets domain email address

Your upgrade will not work if you don't enter the correct email address.

On the next pageEnter your Google Sheets email

Your upgrade will not work if you don't enter the correct Google email address.

Promo - Get 40% OFF with our yearly plan, expiring soon!
This is default text for notification bar