Watch Video – Highlight Duplicates in Google Sheets
With a little know-how, you can use Google Sheets to highlight duplicates. You can customize how Sheets represents a duplicate match so it makes sense to a person looking at the data.
For example, Google Sheets can highlight an entire row of data if there’s a duplicate value in a specific column.
Alternatively, Sheets can just highlight any cell with a value that appears more than once on a spreadsheet page.
The following guide breaks down how to highlight duplicates in Google Sheets (explained with multiple examples)
Highlight Duplicates in Google Sheets (in a Single Column)
Suppose you have a dataset as shown below and you want to highlight all the duplicate values in column A.
To highlight duplicates cells with conditional formatting, follow these steps.
- Select the range. In the example case, we want to use A2:A15 (image 1, box).
- Open the conditional formatting window by opening the “Format” drop menu and choosing “Conditional formatting” from the list.
- Click on the “+ Add another rule” option in the conditional formatting pane that opens. In case you already have conditional formatting applied, this option will be at the bottom (below the existing rule).
- Set “Format cells if…” to “Custom formula is” and enter the duplicate check formula “=countif(Range,Criteria)>1” and hit “Done” to return a “true/false” response. In the case of our example, we’re using the formula “=countif($A$2:$A$15,A2)>1” to check for duplicates.
Google Sheets is now highlighting duplicates in our grocery list.
Note that using this method highlights all the instances of the duplicate cell. So if an item repeats twice or thrice, all the cells that have this item/text will be highlighted.
The COUNTIF function used in conditional formatting counts how many times a cell text string appears in the list. If it’s more than 1, the formula returns TRUE and those cells are highlighted.
Note: Use the “Formatting style” section to change the highlighting color and font style. This is very helpful if you need to highlight more than one color at a time.
Highlighting and Find Duplicates in Google Sheets
When working with larger spreadsheets with many columns, you will likely want to highlight the entire column when there’s a duplicate instead of just the cell.
This makes it easier to find duplicates when the duplicated cell isn’t on the screen.
This process differs in two ways:
- The range needs to include all rows (A2:A15 is now A2:F15).
- The criteria section needs to use an absolute value for the column (A2 is now $A2).
Let’s use our grocery list from before, but now we’ve added where you find each product in the store as a new column (as shown below).
Follow these steps to highlight the entire row for duplicate cells in one column:
- Select the range of all columns and rows you want to highlight instead of just the column you’re searching for matches. In the example, select A2:B15 instead of A2:A15
- Open the “Conditional formatting” feature and check the new range (image 2, top box).
- In the “Conditional formatting” tab, set the “Format rules” to “Custom formula is…” and enter the duplicate-finding formula with the criteria adjusted for an absolute column. This means adding “$” before “A2” in our example. So “=countif($A$2:$A$15,A2)>1” is now “=countif($A$2:$A$15,$A2)>1”.
- Hit “Done” to apply the new formatting (image 2, bottom box).
Now we’ve highlighted the entire row range for columns with duplicates.
Highlight Duplicates Instances Only
As I mentioned earlier, when you use conditional formatting to highlight duplicates in Google Sheets, it will highlight all the instances of duplicate cells.
But what if you want to highlight only the duplicate instances (and not the first time it appears in the list).
You can adjust the formula so Google Sheets will only highlight the second or later instance of a duplicate by making a tweak to the formula.
Suppose you have the dataset as shown below and you want to highlight only the cells that have the duplicate item.
We need to make a tweak to our old “=countif($A$2:$A$15,$A2)>1” formula (this is the one we used in the previous section).
- Adjust the ending range value from $A$15 to $A2. Now that we’ve set the column to absolute in the ending, but now the row, each row will only look at rows above itself for duplicates.
- Our new formula will look like this: ”=countif($A$2:$A2,$A2)>1”
In our example, this change to the formula (arrow) now only highlights the second instance in which our duplicates appear (box).
Remove all duplicates shortcut
Google Sheets has a handy tool for removing duplicates instead of highlighting them.
We’ll use our grocery list with product location data from before to show how it works.
The following steps demonstrate this tool:
- Select the cell range you want to analyze.
- Open the “Data” menu and select “Remove Duplicates”.
- From the “Remove duplicates” pop-up, check “Data has header row” if the data has this feature in your selection then choose the columns you want to check for duplicate values from the checklist. In our example, we only want to compare column A values.
- Choose “Remove duplicates” to apply the change.
The spreadsheet now only has rows without duplicate cells from our selection.
Highlight Duplicates with Added criteria
Google Sheets can check for conditional highlighting with additional criteria. You can set the program to highlight only rows with duplicate values in multiple columns or only duplicates with specific values.
The formula needs to use the “*” (and) operator to apply both conditions. An example formula would look like this:
=(countif(Range,Criteria)>1) * (New Condition) )
For example, we can have our grocery list examples from before only highlight duplicate products that are fruits or vegetables. We don’t care about products that appear twice in the bakery or any of the aisles.
Our spreadsheet now features lists all the different fruits and vegetables available at the store:
In our example, we want to highlight the values in columns C and D if a row value appears twice in column C and at all in columns A and B.
Our formula construction will look like this:
- Start with our old formula, now modified to check column C instead of A: =countif($C$2:$C$15,$C2)>1
- Wrap the formula in parenthesis to add the second condition: =(countif($C$2:$C$15,$C2)>1))
- Add the “and/*” operator and the second set of parentheses for the second condition:
- Our second condition checks if the match appears in columns A and B, so it will look a little different. Instead of checking for “greater than 1” it will check for “greater than 0” and look like this: countif($A$2:$B$15,$C2)>0
- Our new formula will look like this: =(countif($C$2:$C$15,$C2)>1)*(countif($A$2:$B$15,$C2)>0)
Applying the new range and new formula to our data set will highlight all the fruits and vegetables that are duplicates. It will not highlight the bagels which appear twice because they aren’t in the fruits or vegetable columns.
Some tips when highlighting duplicates in Google Sheets
Here are some things to keep in mind when working on highlighting duplicates in Google Sheets:
Create a Unique Cells List (Instead of higlighting duplicates)
Sometimes going through the process of highlighting data to find duplicates isn’t the fastest way to find out what you’re looking for.
If you just want to come up with a list of unique values to visualize how many duplicates you’re working with, the “=unique()” formula might be right for you.
To use this feature: enter “=unique(range start: range end)” in the cell you want to be at the top of your list.
In this grocery list example, we are entering “=unique(A2:A15)” at cell B2. When applied, it populates all the cells below it necessary to come up with a unique values list.
Trim Whitespace to Curate Data
It’s common to encounter extra spaces before and after data when you’re importing information into Google Sheets from other sources.
For example, an email might come in as both “email@example.com” and “firstname.lastname@example.org “ with a space at the end of the second version. These are duplicates but won’t show up as such because that empty space confuses Google Sheets.
You can trim whitespace from your data with the following:
- Select the data you want to trim (left bßox).
- Open the “Data” drop menu (arrow).
- Choose “Trim whitespace” from the list.
If the selected data has any invisible whitespaces, Google Sheets will remove them. The data won’t look any different to you, but it is now different for data analysis.
Highlighting duplicates in Google Sheets is a valuable tool for making information easier for people to understand.
Sheets offers an immense range of customization options for highlighting duplicates with multiple conditions.
I hope this tutorial helped you better understand how to use this useful feature!