Find Duplicates in Google Sheets: The Easiest Way in 2024!

Welcome, my friends! Today, I’m going to share with you the juiciest secrets on how to find duplicates in Google Sheets. By using a combination of conditional formatting, the UNIQUE function, and a handy add-on, you’ll be able to clean up your data effortlessly. Let’s dive in!

Find Duplicates in Google Sheets with Conditional Formatting

1. Select your data set: In our example, we’ll choose the range A1:E12. As it stands, it’s challenging to identify any duplicate names.

2. Go to File > Conditional Formatting: Upon selecting conditional formatting, a menu for conditional formatting rules will appear on the right side of your screen.

3. Choose “Custom Formula” in the Formatting Rules: While other formatting rules are available, we want to find duplicates, so we’ll create our own formula.

4. Insert the following formula in the box: =COUNTIF(range,criteria)>1. Here’s what our formula does:

   • COUNTIF: This function counts the number of occurrences of a unique value in a specified range.
   • Range: Specifies the range in which we’ll count the values. We’ll count in the range $A$1:$E$12. Remember to use a dollar sign “$” before the column and row to indicate that our range is absolute.
   • Criteria: Here, we specify that we’ll count a value once it is equivalent to the cell. Let’s set our first value, A1, as the criteria.
   • >1: Evaluates if our value count is greater than one, indicating the presence of a duplicate. Our conditional formatting will highlight the duplicate values.

   For our example, we’ll use =COUNTIF($A$1:$E$12,A1)>1.

5. Now, we can easily spot the duplicates in our data set. The duplicate values will be highlighted with a light green background.

Tip: You can modify the formatting that applies to duplicates in the conditional formatting rule’s style.

Find Duplicates in Google Sheets Using the UNIQUE Function

1. Select a separate cell from your data set: We’ll choose cell B2, which is separate from our data set in column A. Make sure the rest of the column below is empty.

2. In this cell, use the formula =UNIQUE(range): Here’s what you need to know:

   • UNIQUE: This formula retrieves values that appear only once in the specified range.
   • Range: This is where your function will search for unique values.

   With this in mind, we’ll enter =UNIQUE(A2:A18) or =UNIQUE(A:A) (for the entire column A).

3. Press Enter and see the list of values where duplicates have been removed: In our example, we can see that duplicates of strawberries, carrots, onions, and peppers have been removed from our new list, leaving only one occurrence of each value. It’s a simple way to clean up duplicates from your sheet.

Find Duplicates in Google Sheets Using an Add-on

1. Go to File > Add-ons > Get add-ons: Add-ons are additional programs that can assist you with simple or complex tasks in Google Sheets.

2. Search for “Remove Duplicates” in the Google Workspace Marketplace: Several options are available, but we’ll try the “Remove Duplicates” add-on from Ablebits.

3. Install the add-on and grant permissions: The pop-up tab will prompt you to sign in and authorize the add-on installation on your account. Simply follow the instructions and authorize the add-on. Now, you can use it to remove duplicates in Google Sheets.

4. In your spreadsheet, select the range where you want to find duplicates: Let’s choose the range A1:A18 for this example.

5. Go to Add-ons > Remove Duplicates > Find duplicate or unique rows: The add-on offers various functions for working with values and duplicates, such as comparing columns and merging duplicate rows.

6. Enter the range in the selection box or automatically select your cells and click Next: Since we already selected our cells before clicking the add-on, you’ll see our range automatically appear in the selection.

7. Select the type of data you want to find and click Next: You can choose to find all duplicates, the first occurrences of duplicates, or unique values. In this example, we only want to see the duplicates.

8. Select your columns and click Next: This step is useful when you have multiple columns. Since we only have one in our example, we can proceed.

9. Choose what you want to do with the selected data: In our example, we want to fill the duplicate cells with the color yellow. Adjust this according to your preference.

10. Click Finish and now your duplicates will be highlighted: From here, we can see that the numbers 13, 10, and 12 are repeated in our column. As we selected only the duplicates and not their first occurrences, the first appearances of our numbers in the column are not highlighted in the specified color.

Using this add-on, you can also choose to remove the values from the rows that contain duplicates, leaving only unique values.

We hope this article has given you a better understanding of how to find duplicates in Google Sheets using various methods. For more information and additional tips, visit Crawlan.com. Don’t forget to also check out our articles on how to extract a substring in Google Sheets and how to remove duplicates.

Until next time, my lovelies!