How to Apply Conditional Formatting to Find Duplicates in Google Sheets

How to use conditional formatting duplicates google sheets 11

In this article we will learn about How to use Conditional Formatting Duplicates in Google Sheets and its use cases.

What is Conditional Formatting Duplicates Google Sheets?

Conditional Formatting provides an easy way to automatically format the Google Sheets by applying a formula or condition to be satisfied in order to activate Conditional Formatting. Conditional Formatting has a special feature using which we can find duplicates (duplicates of value of cells) in Google Sheets.

Sometimes we may face a problem where we have to find out the values which are duplicates of each other in a Google Sheet. Finding the duplicates manually may take hours. Here, in order to solve such problem with a few clicks, Google Sheets allow us to use Conditional Formatting Duplicates Google Sheets method. In this article, we will provide an easy method to use Conditional Formatting Duplicates Google Sheets.

Why we use Conditional Formatting Duplicates Google Sheets?

Sometimes, while working with Google Sheets, we may be dealing with a large amount of data set and required to find its validity in the terms of uniqueness. We are mostly dealing with large amounts of data and that data is input very carefully later it is not possible to check for uniqueness in a data set of thousands.

So, in these types of cases, we might want to highlight the cells which contain same duplicate values to throw them out of our data set to make our data set more valid and unique in terms of values. So that our results are only based on actual real values and not duplicates.

See also  Conditional Formatting based on Another Cell in Google Sheets

These may include finding duplicates in Full Names, ID Number, Driving License Number, Phone, Address, Citizenship Number, or email, etc. Google Sheets allows us to use Conditional Formatting Duplicates Google Sheets to find duplicate values in sheets. Using the method for Go Conditional Formatting Duplicates Google Sheets, we can find out those cells which contain duplicate values and exterminate them and work only on actual values.

Download/Copy Practice Workbook

How to use Conditional Formatting Duplicates Google Sheets?

Let us take an example of a Google Sheet where Response List upon a Skin Product is provided. Here, the example contains only a few cells for demonstration. Actually, the data of responses may exceed hundreds of responses and then, it will become particularly difficult to find out the cells containing duplicate values and exclude those responses from response list as they are fake responses. Here we demonstrate the method on the sheet as below:

How to use conditional formatting duplicates google sheets 1

Now, we have to highlight the cells containing duplicate values in email column to exclude them.

Let us apply the Conditional Formatting Duplicates Google Sheets as following:

Step 1:

Select the cells where you want to apply the Conditional Formatting.

In this case, we need to apply the Conditional Formatting to the Email Column.

See also  How to Copy Conditional Formatting in Google Sheets (2 Methods)

We can select manually by dragging the mouse over the required cells as:

How to use conditional formatting duplicates google sheets 2

OR

We can type in the range of cells to conditionally format in “Name Box” as below:

How to use conditional formatting duplicates google sheets 3

As we select the cells, we see that the selected cells are highlighted in light blue showing selection.

Step 2:

Next step is to apply Conditional Formatting.

As the cells are selected, we can apply conditional formatting using any of the 2 methods below:

  • Method 1:
    • Right Click anywhere on the selected cells. A sub-menu will appear. Choose “View more cell actions” and then Conditional Formatting as shown below:
    • Right Click -> View More Cell Actions -> Conditional Formatting

How to use conditional formatting duplicates google sheets 4

  • Method 2:
    • From the Format toolbar, choose Conditional Formatting as shown below:
    • Format -> Conditional Formatting

How to use conditional formatting duplicates google sheets 5

As we click on Conditional Formatting, Conditional Formatting Rules Box will appear as:

How to use conditional formatting duplicates google sheets 6

 

Step 3:

Set the condition that is required to activate the conditional formatting.

Here, we need to highlight the cells which are duplicates of each other. So, we apply the condition as a custom formula.

Apply custom formula by selecting the Custom Formula Is under the Format Rules section as:

How to use conditional formatting duplicates google sheets 7

Now type the formula in the Formula or Value Box in Format Rules section. In this case, the condition is to find out which cells are duplicates, so our formula here is =COUNTIF($E$2:$E$13,E2)>1. We apply it as:

How to use conditional formatting duplicates google sheets 8

As soon as we apply the custom formula, we begin to see changes in our sheet.

See also  How to Apply Conditional Formatting Lowest and Highest Value in Google Sheets

Step 4:

Condition for Conditional Formatting is set, now is the time to set the “Format” for Conditional Formatting. We can set our required format in Formatting Style section in Conditional Format Rule Box as:

How to use conditional formatting duplicates google sheets 9

 

Step 5:

Click “Done” to submit the conditional formatting has been applied.

How to use conditional formatting duplicates google sheets 10

We can see the conditional formatting has been applied as:

How to use conditional formatting duplicates google sheets 11

We could also cancel the conditional formatting if we do not require this formatting by clicking cancel button.

We know from the above results that out of 12 responsive, 6 responses are fake and should be removed from the product reviews list.

Notes

We used the formula “=COUNTIF($E$2:$E$13,E2)>1” in this example. It can be changed as per requirements of the problem scenario. Remember that this formula contains absolute cell numbers and hence cannot be carelessly copy-pasted. Or copying conditional formatting with this formula would not be useful. So the user has to specify the range for the cells where duplicates are required to be found out very carefully specifying the absolute cell numbers.

Conclusion

Applying Conditional Formatting Duplicates Google Sheets allows us to find duplicate cells in large sheets which enables us to clear duplicates and find unique values in our sheets and save the data set from duplication and false values. In this article, easy step-by-step method for Conditional Formatting Duplicates Google Sheets is described. Any queries are most welcomed. Feel free to comment below for any questions.

Thanks for Reading!

Content Protection by DMCA.com

Widad Khan

Hi, I am Widad Khan. I was born and raised in Pakistan. I have always loved the field of Computer Sciences and since I was a kid, I would say I wanna grow up to be a scientist, now this term is mostly related to "Computer Scientist" and not scientist alone. I am pursuing my Bachelor Degree in Computer Sciences from GCU, Lahore. I have always loved keeping records of different types of data. Earlier, I would use MS Excel for that. But with the advancements in Google Sheets, my work has become much easier. And also it's very easy to share and coordinate data with fellows and coordinates.

OfficeDemy.com
Logo
Enable registration in settings - general