How to Highlight Duplicates: Google Sheets Guide

This guide show how to highlight duplicates in Google Sheets. Note that you can do this in several ways, but the simplest is to use a custom formula in the conditional formatting menu. I’ll also show you how to highlight duplicates with the COUNTIF formula. Follow along below for the step-by-step highlighting guide, the syntax of the formula, and examples of how to use it.

Conditional Formatting to Highlight Duplicates in Google Sheets

1. Select the range.
2. Go to Format > Conditional formatting
3. Click +Add a rule
4. Set “Format cells if…” to “Custom formula is” and enter the duplicate check formula (shown below)
5. Click Done.

The COUNTIF Formula is the key to highlighting duplicates. You’ll just want to edit the parts of the function to get it to do what you want. Here’s the formula:

=COUNTIF(Range,Criteria)>1

Note that the formula has two specific things inside. You’ll want to list the range first, then the criteria you’re checking for. The “>1” symbol then counts the cell when there are at least two instances of your criteria. Does this make sense? You’re basically asking your spreadsheet to count cells if they show up more than one time. If they do, you’ll highlight the duplicate.

Practical Example: Free Spreadsheet Template

Sometimes, it helps to apply concepts directly. That’s why I made this example sheet. With it, you can follow along with my tutorial. Note that it has two sheet tabs, one for each example used in my guide on how to highlight duplicates. To use it, click the sheet link. If it doesn’t automatically prompt you to make your own copy, click File > Make a Copy.

Watch Video: How to Highlight Duplicates

Sometimes, it’s easier to watch than to read. That’s why I made this quick video on how to highlight duplicates. Note that I’ve also got this on my YouTube channel. Subscribe there for regular spreadsheet advice, formulas, and templates.

Highlight Duplicates in Google Sheets in a Single Column

Suppose you have a dataset as shown below, and you want to highlight all the duplicate data in column A.

To highlight duplicates with conditional formatting, follow the steps below. Remember, we’re going to use the COUNTIF function first. Then, we’ll tell our spreadsheet to conditionally highlight duplicated cells. Here’s the step-by-step breakdown.

1. Select the range. In the example case, we want to use A2:A15 (image 1, box).
   

   

2. Go to Format >Conditional formatting.
   

   

3. Click on the “+ Add another rule” option in the conditional formatting pane that opens. If it’s already open, you’ll find this option below the existing rules.
   

   

4. Set “Format cells if…” to “Custom formula is” and enter the duplicate check formula “=COUNTIF(Range,Criteria)>1” In this case, we’re using the formula:

   =COUNTIF($A$2:$A$15,A2)>1

   

5. Click Done.

Google Sheets is now highlighting duplicates in our grocery list. This is something I use when I have a long grocery list, so I don’t accidentally add the same item multiple times. Of course, you can use this for many reasons. You may want to highlight duplicates in a list of suggestions or form responses. Suppose, for example, you connect Google Forms and have responses sent to your spreadsheet. In that case, you may want to quickly see if anyone submits duplicate responses.

Note that using this method highlights all the instances of the duplicate cell. So if an item repeats twice or thrice, all the cells that have this item/text will be highlighted. The COUNTIF function used in conditional formatting counts how many times a cell text string appears in the list. If it’s more than 1, the formula returns TRUE, and those cells are highlighted.

Note: Use the “Formatting style” section to change the highlighting color and font style. This is very helpful if you need to highlight more than one fill color at a time.

How to Highlight Duplicates in Multiple Google Sheets Columns

When working with larger spreadsheets with many columns, you will likely want to highlight the entire column when there’s a duplicate instead of just the cell.

This makes it easier to have Google Sheets find duplicates when the duplicated cell isn’t on the screen.

This process differs in two ways:

• The range needs to include all rows (A2:A15 is now A2:F15).
• The criteria section needs to use an absolute value for the column (A2 is now $A2).

Let’s use our grocery list from before, but now we’ve added where you find each product in the store as a new column (as shown below).

Follow these steps to highlight the entire row for duplicate cells in one column:

1. Select the range of all columns and rows you want to highlight. In the example, select A2:B15 instead of A2:A15
   

   

2. Open the “Conditional Formatting” feature and check the new range (image 2, top box).
3. In the “Conditional Formatting” tab, set the “Format rules” to “Custom formula is…”
4. Enter the duplicate-finding formula with the criteria adjusted for an absolute column. This means adding a dollar sign “$” before “A2” in our example. So “=COUNTIF($A$2:$A$15,A2)>1” is now “=COUNTIF($A$2:$A$15,$A2)>1”.

4. Hit “Done” to apply the new formatting (image 2, bottom box).

Now we’ve highlighted the entire row range for columns with duplicates.

How to Highlight the Entire Row if Duplicates Are in One Column

