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:
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:
Step 3. Now that the Conditional format rules tab is open, go to the Format rules dropdown and select Custom formula is.
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:
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:
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:
=COUNTIF($A$4:$A$29,A4)>1
And, of course, our demonstration gif:
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.
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.
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.
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.
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
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.
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:
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.
