# How to Find Duplicates in Google Sheets [Easiest Way 2024]

May 8, 2024

In this article we will show you how to find duplicates in google sheets with conditional formatting, the UNIQUE function and with a useful add-on.

## Find Duplicates in Google Sheets With Conditional Formatting

In our example we will select the names in the range A1:E12. In its current formatting, it would be difficult to see the duplicate names.

### 2. In the File Menu, go to Format > Conditional Formatting

Selecting Conditional Formatting should bring up a menu for Conditional Format Rules at the right side of your screen.

### 3. In the Format Rules, choose Custom Formula is

There are also other formatting rules you can choose but since we want to find duplicates, we will input our own formula.

### 4. In the box below, insert the following formula: =COUNTIF(range,criterion)>1

Here is what our formula does,

Formula Breakdown:

COUNTIF: This function counts the number of occurrences of a single value inside a specified range

Range: Specifies what range we’ll be counting values in. We’ll be counting inside the range \$A\$1:\$E\$12. Remember to put a dollar sign “\$” before the column and the row to specify that our range is absolute.

Criterion: Here we specify that we will count a value once it is equivalent to the cell. Let us put our first value, A1 as the criterion.

>1: Assesses if our value count is greater than one, which represents that a duplicate is present. Our conditional formatting will be triggered to highlight the values with duplicates.

For our example, we will use =COUNTIF(\$A\$1:\$E\$12,A1)>1

### 5. Now we can easily spot the duplicates in our dataset

We will see the duplicate values with a light green background.

TIP: You can change the formatting that applies to duplicates in the formatting style found in the Conditional Format Rule tab.

## Finding Duplicates in Google Sheets using the UNIQUE Function

### 1. Select a separate cell from your dataset

Here we will select cell B2, separate from our dataset in column A. Make sure the rest of the column below is empty.

### 2. In this cell, use the formula =UNIQUE(range)

Where,

UNIQUE: is a formula that gets values that only appear once in your specified range

Range: is where your function will look for unique values

Given this we will input =UNIQUE(A2:A18) or =UNIQUE(A:A) (for the entire A column)

### 3. Press enter and see a list of your values where the duplicates have been removed.

You can see in our example that the duplicates of strawberries, carrots, onions, and bell peppers have been removed in our new list with only one of each value remaining. This is a very simple way to clean duplicates from your sheet.

### 2. Search “Remove Duplicates” in the Google Space Marketplace

There are several options but we will try the Remove Duplicates Add-on from Ablebits.

### 3. Install the add-on and allow permissions

The pop-up tab will ask you to log-in and allow the add-on to be installed to your account, simply follow through the instructions and allow. Now you can use this add-on to remove duplicates in Google Sheets.

### 4. In your worksheet, select the range you want to find duplicates in

Let us select the range A1:A18 for this example.

### 5. Go to Extensions > RemoveDuplicates > Find duplicate or unique rows

The add-on will also allow you to do other functions with the values and the duplicates such as comparing columns, combining duplicate rows and more.

### 6. Input range in the selection area or auto select your cells and click Next

Since we’ve selected our cells before pressing the add-on, you will see our range auto-populate the selection.

### 7. Select what type of data you want to find and click Next

You can choose to find either all duplicates, the first instances of the duplicates or the unique values. For this sample, we only want to see the duplicates.

### 8. Select your columns and click Next

This is helpful for instances where you have several columns. For our example however, we only have one so we can proceed.

### 9. Choose what you want to do with the data selected

For our example, we want to fill the cells of the duplicates with the color yellow. You can adjust this accordingly.

### 10. Press Finish and now your duplicates will be highlighted

Now we can see that the numbers 13, 10 and 12 have been repeated in our column. Since we only selected duplicates and not their first occurrences, the first time our numbers appear in the column are not highlighted in our specified color.

Using the add-on, you can also choose to delete the values of the rows that contain the duplicates to be left with unique values only.

We hope this article has helped you and given you a better understanding of how to find duplicates in Google Sheets. You might also like our articles on how to extract a substring in Google Sheets and how to remove duplicates.

To optimize your workflow, we recommend reading our guide on how to send a reminder in Google Sheets.

Get Google Sheets productivity and automation tips delivered straight to your inbox