Highlight Duplicates in Google Sheets [Step by Step Guide]

If you come across a large dataset in Google Sheets (or Excel), the likelihood of duplication is possible. However, before removing duplicates, you may want to see, record, and/or compare the duplicated data. You can find duplicates by highlighting all the duplications in your Google Sheets dataset.

In this article we will guide you how to highlight duplicates in a quick and easy way.

Why Should You Highlight Duplicates?

Even though this depends on you, the main reason to highlight duplicates is to record and compare data. This can help you look for errors in the data source depending on what data is being redundant. Highlighting duplicates also helps you analyze data such as online purchase lists and inventory lists.

How to Highlight Duplicates in Google Sheets?

Conditional Formatting in Google Sheets

Conditional Formatting is the easiest and most flexible way to manipulate your Google Sheets. As the name suggests, it gives you increased autonomy over your Google Sheets to manipulate in your own way, according to your own needs. For example, color coding cells, differentiating data based on values, comparing data values, and much more! 

Highlighting Duplicates in Google Sheets

Let’s use Conditional Formatting to highlight duplicates in our sheet. 

There is no restriction on the template of your sheets or the format of your data.

Step 1: Open your Google Sheets file. 

List with Duplicate Entries

Step 2: To check which items are repeating, right-click on the desired column and select Conditional Formatting.

The range will be automatically set to the whole column this way, but you can change the range in the Apply To Range box.

To highlight the duplicates, we will have to use a formula, i.e.

=COUNTIF([column]:[column],[startingCell])>DuplicateCount

Step 3: To add your custom formula, click on “Custom Formula is” from the Format Rules > Format Cells if dropdown menu:

Conditional Format Rules Tab

Step 4: Now add the formula in the Value or formula textbox.

In the case above, the formula will be =COUNTIF(A:A,A1)>1

Step 5: Select your desired color. 

Step 6: Click Done after you add the formula to save this custom format.

You can change the DuplicateCount in the formula according to what you need, here we have used 1 meaning this will show all records appearing more than once. If i had used, say, 5 then it would have shown all the records appearing more than 5 times. This is just another way you can use conditional formatting to fetch more information about your data and analyze data values.

Remove a Formula in Google Sheets

If you want to remove a formula you have used in Conditional Formatting, just open the Conditional Formatting window by right-clicking on the column and selecting Conditional Formatting. All your formulae will appear on the right. To delete a formula, hover on the formula and a dustbin icon will appear – click on this icon to remove the formula.

Errors in Highlighting Duplicates

Conditional Formatting can also result in erroneous filtering, based on user errors. Here is a list of potential errors you might be making:

1. Range Value and Formula Starting Row Values are mismatched. 

Mismatching Row Values

2. Shifted data down or up a row without updating the Range value.
3. Cut and Pasted data from one column to another without updating the Range value and the formula values. 

Now if you want to remove these duplicates, you can check our guide on how to Remove Duplicates instead of deleting each value manually.