Let’s say we wanted to highlight an entire row if there are duplicates in column B. In our example below, we could apply the following formula to the conditional formatting menu to achieve this:

=COUNTIF($B$2:$B$7,$B2)>1

This indicates the conditional formatting should highlight the entire row if Google Sheets shows duplicates in the range $B$2:$B$7.

How to Highlight Duplicates But Ignore the First Instance

As I mentioned earlier, when you use conditional formatting to highlight duplicates in Google Sheets, it will highlight all the instances of duplicate cells. But what if you want to highlight only the duplicate instances (and not the first time it appears in the list)?

You can adjust the formula so Google Sheets will only highlight the second or later instance of a duplicate by making a tweak to the formula. Suppose you have the dataset as shown below, and you want to highlight only the cells that have the duplicate item.

We need to make a tweak to our old “=COUNTIF($A$2:$A$15,$A2)>1” formula (this is the one we used in the previous section).

• Adjust the ending range value from $A$15 to $A2. Now that we’ve set the column to absolute in the ending, but now the row, each row will only look at rows above itself for duplicates.
• Our new formula will look like this:

=COUNTIF($A$2:$A2,$A2)>1

In our example, this change to the formula (arrow) now only highlights the second instance in which our duplicates appear (box).

How to Highlight Complete Row Duplicates in Google Sheets

Now instead, let’s pretend you only want to highlight the row if every column in the table is a complete match. We can do this by using the ARRAYFORMULA function to concatenate the data into one string before applying the COUNTIF function.

This will apply Google Sheets conditional formatting for duplicates to the appropriate cells. I used the following formula in the example below to get the desired results. You just have to change the cell references to match your table.

=COUNTIF(ArrayFormula($A$2:$A$9&$B$2:$B$9&$C$2:$C$9),$A2&$B2&$C2)>1

A Shortcut to Remove All Duplicates

You know how much I love spreadsheet shortcuts. Fortunately, Google Sheets has a handy tool for removing duplicates. Note that this process gets ride of duplicate data instead of highlighting it. Below, I’ll continue with my grocery list example. It includes product location data from before.

Here’s how to find duplicates in Google Sheets with this tool:

1. Select the cell range you want to analyze.
2. Open the “Data” menu.
3.  Go to Data Cleanup > “Remove Duplicates”.

4. From the “Remove duplicates” pop-up.
5. check “Data has header row” if the data has this feature
6. Choose the columns you want to check for duplicate values from the checklist. In our example, we only want to compare column A values.
7. Choose “Remove duplicates” to apply the change.
   

   

The spreadsheet now only has rows without duplicate cells from our selection.

Google Sheets conditional formatting duplicates by highlighting also helps to show duplicates in Google Sheets so that you can remove the ones you don’t need at your own discretion.

Highlight Exact Duplicates – 2nd, 3rd, etc Instances

It is also possible to use conditional formatting to highlight specific instances of the duplicated word. Let’s say in our example below, we want to highlight the items that appear more than once without highlighting the first instance they appear.

We would:

1. Select our range
2. Go to Format > Conditional formatting
3. Click +Add a rule
4. Set “Format cells if…” to “Custom formula is” and enter the duplicate check formula =COUNTIF(ArrayFormula($A$2:$A2&$B$2:$B2),$A2&$B2)>1
5. Choose a color scheme, then click Done.

With that formula, we will have our duplicates highlighted only from the second instances going forward, like below.

Highlight Duplicates with Added Criteria

Google Sheets can check for conditional highlighting with additional criteria. You can set the program to highlight only rows with duplicate data in multiple columns or only duplicates with specific values.

The formula needs to use the “*” (and) operator to apply both conditions. An example formula to find duplicates in Google Sheets with more criteria would look like this:

=(COUNTIF(Range,Criteria)>1) * (New Condition) )

For example, we can have our grocery list examples from before only highlight duplicate products that are fruits or vegetables. We don’t care about products that appear twice in the bakery or any of the aisles.

Our spreadsheet now features lists of all the different fruits and vegetables available at the store:

In our example, we want to highlight the values in columns C and D if a row value appears twice in column C and at all in columns A and B.

Our formula construction will look like this:

