.png)
How to Find Duplicates in Google Sheets [Easiest Way 2023]
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
1. Select your dataset
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.
Finding Duplicates in Google Sheets using an Add-On
1. In the file menu, go to Extensions > Add-ons > Get add-ons
Add-ons are additional programs that can help you with simple or complex tasks on Google Sheets.
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 > Remove Duplicates > 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.
