Google Sheets Cheat Sheet

How To Highlight Duplicates In Google Sheets

  1. Highlight the columns in which you want to highlight duplicates
  2. Go to FormatConditional formatting in the main menu to bring up the Conditional format rules sidebar
  3. In the Format cells if… dropdown select Custom formula is and enter one of the formulas below
  4. Choose your desired Formatting style and click Done

Custom formula to highlight duplicate values in single cells:

=COUNTIF($A:$B, A1)>1

Custom formula to highlight duplicate rows:

=ArrayFormula(IF($A1&$B1<>"",COUNTIF($A:$A&"¦"&$B:$B,$A1&"¦"&$B1)>1))

You'll need to adjust the cell and range references to match your highlighted columns.

Keep reading for a detailed description about how each of these formulas works.

You can highlight duplicate entries in Google Sheets using conditional formatting to change the color of the cell.

Which do you need to do? Highlighting duplicates based on:

Highlighting Duplicates In One Column In Google Sheets

Duplicates are values that appear more than once.

This makes finding and highlighting them using conditional formatting relatively easy… with the right formula.

Conditional formatting is a BIG topic. I'm only going to touch on the relevant parts here.

Here's an example of a column with a duplicate entry:

A
1Salespeople
2Michael
3Jim
4Dwight
5Stanley
6Jim
7Phyllis
8Andy

What's a formula that counts if each entry occurs more than once?

How about the appropriately named:

=COUNTIF(range, criterion)

You can input the range of names from the example above and provide each individual name as a criterion:

ABC
1SalespeopleFormulaOutput
2Michael=COUNTIF(A2:A8, A2)1
3Jim=COUNTIF(A2:A8, A3)2
4Dwight=COUNTIF(A2:A8, A4)1
5Stanley=COUNTIF(A2:A8, A5)1
6Jim=COUNTIF(A2:A8, A6)2
7Phyllis=COUNTIF(A2:A8, A7)1
8Andy=COUNTIF(A2:A8, A8)1

Now you know which entries are duplicates, but this won't highlight them.

Why?

When designing a formula for conditional formatting you need the output to be TRUE or FALSE.

Work a logic test into your conditional formatting formulas to make sure of this.

You need to write a formula that uses a logic test to output either TRUE or FALSE.

What if you alter the current formula to test if its output is greater than 1:

ABC
1SalespeopleFormulaOutput
2Michael=COUNTIF(A2:A8, A2)>1FALSE
3Jim=COUNTIF(A2:A8, A3)>1TRUE
4Dwight=COUNTIF(A2:A8, A4)>1FALSE
5Stanley=COUNTIF(A2:A8, A5)>1FALSE
6Jim=COUNTIF(A2:A8, A6)>1TRUE
7Phyllis=COUNTIF(A2:A8, A7)>1FALSE
8Andy=COUNTIF(A2:A8, A8)>1FALSE

Now you're getting somewhere.

The final thing to think about is relative and absolute referencing.

You can only enter one custom formula for conditional formatting purposes.

This means you need to make it work as if the formula in the top-left corner of the selected range was being auto-filled to the rest of the range.

So, let's focus on just the first formula (which is the top-left in this example):

=COUNTIF(A2:A8, A2)>1

If this formula was auto-filled down it would change to:

=COUNTIF(A3:A9, A3)>1

The criterion changed as required but the range has changed to something that will no longer work as needed.

As such, you'll need to make this an absolute reference:

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

You can get around the need to consider absolute and relative referencing for a single column by:

=COUNTIF(A:A, A1)>1

Remember to change the column references to suit your needs.

For example, if you need to highlight in column B the formula would need to change to:

=COUNTIF(B:B, B1)>1

If you need to include more than one column (say A and B) the formula would need to change to:

=COUNTIF($A:$B, A1)>1

Where the range is all columns and the criterion is the top-left cell from those columns.

Notice that when you're working with multiple columns you must think about the reference's relativity and make the range absolute.

Now that you've got a formula, let's highlight the duplicates!

STEP 1: Highlight the column or range you want to highlight duplicates in:

shows how to select columns by clicking and dragging on column labels in google sheets

STEP 2: In the main menu, go to FormatConditional formatting to bring up the Conditional format rules sidebar:

shows the conditional formatting sidebar in google sheets and how to access it using the Format dropdown in the main menu

(You can also right click on the range OR click on the font color or background color icons in the toolbar and select Conditional formatting to get to the sidebar.)

STEP 3: Under Format rules in the Format cells if… dropdown select Custom formula is and enter the formula you've developed for your specific situation:

shows a custom formula entered into the conditional formatting sidebar using the format cells if dropdown

STEP 4: Choose your desired Formatting style and click Done:

shows where to input formatting style in the conditional formatting sidebar to highlight duplicates

Your duplicates will be highlighted:

A
1Salespeople
2Michael
3Jim
4Dwight
5Stanley
6Jim
7Phyllis
8Andy

Highlighting Duplicates In One Column Excluding The First Instance

Highlighting the second and subsequent instances of a duplicate can be useful to help remove duplicates while keeping one entry.

