How to Highlight Duplicates in Google Sheets

When dealing with a lot of data, you may sometimes want to double-check and make sure no duplicates exist—or, conversely, you may want to highlight any duplicates to call attention to them. In that case, you can use a straightforward formula in Google Sheets. Learn how to find duplicates in Google Sheets with step-by-step instructions.

Practice with Google Sheets: Highlight duplicates

To begin, you'll need your tab open to your spreadsheet. If you’re not already working with your own data set and want to follow along with our examples, make a copy of this template to practice. 

How to highlight duplicates in Google Sheets

Highlighting duplicates in Google Sheets requires conditional formatting using the custom formula =COUNTIF (A:A, A1)>1. Follow these steps to learn how to use it.  

How to find duplicates in Google Sheets with a browser add-on

TIP: If you’d rather not dive into formulas just yet, you can download an add-on from Google Sheets that will find and highlight duplicates for you.

1. Highlight the column you want to find duplicates in.

Using our practice sheet, see if the Director (1) column has any duplicates. 

2. Click 'Format' in the top menu.

3. Click 'Conditional formatting.'

This will populate a box on the right-hand side of the screen. You’ll see a prompt called “Format cells if…” Click on that and scroll to the bottom.

Learn more: How to Use Conditional Formatting in Google Sheets

4. In the 'Format cells if' box, click 'Custom formula is.'

5. Use the COUNTIF formula to find duplicates.

The COUNTIF formula [=COUNTIF (A:A, A1)>1] tells Sheets where to look for duplicates. The information in the parentheses represents the column you want to track and the specific cell you want to start with. The information outside the parentheses states that you want Sheets to count duplicates or anything appearing more than once (>1). 

Since you're looking for duplicate directors, you want to adjust the formula to read the C column. Your formula should become =COUNTIF (C:C, C2)>1. You can see how it begins to highlight repeat directors. 

How to count duplicates in multiple columns

Now that you know how to count duplicates in one column, learn how to adjust the process to count duplicates in multiple columns next.

Let’s say you want to check movie titles and directors, so columns B and C in this case. We’ve purposely added an error in the titles column, repeating Batman twice. Clear any previous conditional format rules, and repeat the steps above until you get to the box where you’ll input your custom formula. 

You can go about this in two ways: 

1. Use 'Apply to range.'

By highlighting the columns you want to check, you’ll automatically tell Apply to range what to concentrate on, but you’ll have to adjust your custom formula to start with the value of that first column and first row. 

For your purposes, you’re looking at columns B and C, so your function should be =COUNTIF(B:B, B1)>1. That tells Sheets to start with B1 and go from there. 

You can adjust the range in Apply to range as needed. Let’s say you were looking at columns B and C, but now you want to include columns B through F. Rather than clear the conditional formatting, highlight your new columns, and start over, you can simply update the “Apply to range” to read “B1:F999.” 

Make sure the syntax of your formula matches the first value. For example, if you want to look at columns C through F now, you’ll update “Apply to range” to “C1:F999” and then make sure the function reads =COUNTIF(C:C, C1)>1. 

2. Use absolute values. 

Absolute values are a way to specify where Sheets should look for duplicates with the “$” symbol. You’ll need to frame every cell with a “$.” Our function becomes =COUNTIF ($B$1:$C$50, B1)>1. 

Learn more: Google Sheets vs. Excel: What's the Difference? 

How to highlight multiple columns using different colors

Performing these steps will highlight your duplicates using one color. But if you have multiple duplicates, you won't be able to see how many of each duplicate you have. 

In that case, you’d want to do a pivot table, which can help you see and better understand the relationship between data.

Explore further how to work with data.

Highlighting duplicates in Google Sheets is one way to filter and clean your data. Interested in strengthening your abilities to work with data using Google Sheets? Enroll in the Google Data Analytics Professional Certificate. You’ll learn more about spreadsheets and other key analysis tools. 

Build job-ready skills with a Coursera Plus subscription

Start 7-day free trial

• Get access to 10,000+ learning programs from world-class universities and companies, including Google, Yale, Salesforce, and more
• Try different courses and find your best fit at no additional cost
• Earn certificates for learning programs you complete
• A subscription price of $59/month, cancel anytime

Start 7-day free trial