How Can I Compare Two Tabs for Differences?

About this Tutorial

So in this video we're going to compare two tabs and differences. Basically, someone asked actually on AppSumo, someone asked how do I compare two sheets to know what the differences are? This is the answer.

Sheet Resources

Access Sheet

Copy Sheet

Preview Sheet

Featured Formulas

COLUMN() IF() INDEX() INDIRECT() ROW()

Video Transcript

<div>00:01 Hello, welcome. So in this video we're going to compare two tabs and differences. Basically, someone asked actually on AppSumo , someone asked how do I compare two sheets to know what the differences are? 00:15 Now here's sort of what you could potentially do, right? And you might be doing already, is if you have say, video titles, like here I'm, I'm just checking my YouTube video titles and I've changed a few titles, but I wanna know like, which titles did I change? 00:28 Or maybe I outsource this.

Anytime we do things like outsource blog posts writing editing outsource something, we might send some data , get data back, and then we're like, oh, what actually changed? 00:45 And so you might have two CSV files, you import them into Google Sheets, you create two different tabs, sheet one and sheet two, and you're like, what are the differences? 00:54 Now look, if, if the differences are stark and very many, you want to go through those one by one, but like, look at this, these two sheets, you cannot tell the difference. 01:04 Well, you can probably tell if you're really looking hard.

The very first title is different. I've changed the numbers 1 million to literally the words 1 million. 01:13 So what else is different though? If only there are few changes? How can you tell the difference between sheet one and sheet two? 01:20 Now again, you might be thinking, I don't need to do this. I'm I don't have changes or somebody else highlights changes for me. 01:29 But in a way, you want to create an asynchronous environment sometimes with data , and you're gonna send data and you're gonna get data back. 01:37 And especially if you get outside of sheets, right?

If, if you have data inside of sheets, you can look at version history . 01:45 I have another video here on Better Sheets about change logs of keeping logs as edits go. We can even have an on edit that says every time there's an edit log, that edit, which is pretty phenomenal, right? 01:59 But like in reality though, we're going to have some data that we send away and we get back.

Now in this case it's video titles, but in your case it might be stats custom statistics data that you get from someone and you maybe get one day and then ano another stat the next day. 02:21 And you want to know the differences between them. Again, this is really helpful if there's maybe a few changes and you're not going piece by piece. 02:30 So let's go through at least one of the simplest ways that I know how to do this. I would create a third sheet called compare. 02:37 Let's call it compare.

And literally in here we are going to have we're just gonna compare the two things, the two cells on the, on two different sheets, the same cell on two different sheets. 02:53 And so we're gonna use like an if formula . The logical expression are gonna say sheet one exclamation, point A one is equal equals sheet two A one. 03:11 Let's see if it's true. We want to say same. If it's not true, we wanna say different.

For instance, so that says same. 03:24 So we already know, you know, we already picked out sheet one B two and sheet two B two are different. 03:32 So let's see if this works. And if we just com copy and paste. What's happening is that A one is changing to B one as we copy paste. 03:43 And so again, all this is same, same, same. We can copy paste there and different. So now we have exactly what's different, the exact cell that's different. 03:57 Are there any other cells that are different? And we can look through this. Same, same, same, same, same difference.

See, we found a new one, B 42, B 42. 04:07 Could we actually have seen that B 42? There it is. What is the difference between these B 42 is everything different? 04:24 Oh no, sorry, B 41. Oh my god, I got that. B 40, create a business in one hour. B 41, create a business in one hour with the word one. 04:33 All right? See, we in a world of texts, we would've never figured that out.

Now why would we create an entire sheet that had same, same, same, same, same different, and why would we ever wanna like look through this, right? 04:47 We can copy everything else and see it's all same, but like this is pretty hard to look through, right? Even though it's a different word, right? 04:55 Literally the word different, we can spot it visually. Maybe there are other ways we can spot this differently.

We can definitely do something like where is it format conditional formatting , and we can change the format . 05:13 Rule to text is text is exactly different and we can give it a red, maybe with a white there. And this pops up pretty well, right? 05:32 But you know, this is not that great, right? We actually do know the difference. We can see the difference here. 05:40 But we're still like looking through this. We wanna really look through our data . Could we potentially add the same conditional formatting ? 05:49 But when it's B2 here, can we do it on sheet two or sheet one?

Let's see if we can do that. 05:56 So we gotta do format conditional formatting . We're going to say apply to range A one. Let's do A to D and we're going to try doing custom formula sheet. 06:18 No compare. A one Is Different. Now what's interesting is that when we use compare a one, right is equal to different. 06:41 That's what we want to find out. Is it equal to different on the other page? But this compare is not very good, so we need to use it. 06:50 It doesn't work actually. That's the thing we need to use indirect .

We need to use equals indirect and then use compare. 07:18 Okay? So we cannot use the indirect . Only we we need to do is we need to compare, is the word different? 07:27 Is it equal to index ? Then inside of that indirect use in quotes the sheet that you're looking at and the rows and columns, then comma row A one, column b, A one, what is gonna happen is this. 07:45 A one in here, in this custom formula for this entire range is actually going to change.

