Having duplicates in your data could be somewhat a tiring thing because you will have to remove duplicates. Importing data could end up having more or less duplicates and then you need to remove them or make changes to them. Here you will get to know how Google spreadsheet highlight duplicates can be managed easily.
Google sheets could possibly have more duplicates as these sheets deal with bigger datasets. Undoubtedly, it must be annoying to find, highlight and then remove those duplicates one by one. Suppose you are having a list of email addresses and contact numbers of your customers, you will have to highlight duplicates in the spreadsheet to review and fix the data.
Talking about Microsoft Excel provides a conditional formatting option to deal with duplicates, whereas Google Sheets currently don’t provide any feature for this. However, you can do this by customizing a couple of formulas together to make a useful formula to execute this task.
Highlight Duplicate Cells in a Single Google Sheets Column
Suppose you are having only one column with repeated values:
Here we’ll be using conditional formatting that’s why you will have to understand this function first.
Open conditional formatting to highlight duplicate cells in one Google Sheets column and follow the steps given below:
Firstly, we’ll be applying this rule A2:A10 to the range of cells.
From the drop-down menu, choose the Custom formula with the condition. Enter the following formula:
=COUNTIF($A$2:$A$10, $A2)>1
Putting the dollar $ sign here in this formula is just to make sure that the formula could approach each cell from column A.
From the Formatting style, you may choose any color to highlight the duplicates.
Click on Done.
The COUNTIF formula will scan column A while helping in highlighting which value is repeated. As per your settings, you will see all the duplicate values are being highlighted.
Highlight Duplicates in Multiple Google Sheets Columns
Not only one column, but it could be more than one column having duplicate values.
So, the thing is how you would be able to scrutinize those repeated values from multiple columns. Well, once again we’ll take help from conditional formatting. Below are some useful steps to follow:
Choose A2:C10 as a range to color duplicate cell values.
Now, change the range for the Custom formula:
=COUNTIF($A$2:$C$10,A2)>1
Did you notice one thing in this formula?
We didn’t add the dollar sign $ with A2, it is because the formula can count all the values of each cell from the table rather than just from column A.
Now, choose one color of your choice in the Formatting style and press the Done button.
Unlike the above-mentioned syntax of COUNTIF, this formula can easily look into all the columns. It also helps in counting how many times a value is repeated in all the columns. If a value appears more than once, it will be highlighted.
Highlight the Entire Row if Duplicates are in One Column
In this case, we’ll get to know about the table having multiple values in all the columns. And the entire row is just a single entry:
Look at this image; you will see duplicates in column B. The pasta & condiment part appears more than once.
In such conditions, these entire rows could be treated as duplicates. And you might be needed to highlight them in the Google Sheets thoroughly. Now, make sure to set these values for the conditional formatting:
Follow the rule to the range A2:C10.
Below you will see the formula:
=COUNTIF($B$2:$B$10,$B2)>1
Using this syntax, you would be able to count each value from column B. The conditional formatting will help in highlighting the duplicates from column B as well as the related records in other columns.
Highlight Complete Row Duplicates in Spreadsheets
What happens if the entire row appears repeatedly having values from all columns in the table?
Here the tricky part is to check all columns having highlighted absolute duplicate rows in the Google sheet.
Applying this formula in Conditional formatting is as:
=COUNTIF(ArrayFormula($A$2:$A$10&$B$2:$B$10&$C$2:$C$10),$A2&$B2&$C2)>1
Let’s have some explanation to this syntax:
ArrayFormula ($A$2:$A$10&$B$2:$B$10&$C$2:$C$10) in this syntax can concatenate every 3 cells from a single row into one text string that may appear as:
SpaghettiPasta9-RQQ-24
Now, COUNTIFS handles each string from the first one $A2&$B2&$C2 to all the 9 strings.
If your data has more than one string (>1), all the duplicates will be highlighted.
Find Duplicates in Google Sheets with an Add-on
A Google add-on is available to use when needing to have Google spreadsheet highlight duplicates. With these add-ons, not only highlight but you can do a lot more with your duplicates. Such as you can even identify or even delete them, compare data, ignore header rows and copy or move the data to some other location by default.
