Conditional Formatting Duplicates Across Tabs in Google Sheets

To conditional format duplicates across sheet tabs in Google Sheets, you can use two functions: COUNTIF and INDIRECT. Optionally, you can also use VSTACK and TOCOL.

In conditional formatting rules, when referring to a cell or cell range in a different sheet tab, you must use the INDIRECT function. For example, you can’t use Sheet2!A1 from “Sheet1” in a conditional format rule; instead, use it as INDIRECT("Sheet2!A1").

The purpose of COUNTIF is to conditionally count values in a range, which is useful for identifying duplicates. Highlighting the cells where COUNTIF returns a value > 1 is the logic for highlighting duplicates in Google Sheets.

VSTACK combines ranges, which can also be done with array constants, and TOCOL removes blank cells in the combined range.

Highlighting Duplicates Across Sheet Tabs in a Single Column

I am using only two sheet tabs in my example. Please follow my example carefully, and it will help you extend the formulas to more sheets.

Here is the content in column A of Sheet1:

Sheet1:

See what happens in Sheet2 when I enter duplicates of Sheet1 column A values.

Sheet2:

Example of Highlighting Duplicates Across Sheets

Go back to Sheet1 and see that the duplicates are highlighted there too.

The formula to conditionally format duplicates across sheet tabs in a single column:

For Sheet1:

=COUNTIF(TOCOL(
   VSTACK(
      $A$2:$A, 
      INDIRECT("Sheet2!A2:A")
   ), 3), A2)>1

For Sheet2:

=COUNTIF(TOCOL(
   VSTACK(
      $A$2:$A, 
      INDIRECT("Sheet1!A2:A")
   ), 3), A2)>1

Formula Explanation:

The VSTACK function vertically stacks the ranges in two sheets, and the TOCOL function removes empty cells and error values to improve performance.

The COUNTIF function counts the value in cell A2 in the current sheet within this combined range. If the count is greater than 1, the sheet highlights it.

Applying the Custom Rules in Both Sheets

The conditional formatting option is available under the Format menu in Google Sheets.

Steps to Conditional Format Duplicates Across Two Sheets:

Inputting the Across-Sheet Formula in the Conditional Formatting Panel

Select the range A2:A in Sheet1 (from A2 to the last row you want in that column).
Open the conditional format rule from the menu: Format > Conditional formatting.
Under “Format cells if,” select “Custom formula is.”
Copy-paste the above first formula in the given field.
Select the formatting style and click Done.

In Sheet2, follow the same instructions, but use the Sheet2 formula provided above.

Once applied, you will notice that if a value repeats in either sheet (Sheet1 or Sheet2), it gets highlighted in the relevant sheet or both.

That’s how we highlight duplicates across sheet tabs in one column.

How to Add a Third Sheet for Highlighting

If you want to conditional format across three sheet tabs, you should use the following formulas.

Within VSTACK, which vertically stacks the range to highlight from all sheets, specify the third sheet name. The current sheet reference can be specified directly without INDIRECT or the sheet name.

Formula for Sheet1:

=COUNTIF(TOCOL(
   VSTACK(
      $A$2:$A, 
      INDIRECT("Sheet2!A2:A"), 
      INDIRECT("Sheet3!A2:A")
   ), 3), A2)>1

Formula for Sheet2:

=COUNTIF(TOCOL(
   VSTACK(
      $A$2:$A, 
      INDIRECT("Sheet1!A2:A"), 
      INDIRECT("Sheet3!A2:A")
   ), 3), A2)>1

Formula for Sheet3:

=COUNTIF(TOCOL(
   VSTACK(
      $A$2:$A, 
      INDIRECT("Sheet1!A2:A"), 
      INDIRECT("Sheet2!A2:A")
   ), 3), A2)>1

Highlighting Duplicates Across Sheet Tabs in Multiple Columns

In the above examples, we used custom rules in conditional formatting to highlight duplicates in one column across all sheets.

If you want to highlight multiple columns across sheets, make these changes in the formulas:

For example, for three columns:

