How to highlight duplicates in Google Sheets

Highlight duplicate cells in a single Google Sheets column

Let's start with the basic example. It is when you have just one column with repeated values:

Here's how to highlight duplicate cells in one Google Sheets column:

1. Go to Format > Conditional formatting in the Google Sheets menu:
2. You will see the Conditional format rules window with the Single color tab opened by default:
3. Select the range of cells where you want to highlight dupes (in the apply to range field) — A2:A10 in my example:
4. Under Format rules, pick Custom formula from the drop-down:
5. And enter the following formula:

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

   Note. Formulas in conditional formatting are dynamic. Hence, in this COUNTIF, I lock only A in A2 with a dollar sign. This way, the formula will handle each cell from column A. You will learn more about this trick with cell references in this article.
6. Select any color from the Formatting style to highlight those duplicates.
7. Click Done.

That COUNTIF formula will scan your column A and tell the rule which records appear more than once. All these duplicate cells will be colored according to your settings:

Highlight duplicates in multiple Google Sheets columns

It may happen that repeated values will be in more than one column:

How do you scan and highlight duplicates in all 3 Google Sheets columns then? Using the conditional formatting as well. The drill is the same as above with a few slight adjustments:

1. Select A2:C10 as a range to color repeated cells within
2. Change the range for Custom formula as well:

   =COUNTIF($A$2:$C$10,A2)>1
   This time, remove the dollar sign from A2. This will let the formula count all occurrences of each cell from the table, not just from column A.

   Tip. Read this article to learn more about relative, absolute, & mixed cell references.

3. Pick a color in the Formatting style section and hit Done

This COUNTIF scans all 3 columns and counts how many times each value appears in the whole range. If more than once, conditional formatting will highlight these duplicate cells in your Google Sheets table.

Highlight the entire row if duplicates are in one column

Next up is the case when your table contains different records in each column. But the entire row in this table is considered as a single entry, a single piece of information:

As you can see, there are duplicates in column B: pasta & condiment sections occur twice each.

In cases like this, you may want to treat these entire rows as duplicates. And you may need to highlight these duplicate rows in your Google spreadsheet altogether.

If that's exactly what you're here for, make sure to set these for your conditional formatting:

1. Apply the rule to the range A2:C10
2. And here's the formula:

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

This COUNTIF counts records from column B, well, in column B :) And then the conditional formatting rule highlights not just duplicates in column B, but the related records in other columns as well.

Highlight complete row duplicates in spreadsheets

Now, what if the entire row (with the same records in all columns) appears several times in your table?

How do you check all 3 columns through the table and highlight absolute duplicate rows in your Google sheet?

Using this formula in conditional formatting:

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

Let's break it down into pieces to understand how it works:

1. ArrayFormula($A$2:$A$10&$B$2:$B$10&$C$2:$C$10) concatenates every 3 cells from each row into one text string that looks like this: SpaghettiPasta9-RQQ-24

   Thus, in my example, there are 9 such strings — one per row.

2. Then COUNTIFS takes each string (starting from the first one: $A2&$B2&$C2) and looks for it among those 9 strings.
3. If there's more than one string (>1), these duplicates get highlighted.

Highlight actual duplicates — 2n, 3d, etc instances

Let's suppose you'd like to keep the 1st entries of duplicate rows intact and see all other occurrences if there are any.

With just one change in the formula, you'll be able to highlight these 'real' duplicate rows — not the first entries, but their 2nd, 3rd, 4th, etc instances.

So here's the formula I suggested right above for all duplicate rows:

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

And this is the formula you need to highlight only duplicate instances in Google Sheets:

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

Can you see the difference in the formula?

It's in the first COUNTIF argument:

$A$2:$A2&$B$2:$B2&$C$2:$C2

Instead of mentioning all rows like in the first formula, I use only the first cell of each column.

It lets each row to look only above to see if there are the same rows. If so, every current row will be treated as another instance or, in other words, as an actual duplicate that will be colored.

Formula-free way to highlight duplicates — Remove Duplicates add-on for Google Sheets

It's not a secret that any formula and conditional formatting require a learning curve. If you're not ready to devote your time to those, here's the easiest solution.

Remove Duplicates add-on for Google Sheets will highlight duplicates for you.

It takes just a few clicks on 4 steps. There's a separate step for each action so you won't get confused:

1. First, the add-on offers an intuitive way to select your data:
2. Our add-on knows how to highlight not only duplicates but also uniques. And there's an option to ignore 1st instances as well — all on step 2:
3. Next, you pick columns you'd like to check for duplicates:
4. And finally, the option to highlight found duplicates is just a radio button with a color palette on the last step:

Video: How to highlight duplicates in Google Sheets

This 1,5-minute video shows 3 quickest ways (with and without formulas) to find & highlight duplicates in Google Sheets. You will see how to color 1 column or entire rows based on duplicates, even automatically.

Practice spreadsheet

Highlight duplicates in Google Sheets - conditional formatting examples (make yourself a copy to practice)