Read below to find our tutorials to highlight duplicates in Google Sheets using conditional formatting and a some useful formulas. We cover six ways to highlight duplicates in Google Sheets and give you an example spreadsheet so that you can see these methods in action and master them!
When to highlight duplicates in Google Sheets?
Highlighting duplicates in Google Sheets can be incredibly useful for locating repeat data in an enormous spreadsheet.
Suppose you're organizing a newsletter list (or any other Google Sheets spreadsheet), and your boss wants you to flag--but not delete--emails that occur more than once.
Unfortunately, you can't select the Remove duplicates button (under the Data tab) as that will get rid of certain data completely.
Instead, you'll want to highlight duplicates in Google Sheets, which can be done pretty quickly with conditional formatting.
Google Sheets Highlight duplicates: What skills do we need?
COUNTIF or COUNTIFS
We will use the COUNTIF or the COUNTIFS formula to find duplicate data, which are both incredibly useful. You can read more about these formulas in the tutorials linked below:
COUNTIF Google Sheets: The Ultimate Guide
COUNTIFS Google Sheets: The Ultimate Guide
Google Sheets Conditional Formatting
Furthermore, we will use Google Sheets conditional formatting to highlight duplicates in Google Sheets. You can read more about conditional formatting in our article below:
Conditional Formatting Google Sheets: The Ultimate 2022 Guide
Finally, you should realize that these methods will not work if some cells have extra spaces or other character after the cells content.
For example, one cell has (San Francisco, CA_) and another has (San Francisco, CA).
In this case, the cell contents do not exactly match, meaning that Google Sheets will not recognize that the city names match.
Before beginning this tutorial, make sure your data is in similar formats so that the comparison and highlighting works correctly.
How to Highlight Duplicates in Google Sheets
Follow these 6 simple steps to highlight duplicates in Google Sheets:
- Select the range where you want to search for duplicates.
- Click the Format tab.
- Choose the Conditional Formatting option.
- Under Format Rules, select Custom Formula is.
- Enter the following formula to match your selection: =countif([$column$row:$column$row],[$first cell in range])>1.
- In our example, we want to search through column C, starting in C1, so we entered:
- =COUNTIF(C:C, C1)>1
- Select your desired color to highlight duplicates from the Formatting Style options
- Voila, the sheet will update accordingly!
For our example, we included the header row. You can choose to include the header row or not, but it will not influence final result in most cases.
Highlight Duplicate in multiple columns
Finding duplicates in multiple columns can be useful if you want to compare different lists to identify repeated values.
For example, you can compare grocery lists to prevent yourself from buying multiple items of the same food. You can also use it to compare separate lists of travel destinations to find agreed-upon destinations for a vacation or trip.
Similar to the method above, we will again use conditional formatting and the formula, but with some slight changes when we format conditional formatting rules.
Follow these steps:
- Select your range of cells.
- In the Custom Formula is box, add this formula: =countif([$column$row:$column$row],[first cell in range])>1
The only difference between this formula and the first example is that there is no $ sign in front of the first cell in the range.
This allows the formula to find duplicates in Google Sheets from every cell in the range, not just the first row. In our example, we used the formula =COUNTIF($A$2:$C$8,A2)>1.
- Select a color from the Formatting style options and click done.
Now, you can identify duplicate data in multiple columns.
Highlight the entire row if duplicates are in one column
Next, we will address the example of highlighting the entire row if only one column has a duplicate.
In our example, we have cities and capitals in different regions in the world. All of our duplicates are in the “Region” column, column C.
To do this, we want to apply our formatting to the whole chart but only search the third column.
Follow these steps:
- Select the whole range of data
- In the Custom Formula is box of the conditional formatting sidebar, type =countif([$column$row:$column$row],[$first cell in range])>1.
However, we only want to include the column with the duplicates.
In our case, we used the formula =COUNTIF($C$2:$C$9,$C2)>1 and applied this to the range A2:C9.
This searches the third column for duplicate values and applies the formatting rules to the whole row of the range.
Highlight complete row duplicates in Google Sheets
Finally, we want to highlight entire duplicate rows in Google Sheets. In this case, the whole row has to match another row for it to count as a duplicate.
Once again, we have a list of cities, countries, and regions.
We want to find duplicate rows in Google Sheets that have the same city, country, and region.
Once again, we will use the COUNTIF formula, but this time we will also use ARRAYFORMULA:
- Select the full range of cells
- In the Custom formula is box, type the formula =COUNTIF(ArrayFormula($A$2:$A$15&$B$2:$B$15&$C$2:$C$15),$A2&$B2&$C2)>1.
ARRAYFORMULA concatenates all three rows into one string, and then COUNTIF searches for duplicates among the strings.
Highlight only duplicate instances in Google Sheets
Often, you only actually want to highlight the second instance of replicated content and ignore the first instance. Then, you can remove or address the duplicates while keeping one instance that will no longer be a duplicate after you remove the extra instances.
Rather than highlighting all duplicate values, this example will only highlight the second, third, fourth and so on instance of duplicated content.
To do this, follow the same steps as the example above and slightly tweak the ARRAYFORMULA portion. Rather than concatenating every row, only mention the first row of each column.
In our example, we would write =COUNTIF(ArrayFormula($A$2:$A2&$B$2:$B2&$C$2:$C2),$A2&$B2&$C2)>1.
The syntax difference between the previous custom formula to highlight duplicate rows and this method to only highlight second instances is that we only mention the first cell in the column rather than the whole column: $A$2:$A2&$B$2:$B2&$C$2:$C2
Using this syntax, when the formula goes through each line, it only compares that line to lines above it in its search for duplicates. Therefore, only the second, third, fourth, and nth instances will count as duplicates.
Highlight Duplicates with an Add-On
An easy option to avoid using conditional formatting is to use an add-on. For this example, we will use Remove Duplicates.
Although this add-on claims to remove duplicates, we can configure it to only highlight duplicate cells.
After installing the add-on, you can find it by going to Extensions->Remove duplicates.
From there, you can choose whether to find duplicate or unique rows or cells, depending on your needs.
The first step requires you to select the range of cells that the add-on will inspect, or you can let the add-on auto-select.
Second, you can choose what you are searching for. Selecting "Duplicates + 1st occurrences" will find all duplicate cells, while selecting "Duplicates" will only find the duplicate instances while ignoring the first appearance of a certain string.
Finally, you can select what the add-on will do when it finds these cells. Select "Fill with color," then use the drop down menu to choose a color.
Press Finish, and the add-on will complete its task flawlessly!
Check for Duplicates Using UNIQUE
The UNIQUE formula can be helpful for finding duplicates in a relatively small data. The function works by returning all unique values from a range of cells. You can read our full tutorial here:
Google Sheets UNIQUE Function [Ultimate Guide]
While this method does not highlight duplicates in Google Sheets, you can easily identify duplicate values using this method and a small amount of data.
Potential Problems and Easy Solutions
Extra Spaces
Do you have any extra spaces before, after, or between your cell contents?
Any extra spaces in a Google Sheets cell will throw off this method because the contents of a cell will not match.
By removing any miscellaneous spaces using the remove whitespace tool in Google Sheets (Data->Data cleanup->Remove whitespace), you can prevent this problem from ruining your hard work.
Incorrect reference
Absolute references, relative references, mixed references. They can get very confusing at times, but Google Sheets references are essential for correctly implementing this method to highlight duplicates in Google Sheets.
Check and make sure that the format of your cell reference matches what we have in our tutorial. Often, an extra or missing "$" can mess up your whole formula.
Highlight Duplicates in Google Sheets FAQS
Editing or Deleting Conditional Formatting Rules
Sometimes you just want to find the duplicates without highlighting them. You can easily remove a conditional formatting rule by clicking the trash icon in the sidebar.
Can I group duplicates?
This tutorial mainly covers how to highlight the duplicates with conditional formatting rather than group them. To aggregate duplicate values, we recommend using the SORT function or the filter, although these will not allow you to apply conditional formatting. You can read our articles about these topics below:
How to Custom Sort in Google Sheets (Best Method 2022)
How to Use Google Sheets Slicer: Filtering Data
How do I compare duplicates across multiple spreadsheets?
The easiest way to compare two separate spreadsheets would be to consolidate them into multiple sheets of one workbook.
From there, you can use sheet references (=Sheet1!) in the COUNTIF formula to find duplicates across multiple sheets.
How to remove duplicates in Google Sheets?
While this tutorial only demonstrates how to highlight duplicates in Google Sheets, sometimes you might want to remove duplicates. This could be part of a data munging process or another process of beautifying your data.
Luckily, we have written another article explicitly about removing duplicates that you can read below:
How to Remove Duplicates in Google Sheets
The simple answer involves selecting your data range in Google Sheets, clicking Data-> Data cleanup -> Remove duplicates.
Conclusion
Congratulations! You are one step closer to mastering Google Sheets. Highlighting duplicates in Google Sheets with conditional formatting and COUNTIF can be difficult, but keep practicing and soon it will be a breeze!
To help you practice, click this link to access our sample sheet to find duplicates in Google Sheets.
Recommended articles
More resources