If we had say, added dollar signs in front of A and one, it wouldn't work because it would stay a one as it goes through here through go through the conditional formatting . 08:01 So I will actually put this on this and let's examine this whole thing. Now what's going on is let's do a little tag there. 08:13 What's going on is we, the result is that we want to highlight the different cells . And so we're just comparing each cell between sheet one and sheet two, but we're doing that on the compare tab. 08:28 We've set ourselves up with everything we need.

And now we can actually change sheet one, sheet two to whatever we want. 08:35 Because the compare sheet is always gonna look at sheet one, sheet two with the same exact cells and look at them and say, are these the same? 08:43 And it's gonna, if they're not the same, they're gonna say different. Again, we're doing this so that we don't have to look at this compare page. 08:51 We want to actually be able to conditional use conditional formatting to show it on the data itself.

So here we have the exact two cells that are different. 09:01 Now, does this change before I go too deep into conditional , the conditional formatting that we got, let me show you that it changes. 09:10 So if we change this to 15 over here on she one, it changes automatically. It shows us the exact differences. 09:18 This is pretty cool. All right, let's go into how this conditional formatting works a little bit more.

So first off, conditional formatting custom formula must always start with an equal sign. 09:30 It's doesn't really mean equal, it means like this is gonna be a formula . In this particular case, it means it's gonna be a formula . 09:39 Then we're gonna use the different as literally just text.

So we wrap that in double quotes and then we say, well, what, what do we have to compare different two, we wanna compare it to the compare tab. 09:52 We wanna know on the compare tab is this particular cell that I'm on, this conditional formatting that I'm formatting , is this the one, what does it say on compare? 10:02 And if it says different, I want to highlight it. Okay?

So what we actually do is we use index here, we're gonna use the index formula . 10:10 Then when we want to look at another tab, we can't not use the indirect , we have to use indirect here. 10:19 And indirect is sort of a magical little formula that says go to the sheet and ta and, and sell and reference within my indirect. 10:31 So when we wanna reference another sheet we use indirect.

Sometimes when we're sort of programmatically building out that we wanna look at another sheet in another which particular cell, and we wanna change that cell number or cell reference , we want to keep the sheet. 10:47 In other videos I've talked about the indirect . And actually you can go and search those and see how indirect works. 10:52 Many ways it's a bit magical and bit sort of hand wavy here. But what we're doing is we're building the, the name compare sheet A to D. 11:03 Now if you want more than the D column, just go in here and change D to whatever you want.

Z we could do it, we can say Z. 11:12 And then within that index , so the reference is the entire compare sheet. Then we need to get a row and a column. 11:20 And we've sort of done a little bit of a trick here.

We do row and wrap that around a one column, A one. 11:26 And the trick is that conditional formatting , when you go up, when you apply it to a range , anytime in the custom formula that you have a reference to a cell, it will change re that reference . 11:42 So if I put a one in here and the, and the range is A starts at a one, when it applies the range to say B two, it will change the cell here to B two. 11:53 Again, just as I mentioned earlier in this video, you can put dollar signs in front of the A and the one and it locks it in. 12:00 So it'll apply the A one to everything.

But in this case, we actually do want it to change. We want the change because we don't, we, we want to compare to the cell on compare <laugh>, maybe I should have used a different word for compare the compare sheet, maybe a different title. 12:20 Okay, so now we have a, a sheet here, an entire work file that allows us to import two pieces of data . 12:31 It compares them on the third tab and also the conditional formatting will allow us to like see that data on the sheet itself. 12:40 So again, I just wanna show you a couple changes if you do wanna change it.

So if you wanna do like a colon z, I would recommend going to this custom formula and changing a D to a z, a colon Z. 12:55 And that will allow you to get everything. And then we also want to go to compare, let's just for the sake of having it complete, we will have an entire 10,000. 13:07 Yeah, 1000 rows. 26. So it's 26,000. Comparisons here. Each one going to sell on sheet one, sheet two. Looking at them, are they different? 13:20 If they are different, it will show different.

If there is nothing in it, let's see what happens if there is nothing there, and we'll go back to our compare. 13:31 Everything's the same, same, same. And now let's look at let's just type in a one and bo it is different, right? 13:41 And if it's not different, right, it will not show up perfect. It shows up, it doesn't have the applied conditional formatting . 13:50 They are the same, especially if this is probably even useful to find something like, yeah, hidden spaces hidden characters.

This is great for, so yeah, go grab the sheet if you want this sheet, if you wanna check out this conditional formatting . 14:06 There it is. I put the conditional formatting on here, conditional formatting available right here on this. I'll move it up to a one actually, and I'll use tiny sheets to delete all the rows and columns. 14:22 So this is only a one cell sheet. There you go. Can't go any different than this <laugh>. Let's just make this a little bit bigger so you can really see it. 14:32 There you go. Hope this was helpful to you. Bite.</div>