• Start with our old formula, now modified to check column C instead of A: =COUNTIF($C$2:$C$15,$C2)>1
• Wrap the formula in parenthesis to add the second condition: =(COUNTIF($C$2:$C$15,$C2)>1))
• Add the “and/*” operator and the second set of parentheses for the second condition:

=(COUNTIF($C$2:$C$15,$C2)>1)*())

• Our second condition checks if the match appears in columns A and B, so it will look a little different. Instead of checking for “greater than 1” it will check for “greater than 0” and look like this: COUNTIF($A$2:$B$15,$C2)>0
• Our new formula will look like this:

=(COUNTIF($C$2:$C$15,$C2)>1)*(COUNTIF($A$2:$B$15,$C2)>0)

Applying the new range and new formula to our data range will highlight all the fruits and vegetables that are duplicates. It will not highlight the bagels which appear twice because they aren’t in the fruits or vegetable columns.

Some Tips When Highlighting Duplicates in Google Sheets

Here are some things to keep in mind when working on highlighting duplicates in Google Sheets:

How to Edit, Add or Delete Conditional Formatting Rules

To remove or edit conditional formatting rules, you simply have to:

1. Click any cell that formatting is currently applied to
2. Navigate to Format > Conditional formatting
3. Click the rule in the menu to make changes or click the trash icon to delete

Create a Unique Cells List (Instead of highlighting duplicates)

Sometimes going through the process of highlighting data to find duplicate rows isn’t the fastest way to find out what you’re looking for. If you just want to come up with a list of unique values to visualize how many duplicates you’re working with, the Unique function might be right for you.

To use this feature: enter “=unique(range start: range end)” in the cell you want to be at the top of your list.

In this grocery list example, we are entering “=unique(A2:A15)” at cell B2. When applied, it filters out the duplicates and populates all the cells below it necessary to come up with a unique values list.

Trim Whitespace to Curate Data

It’s common to encounter extra spaces before and after data when you’re importing information into Google Sheets from other sources.

For example, an email might come in as both “me@mysite.tld” and “me@mysite.tld “ with a space at the end of the second version. These are duplicates but won’t show up as such because that empty space confuses Google Sheets.

You can trim whitespace from your data with the following:

• Select the data you want to trim (left bßox).
• Open the “Data” drop menu (arrow).
• Choose “Trim whitespace” from the list.

If the selected data has any invisible whitespaces, Google Sheets will remove them. The data won’t look any different to you, but it is now different for data analysis.

Frequently Asked Questions

Here are some of the most common questions I hear about highlighting duplicates. I’ve done my best to answer as concisely as possible. Generally, these tend to relate to what formula to input and what conditional formatting option to choose. Please let me know if I missed anything!

Can Google Sheets highlight duplicates?

Yes, you can use the COUNTIF function and conditional formatting to highlight duplicate cells. You can use the same process (with the ARRAY formula) to highlight duplicate rows and duplicate columns. Check out my guide above for a more detailed explanation.

What’s the formula to highlight duplicates in Google Sheets?

You should combine the COUNTIF formula with the Conditional formatting menu to highlight duplicates. First, go to the “format” menu and choose conditional formatting. Then, choose to enter a custom formula. You’ll want to use a COUNTIF formula there, which checks whether the contents of a cell are duplicated.

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

You have to use absolute values as the cell references in the Custom formula inside the Conditional formatting menu. Here’s a screenshot of how to check for duplicates in Google Sheets across two columns.

How do I count duplicates only in Google Sheets?

To count duplicates only, you need to create a custom formula to count duplicates in the conditional formatting rule. In my above example, I use the formula =COUNTIF($A$2:$A2,$A2)>1 in the custom formula menu. It’s one of the built-in functions for the spreadsheet, which counts duplicates only.

How do I compare different Google spreadsheets for duplicates?

To highlight duplicate cells across sheets, you have to use a complex formula such as this one:

=AND(A2=INDIRECT("Sheet1!A2:A"),B2=INDIRECT("Sheet1!B2:B"), C2=INDIRECT("Sheet1!C2:C"))

I also have a full guide on finding duplicates across sheets on our sister site, Spreadsheet Point. There’s also a way to remove duplicates. I cover that on YouTube as well.

Is it possible to exclude blanks for duplicate highlights in the same row?

Yes, you can use a combination of the AND function and COUNTIFS to exclude blank cells. You’ll just want to format the custom formula like this:

=AND(COUNTIFS(B:B,B1)>1,B1"")

Note that the AND function ensures that the cells are only counted if there’s a duplicate and if the contents are non-blank cells. The non-blank cells are indicated by the “” in the formula.

Wrapping Up

Highlighting duplicates in Google Sheets is a valuable tool for making information easier for people to understand. There is an immense range of customization options to highlight duplicates in Google Sheets with multiple conditions.

I hope this tutorial helped you better understand how to check for unique data in your spreadsheet! You can also check out our guide to Google Sheets data validation.

Related:

• Google Sheets QUERY Function: A Beginner’s Guide
• Remove Last Character from a String in Google Sheets (or Last N Characters)
• Google Sheets Conditional Formatting Based on Another Cell
• The Ultimate 2024 Conditional Formatting Google Sheets Guide
• How to Zoom Out in Google Sheets (and Zoom in)
• Make a Google Sheets Heat Map in 2 Mins (Very Easy)
• Google Sheets Phone Number Format: How to Easily Format Phone Numbers in Google Sheets
• COUNTIF Not Blank in Google Sheets: An Easy 2024 Guide