How to Highlight Duplicates in Google Sheets?
The shortest answer is to count values in a column(s) as we do in running count. Then apply the fill color or text color based on the count. So for the former, we can use COUNTIF and the latter, the Format menu > Conditional formatting.
This post covers the below conditional formatting tips.
Formula to Highlight Duplicates in a Single Column in Google Sheets.
- All the occurrences of the duplicate values in a single column.
- All the occurrences, except for the first instance.
Formula to Highlight Duplicates in Multiple Columns in Google Sheets.
- All the occurrences of the duplicates in two (or more) columns.
- All the duplicates in two or more columns except for the first occurrence/instance.
The efficient way to identify duplicates in Google Sheets is by highlighting cells using the conditional formatting feature, which is built-in.
But to get the maximum output from this built-in conditional formatting feature, you should know how to use custom formulas in it.
As a side note, the best option to eliminate duplicate values is to use formulas. Google Sheets has two main functions to eliminate duplicates. You may be familiar with one of them, and that’s UNIQUE. But surprisingly, there is one more function called SORTN.
But for conditional formatting, like in Excel, we can depend on the functions COUNTIF or COUNTIFS. The use of UNIQUE or SORTN may not be handy in highlighting duplicates.
[Update: Please check the new menu item Format > Remove Duplicates – How to Use Remove Duplicates Menu Command in Google Sheets.]
Below you can find a few different custom formulas based on COUNTIF and COUNTIFS to highlight duplicates in Google Sheets. No doubt, each of them has its role in highlighting cells.
First, understand how to set a custom rule in conditional formatting. Then we can go to the formulas.
Must Check: Learn All Count Functions in Google Sheets.
Custom Formulas in the Conditional Formatting Panel
Before going to the formulas (rules), we should know how to use them in Google Sheets. It’s not like what we see or do in Excel. So, first, let’s go to that.
To apply custom rules (formulas for highlighting), please follow the below instructions.
-
Go to the menu Format and select Conditional formatting there.
-
It will open a sidebar panel. In that panel, here are the settings to enter the custom formula that I will provide.
You can learn below the cell reference and formula to enter in the fields “Apply to range” and in the “Value or formula” field. I will explain that below.
You may choose the color you want to highlight under the formatting style (please refer to the image above).
Highlight Duplicates in a Single Column in Google Sheets
In each example, I am providing you with two formulas each. Choose the one that seems easy for you to understand.
All the Duplicates in a Column
See how to highlight values in a column automatically when it appears repeatedly. I mean more than once. I am not in favor of this sort of fill coloring.
Here the formula will highlight the first, second, third, etc., instances of any duplicate values.
Conditional Format Rule:
- Apply to Range: A2:A
- Value or Formula: =countif($A$2:$A,A2)>1
Duplicates Except for the First Instance
I am usually following this method to highlight duplicates in Google Sheets. In my personal experience, this is the most preferred method among the Spreadsheet users with whom I have acquainted.
In this, I am considering the first value as a unique value. So the formula will conditionally format the value (color the cell or text) from the second instance only.
Custom Formula Rule:
- Apply to Range: A2:A
- Value or Formula: =countif($A$2:$A2,A2)>1
The above formula one and two are almost similar. If you carefully check, you can see a minor difference in the cell reference that is in the COUNTIF range.
This method has one advantage. To delete duplicates, you only need to select the rows containing the highlighted cells and delete them.
Unfortunately, there is no custom filter rule to filter the cells by highlighting in Google Sheets.
For that, we can use the Filter by Fill Color feature as detailed here – Filter or Sort by Font or Cell Color in Google Sheets – Built-in Feature.
Highlight Duplicates in Multiple Columns In Google Sheets
A list may contain more than two columns, or we can say arrays (ranges).
If you want to include all the columns in a list, the above formulas won’t work for you. The reason is the function in use, i.e., COUNTIF.
All the Instances of Duplicates in Multiple Columns
Here I am going to use the COUNTIFS function since multiple columns are involved. Also, this is in line with formula # 1 above. Please take a close look at the GIF capture below.
As you can see, if any of the rows entirely/completely match, then the formula highlights those rows. That means the values in both columns should match.
Custom Formula Rule:
- Apply to Range: A2:B
- Value or Formula: =COUNTIFS($A$2:$A, $A2:$A, $B$2:$B, $B2:$B)>1
When you have more columns, you can easily add that in this COUNTIFS formula.
Below you can find one more example to highlight duplicates in Google Sheets. Here also I include multiple columns. But the occurrence of values matters here.
Duplicates in Multiple Columns Except for the First Instance
See the formula and the live screenshot (GIF capture). This formula is in line with my single column formula # 2 above.
- Apply to Range: A2:B
- Value or Formula: =COUNTIFS($A$2:$A2, $A2, $B$2:$B2, $B2)>1
Conditional Format Duplicates Across the Cells in Google Sheets
The formula will apply color to any value/cell that has repetition in any cell. I don’t usually follow this sort of rule to highlight duplicates in Google Sheets. But some of you may want such a type of conditional format rule.
This conditional formatting applies to an entire range.
- Apply to Range: A2:G
- Value or Formula: =COUNTIF($A$2:G,Indirect(Address(Row(),Column(),)))>1
In this formula, the criterion is replaced by the INDIRECT, ADDRESS, ROW, and COLUMN combination formula.
The COUNTIF syntax for Reference: COUNTIF(range, criterion)
The combination formula used as the criterion covers all the cells in the sheet individually in the custom formula rule.
Sample Sheet
Resources
- How to Remove Duplicates Without Any Plugin on Google Doc Spreadsheet.
- Remove Duplicate Rows Based on Selected Columns in Google Sheets.
- Google Sheets: How to Compare Two Tables and Remove Duplicates.
- How to Find Multiple Occurrences of Rows in Google Sheets.
- Google Sheets: Find All Duplicates in a Column and Delete the Row.
- Remove Duplicate Rows and Keep the Rows with Max Value in Google Sheets.
- How to Conditional Format Duplicates Across Sheet Tabs in Google Sheets.
- How to Filter Duplicates in Google Sheets and Delete.
- Highlight Partial Matching Duplicates in Google Sheets.
- Highlight Visible Duplicates in Google Sheets.
- Assign the Same Sequential Numbers to Duplicates in a List in Google Sheets.
- Make Duplicates to Unique by Assigning Extra Characters in Google Sheets.
- Merge Duplicate Rows in Google Sheets and Concatenate Values.
- Formula to Combine Duplicate Rows in Google Sheets.
For more interesting articles and tips on Google Sheets, visit Crawlan.com.
