Another detailed tutorial on how to identify duplicates in Google Sheets by highlighting. Let me shed some light on how to conditional format duplicates across sheet tabs in Google Sheets.
By saying ‘another tutorial’ I am just referring to one of my earlier tutorial that handles duplicates within one sheet tab. That contain some awesome tips and tricks.
Similar Topic: Highlight Duplicates in Single, Multiple Columns, All Cells in Google Sheets (within one sheet tab).
To conditional format duplicates across sheet tabs, I am going to use two functions. They are Countif and Indirect.
In conditional formatting rules, when you want to refer to a range in a different sheet tab, you must use the Indirect function.
Must Read: Role of Indirect Function in Conditional Formatting in Google Sheets.
Why are you using Countif?
The function Countif helps me to conditionally count a column. So if the Countif returns the count that’s more than 1, that means the criterion repeats.
Highlighting the cells wherever the Countif returns the value>1
is the logic in highlighting duplicates in Google Sheets.
To highlight duplicates across sheet tabs that within one Google Sheets file, as mentioned above, you must use the function Indirect and multiple Countif formulas.
Let’s see how all that formulas develop.
Highlight/Conditional Format Duplicates Across Sheet Tabs in Google Sheets
To make the formulas simple to you, I am only using two sheet tabs in my example. So please do carefully follow my example and that can help you to extend the formulas for more sheets.
The Formula to Highlight Duplicates Across Sheet Tabs – Single Column
Here is the content in my column A in Sheet1.
Sheet1:
See what happens in Sheet2 when I enter duplicates of Sheet1 column 1 values.
Sheet2:
Just go back to Sheet1 and see that the duplicates highlighted there too.
The formula to Conditionally Format Duplicates Across Sheet Tabs in Single Column:
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
I have already explained to you how this formula highlights duplicates in different sheet tabs.
Are you new to Google Sheets? If yes, then follow the below instructions to apply this custom rule.
The conditional formatting is available under the menu Format in Google Doc Sheets.
Steps to Conditional Format Duplicates:
1. Select the range A2: A in Sheet1.
2. Open the conditional format rule from the menu Format > Conditional Format.
Apply the above first formula there as below.
In Sheet2 also follow the above same instructions.
The Formula to Conditional Format Duplicates Across Sheet Tabs – Two Columns
Honestly, I don’t know how to explain this scenario correctly. So I am depending screenshots.
Here my sheets that involved in highlighting duplicates across sheet tabs are Sheet3 and Sheet4.
The Values in Sheet3:
The Values in Sheet4:
See the highlighting pattern. If values in column A and Colum B match in the same sheet or in a different sheet more than once, such rows are duplicates and subsequently got highlighted.
Here are the formulas to highlight duplicates across sheet tabs in two columns:
The Formula for Sheet3:
=(countifs($A$2:$A,$A$2:$A,$B$2:$B,$B$2:$B)+countifs(indirect("Sheet4!$A$2:$A"),$A$2:$A,indirect("Sheet4!$B$2:$B"),$B$2:$B))>1
Select the range A2: B in Sheet3 and apply this conditional format rule.
The Formula for Sheet4:
=(countifs($A$2:$A,$A$2:$A,$B$2:$B,$B$2:$B)+countifs(indirect("Sheet3!$A$2:$A"),$A$2:$A,indirect("Sheet3!$B$2:$B"),$B$2:$B))>1
Select the range A2: B in Sheet4 and apply this rule.
Similarly, you can add more columns and more sheets. That’s all.
Follow the above examples to conditional format duplicates across sheets in Google Sheets. Enjoy!
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!
Hi, Dannielle,
Try this rule in Sheet2!A2:A1000.
=ifna(vlookup(A2,indirect("Sheet1!A2:B1000"),2,0))="Yes"
Very helpful, thank you!!
Can we highlight duplicates in multiple sheets within a workbook in google sheets?
Can you write the syntax for 6 sheets?
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.
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.
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.
Hi, Ang,
Replace the Sheet2 highlighting rule (formula) with the below one.
=countif(indirect("Sheet1!$A$2:$A"),A2)>0
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!
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!
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.
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!
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)
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.
Hi, Agam Singh,
Please share a demo sheet and manually highlight the cell/cells (your desired result). Then I can possibly try.
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
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
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?
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.
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.