Google sheets Highlight Duplicates in Multiple Columns

Many people are working on Google sheets nowadays. And ones who have experience in working on Google sheets must have experienced with an issue of redundancy. It technically means duplication of data. And its removal is one of the most challenging tasks.

It can be of multiple forms, like duplicate data in a row, a column or even numerous rows or column.

There are multiple methods to choose from; one of them is conditional formatting. And after highlighting, it depends on you, whether you want to keep them or delete them.

In this article, you will be provided with a step by step process to highlight duplicates in Google sheets.

Highlight duplicates in Multiple columns


Step 1: First, you should select the names dataset but remember, not to choose the headers.

Step 2: In the pop up options, select Conditional Formatting.

Step 3: Now, you have to click on the ‘Add another Rule’ option that shows.

Step 4: Now, you must be positive of the fact that the range you selected is correct. It is the area where you want to highlight the duplicates. If this is not correct, you can change that in the ‘Apply to range’ section.

Step 5: Now, click on the option ‘Format Cells if’ dropdown menu, and then click ‘Custom formula is’ option from the dropdown.

Step 6: In the field that arises, you need to enter the below-provided formula: =COUNTIF($A$2:$C$10, A2)>1

Step 7: Now, in the ‘Formatting Style’ options, you must specify the type of formatting in which you require highlighting of the duplicate cells. By default setting, the identical cells will be highlighted in the green shade, but you are free to do your creativity in choosing colour and font style (bold and italics).

Step 8: Finally, you need to click on ‘Done’.

The steps described above will highlight the cells in which there is the duplicity of names, multiple times.

But, how does this COUNTIF formula works?

While using the COUNTIF formula, what we do is, we covered here the cells within three columns. And the procedure checks each section with a sole question of being true or false. And if there is any repetition, detected, then it will be highlighted with a specific format.

Here in the example, we used the range $A$2:$C$10, where we have placed a dollar sign before both, the column alphabet and the row number.

Selection and writing the correct order of range is essential as it makes sure that the content selected is the same when the conditional formatting takes place and keeps track of counting the names written in a particular cell.

Techbosspro’s Take:

Although it can be challenging to see duplicity in data and removal takes a lot of time, the simple steps depicted above can help you highlight them effectively and then the decision remains yours. Whether you desire to keep them in data set or delete them

Hope, this article helped you in doing the task you wanted to do. And if this article helped you do write them in the comments section below.

Recommended for You:

Leave a Comment