$A$2:$A will become $A$2:$C
A2:A within INDIRECT will become A2:C
Within the Conditional Formatting panel, the “Apply to range” must be set to A2:C.

Resources

20 COMMENTS

Dannielle February 12, 2022 At 5:25 am

Hello! It has been helpful! I am wondering how I would do this:

Sheet 2 column A has IDs.
Sheet 1 column A has IDs, and column B has a “Yes” or “No” answer.

I want to highlight Sheet 2, column A if column B in Sheet 1 (with the matching ID number) = “Yes.”

I do hope that made sense and that you can help!

Thank you!

- Prashanth February 12, 2022 At 9:40 am
  
  Hi, Dannielle,
  
  Try this rule in Sheet2!A2:A1000.
  
  =ifna(vlookup(A2,indirect("Sheet1!A2:B1000"),2,0))="Yes"
  
Jj S. July 19, 2021 At 11:53 am

Very helpful, thank you!!

Divya June 8, 2021 At 1:20 am

Can we highlight duplicates in multiple sheets within a workbook in google sheets?

Can you write the syntax for 6 sheets?

- Prashanth June 8, 2021 At 10:11 am
  
  Hi, Divya,
  
  I don’t recommend highlighting duplicates across several sheets as it can affect performance.
  In that case, I would recommend importing data to a single sheet and then highlight it.
  
  Query Syntax/formula to Import cell range A2:A in three sheets to the fourth sheet in that workbook:
  
  =Query({Sheet1!A2:A;Sheet2!A2:A;Sheet3!A2:A}, "Select * where Col1 is not null")
  
  If you are still interested in highlighting across six sheets, here is the syntax that you can follow.
  
  For two sheets, I have already explained. Here is when three sheets are involved.
  
  Format “Apply to range” is A2:A in all three sheets (tabs).
  
  Sheet 1:
  
  =(countif($A$2:$A,A2)+countif(indirect("Sheet2!$A$2:$A"),A2)+ countif(indirect("Sheet3!$A$2:$A"),A2))>1
  
  Sheet2:
  
  =(countif($A$2:$A,A2)+countif(indirect("Sheet1!$A$2:$A"),A2)+ countif(indirect("Sheet3!$A$2:$A"),A2))>1
  
  Sheet 3:
  
  =(countif($A$2:$A,A2)+countif(indirect("Sheet1!$A$2:$A"),A2)+ countif(indirect("Sheet2!$A$2:$A"),A2))>1
  
  You can follow this pattern to include more sheets in the conditional format rule.
  
Lee April 1, 2021 At 8:57 am

Hi Prashanth,

Great resource, I keep coming back but slowly getting it. Sorry, I’m new to spreadsheets.

Can you show me a formula to remove duplicates across a multiple 7 to 31 across tabs in Google sheet (each day in month list)?

I’ve seen the 2 videos, and it didn’t work for me sorry 🙁

I’d like to check 7 days period.

Ang March 26, 2021 At 1:29 am

This is just what I’m looking for, though what would the formula be for Sheet 2 if I do not want to highlight duplicates that are within Sheet 2?

If I list Avacaods twice within Sheet 2 they are both highlighted as duplicates (Avacados are not listed in Sheet 1).

I only want to highlight duplicates comparing to Sheet 1.

- Prashanth March 26, 2021 At 5:53 pm
  
  Hi, Ang,
  
  Replace the Sheet2 highlighting rule (formula) with the below one.
  
  =countif(indirect("Sheet1!$A$2:$A"),A2)>0
  
F October 6, 2020 At 11:39 pm

Thanks for this solution!

For Sheet1:

=(countif($A$2:$A,A2)+countif(indirect("Sheet2!$A$2:$A"),A2))>1

For Sheet2:

=(countif($A$2:$A,A2)+countif(indirect("Sheet1!$A$2:$A"),A2))>1

How can we add the same formula if we have sheet 3, sheet 4, sheet x …

Thank you!

Donna June 23, 2020 At 8:48 am

