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.
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.
Selecting Conditional Formatting should bring up a menu for Conditional Format Rules at the right side of your screen.
There are also other formatting rules you can choose but since we want to find duplicates, we will input our own formula.
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
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.
Here we will select cell B2, separate from our dataset in column A. Make sure the rest of the column below is empty.
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)
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.
Add-ons are additional programs that can help you with simple or complex tasks on Google Sheets.
There are several options but we will try the Remove Duplicates Add-on from Ablebits.
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.
Let us select the range A1:A18 for this example.
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.
Since we’ve selected our cells before pressing the add-on, you will see our range auto-populate the selection.
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.
This is helpful for instances where you have several columns. For our example however, we only have one so we can proceed.
For our example, we want to fill the cells of the duplicates with the color yellow. You can adjust this accordingly.
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.
