If you work with datasets, you often come in situations to find and highlight duplicate values. Until now, at the time of writing this blog, in Google Sheets, there is no direct feature available for this but yes you do it using conditional formatting.
In this tutorial, we will explain to you how to highlight the duplicate values in Google sheets using a COUNTIF formula in multiple ways within the conditional formatting.
Highlight Duplicate Values within a Column in Google Sheets
To highlight the duplicate values within a single column you have to specify the COUNTIF formula accordingly within the conditional formatting. To do so follow the below steps.
- First, select the entire column except the header, within which you want to highlight the duplicate values.
- After that, click on the “Format” tab from the menu and then click on the “Conditional Formatting” option from the drop-down menu.
- Once you click on the “Conditional Formatting” option, it will open the “Conditional format rules” dialog box.
- Here, first, see if the applied range is correct or not, and then click on “Formula rules” and then scroll down and click on the “Custom formula is” option.
- Now, enter the COUNTIF formula in the “Custom formula is” field to find the duplicate values within the column.
=COUNTIF($A$2:$A$1000,A2)>1
- Once done, choose the formatting color and or style to highlight the duplicate values from the “Formatting Style” options.
- In the end, choose the color and click “Done” and Google Sheets will highlight the duplicate values in that selected range.
How COUNTIF Formula Works for Duplicates in Single Columns
To understand how the COUNTIF formula works to find duplicate values within a column, we will use the above used “COUNTIF” formula.
=COUNTIF($A$2:$A$1000,A2)>1
- In the first part of the formula “COUNTIF” checks the range ($A$2:$A$1000) that we have defined by adding the $ sign to make sure that the reference will remain the same and unchanged when the formula applies to all the cells.
- In the second part of the formula, we have defined the criteria which need to be searched as duplicates in the given range.
- In the third part, we have used greater than one “>1” to find if any value exists more than one within the given reference. If it finds a duplicate value, the formula will give TRUE as a result of the formula for that value, based on which that value will get highlighted.
Highlight Duplicate Values within multiple Columns in Google Sheets
- First, select the data range, within which you want to highlight the duplicate values.
- After that go to the “Format” tab and then click on the “Conditional Formatting” option.
- Here, first see if the selected range is correct, after that click on “Format rules” and then scroll down and click on the “Custom formula is” option.
- Now, enter the COUNTIF formula in the “Custom formula is” field to find the duplicate values within the selected range having multiple columns.
=COUNTIF($B$2:$D$10,B2)>1
- Once done, choose the formatting color and or style to highlight the duplicate values from the “Formatting Style” options and click “Done”.
- At this point, the duplicate values within the selected data set will get highlighted.
How COUNTIF Formula Works for Duplicates in Multiple Columns
To understand how the COUNTIF formula works to find duplicate values within multiple columns, we will use the above used “COUNTIF” formula.
=COUNTIF($B$2:$D$10,B2)>1
The formula is almost the same for single columns or multiple-column duplicates. The difference only is that to use “COUNTIF” to highlight duplicates in multiple columns, you need to provide the cell reference of that multiple columns.
- In the first part of the formula “COUNTIF” checks the range ($B$2:$D$10) that we have defined of three columns (B to D) by adding the $ sign to make sure that the reference will remain the same and unchanged when the formula applies to all the cells.
- In the second part of the formula, we have defined the criteria which need to be searched as duplicate and we did not use the $ sign so that criteria will get changed and updated according to the cells name.
- In the third part, we have used greater than one “>1” to find if any value exists more than one within the given reference. If it finds a duplicate value, the formula will give TRUE as a result of the formula for that value, based on which that value will get highlighted.
Highlight Duplicate Rows in Google Sheets
Assume that you have a dataset where you have repeating values within the different cells but you want to find and highlight the duplicate rows only. For this, you have to write the “COUNTIF” formula in that way so that it will find and highlight only the similar rows instead of highlighting the repeating values within different cells as shown in the above formulas.
- First, select the data range and open the “Conditional Formatting” option.
- After that, click on “Format rules” and then scroll down and click on the “Custom formula is” option.
- Now, enter the following COUNTIF formula to find the duplicate rows and then choose the highlighting style and color of the duplicate rows and click “Done”.
=COUNTIF(ARRAYFORMULA($A$2:$A$11&$B$2:$B$11&$C$2:$C$11),$A2&$B2&$C2)>1
How COUNTIF Formula Works for Duplicates Rows
To find the duplicate rows we have combined the “COUNTIF” and the “ARRAYFORMULA” together.
=COUNTIF(ARRAYFORMULA($A$2:$A$11&$B$2:$B$11&$C$2:$C$11),$A2&$B2&$C2)>1
- In the first part of the formula, we have added the “ARRAYFORMULA” and provided the multiple columns reference so that “ARRAYFORMULA” creates the array string which combines the values of all the cells within the same row into a single combined value for each row.
- In the second part of the formula, we have given the concatenated condition “$A2&$B2&$C2” for “COUNTIF” to find the combined condition only and here the “COUNTIF” with “ARRAYFORMULA” uses the “ARRAYFORMULA” result as single cell reference and finds the concatenated condition in it.
- In the third part, we have used greater than one “>1” to find if the concatenated value exists more than one within the given reference.
Highlight Duplicate Values with Additional Criteria
Suppose you have the date as below in which you have usernames with their expertise zones and their employee codes and there are different users with the same names, and same users with multiple expertise, and here you only want to highlight if a unique person has multiple expertise. So, in this case, you have to apply the “COUNTIF” with an additional condition using the “*” syntax to highlight the same person repeating multiple times.
- First, select the data range except the headers and go to the “Format” tab and click on the “Conditional Formatting” option to open it.
- After that, click on “Format rules” and then scroll down and click on the “Custom formula is” option.
- Now enter the following “COUNTIF” formula with the added condition to highlight only the duplicates which match the added condition and select the highlighting color and click “Done”.
=(COUNTIF($A$2:$C$11,$A2)>1)*(COUNTIF($A$2:$C$11,$C2)>1)
How COUNTIF Formula Works with Added Criteria to Highlight Duplicates
This formula works the same as finding the duplicates in a single column but with the added condition.
- In the first part, “COUNTIF” finds if any value of the given criteria “column C” cells repeat more than one in the given range.
- In the second part, “COUNTIF” finds if any value of the given criteria “column A” cells repeat with the given range.
- In the third part, the “*” syntax gets used as the (and) operator which creates and adds one more condition for “COUNTIF” to give the result.
Points to Remember
- Conditional formatting makes the formula dynamic, which means with the change in the values, the highlighted formatting will also get updated.
- You can add more than one condition with the “COUNTIF” formula to highlight the duplicate values.
- Sometimes some repeating values do not get highlighted because of having extra space in some of them so it is better to first use the “TRIM” function to remove any extra space from the values.