Arrow left
Back to guides
Google Sheets Tips

How to Highlight Duplicates in Google Sheets

In this article, you will learn how to highlight duplicates with Conditional Formatting in Google Sheets. 

How to use Conditional Formatting to highlight duplicates in Google Sheets

  • Select a range or array you want to find and highlight the duplicates.

  • Go to the “Format”“Conditional formatting”.

  • Choose a range to which you apply Conditional Formatting.

  • Select “Custom formula is” and enter the COUNTIF formula such as “=COUNTIF(range, tested value)>1”.

  • Click “Done” at the bottom right.

Assume you look at some survey data and want to check if there are any duplications in the data.

First, look at the example below where you highlight the duplications in the Name column.

How to highlight duplicates in a list with Conditional Formatting in Google Sheets

The range should be data in the Name column, B3:B13, as you are looking for duplicates there. You use the COUNTIF formula to test each cell value from B3 to B13, such as “Tiger” and “Lion” and see how many values match the tested value in the selected range. If there is any duplication for a tested word, the COUNTIF formula returns “2” or above.

So, the “Format cells if…” section defined that if the number given by the COUNTIF for a tested cell is more than 1, the cell should be highlighted. As such, the range should be the absolute reference, whereas a tested cell should be the partial absolute reference. [Insert a link to an article about reference]

Save hours formatting your next financial spreadsheet!
100+ templates, all free to use.
Free Spreadsheet Templates

How do I find duplicates in multiple columns in Google Sheets?

If you want to spotlight duplicates across multiple columns, you need to tweak the range and the COUNTIF function, as shown below.

How to find duplicates in multiple columns in Google Sheets

There are three major changes:

(i) The range for the Conditional Formatting should be the entire list - B3:D13

(ii) The range for the COUNTIF function should be the entire list as well

(iii) The tested value in the COUNTIF function should be a relative reference, not a partial absolute reference because you need to test all values in the list.

If you want to highlight these duplicated values in different colors, you need to set up the Conditional Formatting rules - in this case, three rules - by columns and select different fill colors for each rule.

Check this article [A hyperlink to be inserted] if you are keen to learn how to use Conditional Formatting based on another cell value first. 

Turn Quickbooks Online data into a Google Sheets dashboard
100+ templates, update automatically, totally secure!
Book a Demo

Learn how to do this step-by-step in the video below 👇

Automate financial reporting with LiveFlow

Want to eliminate manual updates of your Excel & Google Sheets models?

Yes, show me how

Need help?

Our team is here to help you any time between 9am and 10pm EST.
Check Icon
Email us at: help@liveflow.io

Liked this article? Then you'll love the ones below