This requires a slight tweak to the range reference of the formula from above.

Here's the example again:

ABC
1SalespeopleFormulaOutput
2Michael=COUNTIF(A2:A8, A2)>1FALSE
3Jim=COUNTIF(A2:A8, A3)>1TRUE
4Dwight=COUNTIF(A2:A8, A4)>1FALSE
5Stanley=COUNTIF(A2:A8, A5)>1FALSE
6Jim=COUNTIF(A2:A8, A6)>1TRUE
7Phyllis=COUNTIF(A2:A8, A7)>1FALSE
8Andy=COUNTIF(A2:A8, A8)>1FALSE

This time you only want the second 'Jim' in row 6 to be highlighted (so only this formula should output TRUE).

This can be done by adjusting the range referenced in each COUNTIF function so that it refers only to the current and previous rows, not the subsequent rows.

This way, when the first instance is encountered the COUNTIF output will be 1 and when the second is encountered it will be 2.

Here's what that looks like:

ABC
1SalespeopleFormulaOutput
2Michael=COUNTIF(A2, A2)>1FALSE
3Jim=COUNTIF(A2:A3, A3)>1FALSE
4Dwight=COUNTIF(A2:A4, A4)>1FALSE
5Stanley=COUNTIF(A2:A5, A5)>1FALSE
6Jim=COUNTIF(A2:A6, A6)>1TRUE
7Phyllis=COUNTIF(A2:A7, A7)>1FALSE
8Andy=COUNTIF(A2:A8, A8)>1FALSE

Now you need to amend the top-left formula so that when it is auto-filled into the other cells by your conditional formatting it matches the other formulas.

The first formula is:

=COUNTIF(A2, A2)>1

In a formula the cell reference A2 is the same as the range reference A2:A2.

You can re-write this formula based on this tip:

=COUNTIF(A2:A2, A2)>1

Now you want the first cell in the range reference to remain as A2 while the second changes to match the current row.

Absolute referencing can achieve this:

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

If you wanted to do an entire column, simply change the A2 references to A1 (the top-left most cell):

=COUNTIF(A$1:A1, A1)>1

Remember to change the column references to suit your needs.

For example, if you need to highlight in column B the formula would need to change to:

=COUNTIF(B$1:B1, B1)>1

Now enter this formula into a conditional formatting custom formula rule by following the instructions previously provided.

FREE RESOURCE

Google Sheets Cheat Sheet

Learn 12 tips and tactics that will make your sheets more beautiful and user-friendly from today.

Google Sheets Cheat Sheet

By entering your email address you agree to get email updates from me. I'll respect your privacy and you can unsubscribe at any time.

Highlighting Duplicates In Multiple Columns In Google Sheets

Here's some data:

AB
1First nameLast name
2MichaelScott
3JimHalpert
4DwightSchrute
5StanleyHudson
6JimNixon
7PhyllisVance
8JimHalpert
9PamHalpert
10

The duplicate is 'Jim Halpert' in rows 3 and 8 but you can't rely on only the first name or last name as 'Jim Nixon' or 'Pam Halpert' would be highlighted incorrectly.

To highlight the duplicate correctly you need to combine the columns and then compare them:

ABCD
1First nameLast nameFormulaOutput
2MichaelScott=ArrayFormula(COUNTIF(A:A&B:B,A2&B2)>1)FALSE
3JimHalpert=ArrayFormula(COUNTIF(A:A&B:B,A3&B3)>1)TRUE
4DwightSchrute=ArrayFormula(COUNTIF(A:A&B:B,A4&B4)>1)FALSE
5StanleyHudson=ArrayFormula(COUNTIF(A:A&B:B,A5&B5)>1)FALSE
6JimNixon=ArrayFormula(COUNTIF(A:A&B:B,A6&B6)>1)FALSE
7PhyllisVance=ArrayFormula(COUNTIF(A:A&B:B,A7&B7)>1)FALSE
8JimHalpert=ArrayFormula(COUNTIF(A:A&B:B,A8&B8)>1)TRUE
9PamHalpert=ArrayFormula(COUNTIF(A:A&B:B,A9&B9)>1)FALSE
=ArrayFormula(COUNTIF(A:A&B:B,A10&B10)>1)TRUE

The COUNTIF function doesn't ordinarily accept arrays (like A:A&B:B) as a range.

As such, you will need to wrap your formula in an ArrayFormula function to make it work properly.

There are two problems with this formula:

  1. The comparison is error-prone
  2. Blank rows are outputting TRUE

Fixing The Comparison

When you concatenate text with the & operator the text is merged without a delimiter.

"Michael" and "Scott" becomes "MichaelScott".

However, "Mic" and "haelScott" also becomes "MichaelScott".

You need something in between so that these things are recognised as being different.

When I need to do this I like to use rare characters like ¦.

You probably haven't seen this character before because it almost never appears in text. That makes it perfect for delimiting text.

If you change your formula slightly to be:

=ArrayFormula(COUNTIF(A:A&"¦"&B:B,A2&"¦"&B2)>1)

