Highlight Duplicates in Google Sheets [Step by Step Guide]

google sheets

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. 

Purchase Items Duplicate Entries
List with Duplicate Entries

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

Highlight duplicates in Google Sheets

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:

nuiKB6GXSyjfENW90S6Kn7c4H DxNLZa iyD26IAfAqk3qPnDVqH4FYjeIpO7sUw3w6y87H73Xb4accrXaZd6YfcyFGtkVO7m4X jo2NMmVKpbWjEuyBqizDmtiCJgfj3 Qm0gc
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

WfXaB6nim3ttMKnRmIb2IXFvlgV5lR5MsBDI1g8uay9Wy5MX0o3PK 5QV24m mSJHiTnbNdOSyYa 1y58numGaE4DK7UkSEh XWSps L2U3wp6dqhTXbBu4Ffywe57zjzCvcnLIm

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.

J1W5HCNB74Sn8XZtPqovNN5yngE8JYBLEVxNfwiOCTcHQWRV x0wg5 Vr5Jyqf0tBVeXZAtudQ2GtE0ZDV6Jp1uEcHQGHmKldaXU8IvmiYChp6HVNbC31DlQ0 MXW4b XjVk gWL

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. 


u5xdF9aZFh5nGO f9bxMLGKm KWqeUKEWVmC9 NZAAcFLkOa u7IJ8YnAmN3QQVOr2kxhG78p9ek OY5rq Wj49jwK1Bb7VfGsxwuK0xLzAkNDWWeYGvongn UnZJwdhVs7RsOC
Mismatching Row Values

  1. Shifted data down or up a row without updating the Range value.
  2. 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.

Leave a Reply