This is very helpful. I need to check if a value in a cell exists in a column in another worksheet. How to make this formula work in that situation?

Thanks!

- Prashanth June 24, 2020 At 8:38 am
  
  Hi, Donna,
  
  You may use the MATCH function.
  
  To match the value in cell B4 (“Sheet1”) in another sheet (column C in “Sheet2”) use the below formula in cell C4 in “Sheet1”.
  
  =ifna(match(B4,Sheet2!C:C)>0,FALSE)
  
  It would return TRUE for match and FALSE for a mismatch.
  
janine April 11, 2020 At 11:54 pm

Hi, whoa I can never figure these things out myself! I was wondering if you could help me – I have two lists of customers

A: All customers (including refunds)
B: All refunds.

So what I’m trying to do …. is produce a sheet that ONLY shows customers who did not get a refund. Using your strategy above works….except now the rows are highlighted for me to see but I can’t bulk delete them…

Is there a way to do something like…
IF THERE ARE DUPLICATES, DELETE BOTH?

I can upload both lists on the same sheet, it doesn’t have to be separate sheets so I think that will make it easier?

But everything I’ve looked online only shows me how to delete duplicates. What I’m trying to do is actually DELETE both when duplicates are found.

any insight you can provide, I would really appreciate!

- Prashanth April 12, 2020 At 11:10 am
  
  Hi, janine,
  
  If you use conditional formatting, now you can bulk delete the highlighted cells. I hope you are aware of the new feature in Sheets – Filter or Sort by Font or Cell Color in Google Sheets.
  
  If you are looking to filter the customers who didn’t get a refund, then try this filter.
  
  A1:A – All customers (including refunds)
  B1:B – All refunds.
  
  Assume the first row (A1:B1) contains headers.
  
  The formula to be used in cell C2 (case insensitive):
  
  =filter(A2:A,countif(B2:B,A2:A)=0)
  
  Case sensitive formula:
  
  =filter(A2:A,regexmatch(A2:A,"^"&textjoin("$|^",true,B2:B)&"$")=FALSE)
  
Agam Singh December 26, 2018 At 10:08 am

This is so useful! Thank you so much!

Would you be able to suggest a solution where this also works if cells in some of the columns are empty? As it stands today, the above does not highlight rows that have blank cells (even if they are identical).

As an example use case, when importing bank statements from a “new” sheet to an “existing” sheet, we want to highlight duplicate rows, but for two of the columns (debit and credit), there are values only in one of those columns.

Example data:
Date, Description, Debit, Credit
====================
01/01, Target, $10, <>
01/01, BestBuy returns,<>, $50
02/01, Target, $20, <>

Your help would be much appreciated. Thank you once again.

- Prashanth December 26, 2018 At 11:24 am
  
  Hi, Agam Singh,
  
  Please share a demo sheet and manually highlight the cell/cells (your desired result). Then I can possibly try.
  
Sebastian October 1, 2018 At 5:21 pm

Hi,

can I use match function across two tabs? I used COUNTIF, but the problem is that it highlights not only the duplicates between the tabs but also duplicates in one tab.

Best,
Sebastian

- Prashanth October 1, 2018 At 5:38 pm
  
  Hi, Sebastian,
  
  Yes! You can use. But don’t forget to use INDIRECT as it’s a must in conditional formatting to refer to another tab.
  
  I have a new tutorial but that also using COUNTIF.
  
  Highlight Matches or Differences in Two Lists in Google Sheets
  
  - Sebastian October 1, 2018 At 5:46 pm
    
    Hi Prashanth,
    
    Exactly, I used the Countif but do not know how to do it with a Match. Could you write it for me, please?
    
    - Prashanth October 1, 2018 At 5:54 pm
      
      Hi,
      I’ll try. Please make a ‘demo’ Sheet and share it with me. In that sheet clearly mention the output that you expect.
      Thanks.
      
      - Sebastian October 3, 2018 At 7:56 pm
        
        Hi Prashanth,
        Thanks, I used countif formula but without taking into consideration one tab and it works. Thank you again. You are doing a great job with this blog.