The error above can't happen as the outputs would now be "Michael¦Scott" and "Mic¦haelScott" which are different.

Adding this in you get:

ABCD
1First nameLast nameFormulaOutput
2MichaelScott=ArrayFormula(COUNTIF(A:A&"¦"&B:B,A2&"¦"&B2)>1)FALSE
3JimHalpert=ArrayFormula(COUNTIF(A:A&"¦"&B:B,A3&"¦"&B3)>1)TRUE
4DwightSchrute=ArrayFormula(COUNTIF(A:A&"¦"&B:B,A4&"¦"&B4)>1)FALSE
5StanleyHudson=ArrayFormula(COUNTIF(A:A&"¦"&B:B,A5&"¦"&B5)>1)FALSE
6JimNixon=ArrayFormula(COUNTIF(A:A&"¦"&B:B,A6&"¦"&B6)>1)FALSE
7PhyllisVance=ArrayFormula(COUNTIF(A:A&"¦"&B:B,A7&"¦"&B7)>1)FALSE
8JimHalpert=ArrayFormula(COUNTIF(A:A&"¦"&B:B,A8&"¦"&B8)>1)TRUE
9PamHalpert=ArrayFormula(COUNTIF(A:A&"¦"&B:B,A9&"¦"&B9)>1)FALSE
10=ArrayFormula(COUNTIF(A:A&"¦"&B:B,A10&"¦"&B10)>1)TRUE

Fixing The Blank Rows

Adding a simple IF statement to output FALSE if the row is completely blank will fix this issue:

=ArrayFormula(IF(A2&B2<>"",COUNTIF(A:A&"¦"&B:B,A2&"¦"&B2)>1))

Now you have a usable formula, you need to consider the relative and absolute referencing.

Remember that you'll want the conditional formatting to highlight across columns so you need to think about how your top-left formula will change as it moves down rows AND across columns.

Here's is the top-left formula for the range (the two columns):

=ArrayFormula(IF(A1&B1<>"",COUNTIF(A:A&"¦"&B:B,A1&"¦"&B1)>1))

You want the A1 and B1 references to be absolute for the column and relative for the row:

=$A1&$B1

You want the A:A and B:B references to be absolute for the column:

=$A:$A&"¦"&$B:$B

Which makes the final formula:

=ArrayFormula(IF($A1&$B1<>"",COUNTIF($A:$A&"¦"&$B:$B,$A1&"¦"&$B1)>1))

Adding columns to this formula is a bit of a pain as many columns will result in a long formula.

Adding one column looks like this:

=ArrayFormula(IF($A1&$B1&$C1<>"",COUNTIF($A:$A&"¦"&$B:$B&"¦"&$C:$C,$A1&"¦"&$B1&"¦"&$C1)>1))

Adding two columns looks like this:

=ArrayFormula(IF($A1&$B1&$C1&$D1<>"",COUNTIF($A:$A&"¦"&$B:$B&"¦"&$C:$C&"¦"&$D:$D,$A1&"¦"&$B1&"¦"&$C1&"¦"&$D1)>1))

You get the idea, it's annoying to do for wide data.

If you're interested in a formula that's easier to add and remove columns from I came up with this while developing this post:

=IF(TEXTJOIN("",0,$A1:$B1)<>"",COUNTIF(TRANSPOSE(SPLIT(REGEXEXTRACT(TEXTJOIN("࿊",0,{$A:$B,TRANSPOSE(SPLIT(REPT("༜¦",ROWS($A:$B)),"¦"))}),"(.*)࿊༜$"),"࿊༜࿊",0)),TEXTJOIN("࿊",0,$A1:$B1))>1)

I don't think it's great to use because it's:

  • too complicated to efficiently edit (or understand)
  • far more computationally taxing on your sheet (will slow it down)

However, it is far easier to update as you only need to change the four range references.

To add conditional formatting based on this custom formula follow the instructions previously provided making sure to select multiple columns in STEP 1.

Highlighting Duplicates In Multiple Columns Excluding The First Instance

Highlighting the second and subsequent instances of a duplicate helps to find and remove duplicates while keeping the first entry.

Doing so requires a small change to the range array of the previous formula to make sure the COUNTIF is only acting on the current and previous rows, not the next rows:

=ArrayFormula(IF($A1&$B1<>"",COUNTIF($A$1:$A1&"¦"&$B$1:$B1,$A1&"¦"&$B1)>1))

The idea behind this is explained in greater detail here.

To add conditional formatting based on this custom formula follow the instructions previously provided making sure to select multiple columns in STEP 1.


FREE RESOURCE

Google Sheets Cheat Sheet

Learn 12 tips and tactics that will make your sheets more beautiful and user-friendly from today.

Google Sheets Cheat Sheet

By entering your email address you agree to get email updates from me. I'll respect your privacy and you can unsubscribe at any time.

Kieran Dixon started using spreadsheets in 2010. He leveled-up his skills working for banks and running his own business. Now he makes Google Sheets and Apps Script more approachable for anyone looking to streamline their business and life.