Highlight Duplicates of Two Columns
Learn how to highlight duplicates across two columns in Google Sheets using the COUNTIFS function and conditional formatting. This tutorial provides step-by-step instructions to effectively identify and manage duplicate entries.
So you have a few columns and you want to find out. Which ones are duplicates, not just one column. I just don't want to see. Hey Alice. And Alice and Alice, I wanna say Alice is 45. Eve is 45. That's not a duplicate , but Alice 45 is a duplicate . So how do you highlight duplicates when they're two columns? We're gonna end up using Count Ifs in conditional formatting , but I wanna show you this formula before we do that.
The count of how many are both of those? True. So if we just copy paste our formula down, you'll see here Bob and 20. So let's look Bob 20. Yes. And it's only going to count those that are both the same. So we're gonna end up using this as our base of our formula , and we're gonna end up in conditional formatting with either a true or false statement. And so we're gonna say if it's greater than two, because we want to highlight, sorry, greater than one, because we wanna highlight where there is an actual duplicate , meaning there's more than one everywhere.
We are going to have to change a little bit of this, where you need to add some dollar signs. So let's take this formula and put it into our conditional formatting format. Our range is going to be B colon, C. And we're gonna change our format . Rules to custom formula is equal to, and it's gonna be our count ifs just equal to count ifs B, colon B, B2C, C two, and we want to say greater than two.
So what we need to do is add a dollar sign in front of the bees, all of them, and the Cs. Even C two, and because we're starting our range at B one with this B colon C, we do need to change B two to be one and C two to C one. And if you notice here on the left, we are now correct. Right. Oh, sorry. Oh, greater than one. Now it's correct because it's highlighting the duplicates of all of them, not just over two.
Make sure you check out this video or this video and subscribe right now to get more tips, tricks, how tos, get more out of your Google sheets than you ever have before. I'm excited to be making a ton more videos here. Ask me questions down in the comments and I will answer them in future videos.