How To Compare Excel Files and Find Differences

0:00you get sent two Excel files a version

0:02one and a version two when you open them

0:05they look very similar so how can you

0:07find the differences between them well

0:10in this video I'm going to show you

0:12exactly how to do that for two separate

0:14Excel worksheets as well as for two

0:17separate Excel files so let's get into

0:19it over here we have this Excel table

0:22and you'll notice that we have a version

0:24one and a version two and suppose we

0:27want to find the differences between

0:28them for this the first thing would be

0:30to set it all up so we can see both of

0:33them side by side for this we can head

0:36over to the view Tab and click on new

0:39window you'll notice that this creates a

0:42version two of the exact same file all

0:45we need to do now is go to view again

0:47and click on arrange all from here we

0:51want the vertical Arrangement so it's

0:53going to create a split screen and click

0:55on okay what we've done here is we have

0:58the exact same Excel files so if I

1:00change this value over here to a z

1:02you'll notice that this side changes too

1:04the only thing is we can now see it side

1:06by side so on this one we can be on V1

1:09and on this other one we can be on V2

1:12and to follow along with the same Excel

1:15file you can use the link in the

1:16description below to download it for

1:18free now that we're all set up I'll show

1:20you three different ways that you can

1:22find the differences between the two

1:24sheets from easy to advanc and first up

1:28we have the count a formula so let's

1:30take a look you'll notice that on both

1:32the V1 and the V2 we have the order IDs

1:36which should be unique so if these

1:38aren't unique we can identify that and

1:40that's one of the key differences for

1:43this we can use the equals count if

1:46formula hit the toab key there and the

1:49range is all of our order IDs we can

1:52select them with control shift down

1:55let's also lock them so when we move the

1:57formula down they don't move down by

1:59pressing the F4 key that gives them the

2:02dollar signs comma and the criteria is

2:05that they need to be equal to this sides

2:08if that's the case we'll close the

2:09parenthesis and hit enter and now if it

2:12says A1 that means that it's matching

2:14but if it says a zero it's going to mean

2:17that there's a difference there I'll

2:19drag this all the way down and it looks

2:21like row number six is different if we

2:24take a look at this ID here and compare

2:26it to this other one you can see the

2:28values are indeed different this method

2:31isn't perfect though and that's because

2:33it's only considering the order ID it

2:36doesn't really consider whether this row

2:38with the manager the country or the

2:40sales are any different so let me show

2:42you a better method an intermediate

2:45level alternative could be to use a

2:47conditional formatting with a formula so

2:50let's take a look we have the same table

2:52over here and we're just going to select

2:54it all with contr a once that's selected

2:57we'll go to conditional formatting new

3:00rule and we'll go all the way to the

3:03bottom use a formula and the idea is

3:05that we want to select this first cell

3:07in the table B2 for us remove the dollar

3:10signs so it's Dynamic and it can move by

3:13pressing the F4 key three times there

3:16and we want that to be not equals to the

3:19one on version two which we have to the

3:21right hand side and again we'll remove

3:24the dollar signs from there the idea is

3:26that if these two aren't equal that

3:28means there's obviously a different

3:29difference between them so we want to

3:31format it differently in a way that

3:33stands out like let's say in an orange

3:35color and click on okay now you can see

3:38that all of these in yellow are

3:40currently not matching so row six over

3:43here if we take a look it also isn't

3:45matching and unlike the count if this

3:48one also works for the other columns

3:50like the manager country or sales and

3:53it's fully Dynamic too if I change this

3:56side to Italy then all of a sudden the

3:59conditional format is gone as it's

4:01matching to the version two both the

4:04count if and the conditional formatting

4:06methods that we just saw don't actually

4:08show the two values side to side to

4:10compare let's go over an advanced method

4:13to do exactly that for this we're back

4:16in the original file that has a V1 and a

4:19V2 and we'll create a new sheet and

4:21within it what we'll do is only show the

4:24two options whenever there's a

4:26difference between the two sheets so for

4:28this we'll type equals if hit the Tab

4:32Key and The Logical test is firstly that

4:35the V1 so this value right here that's

4:37our first one inv version one cell B2

4:41has to be equals to version 2 cell B2

4:45comma if those values are matching well

4:48then we can just show any of the two

4:50because they're going to be the same so

4:52I'm saying to just show the one in V2

4:55comma that said if false meaning they're

4:58not matching then we need to show both

5:00options the version one and the version

5:02two option so in quotations here we'll

5:05put for V1 that's put a colon space

5:10close the quotations and then we need to

5:12link it to the actual V1 we'll do that

5:15by adding an

5:16erson and let's go to V1 and select that

5:20B2 cell so we're saying this is going to

5:22be the answer that shows up for version

5:25one and now we need to add the answer

5:28for version two we'll do that by adding

5:30another ERS sand and in quotations we'll

5:33put something like versus we'll put a

5:37space versus Space this is the V2 option

5:41colon there space and a quotation to

5:45close that and another umper sand and

5:48now we need to link it to the actual V2

5:50answer which is right up here I can

5:53close the parenthesis and hit enter for

5:56now because this is matching fine I just

5:58get the order ID and it should be the

6:00same across all of the headers but when

6:03I drag this down let suppose somewhere

6:06around here and let me just resize this

6:09with alt

6:10hoi that's the same as going to format

6:13and autof fitting the column wids you

6:16can see here that whenever there isn't a

6:18match I get both options the version one

6:21option is this figure versus the version

6:23two option which is this other figure I

6:26don't need this bottom row so I'm just

6:27going to delete that one but now you get

6:29the idea of how this works if you don't

6:32really like the BS in the in between we

6:35can switch that to something like adding

6:37a dash hit enter and to copy the same

6:40formula across we can just control shift

6:43down control shift right contrl d to

6:47drag it down and crlr to drag it to the

6:51right now you can see instead of a

6:53versus we have that Dash between the two

6:55options make sure you stick around until

6:58the end for a surprise bonus feature

7:00very few people know about but first if

7:03you want to learn Excel fast and

7:05efficiently you can consider checking

7:07out our Excel for business and finance

7:09course and what makes this course

7:11different is that it's all applied to

7:13the real world while we still cover

7:15theoretical lessons like formatting

7:17formulas and charts we also offer case

7:20studies that simulate the type of work

7:22you might be assigned in your day-to-day

7:25ranging from Financial modeling to

7:27cleaning a real data set and presenting

7:30some visual insights we also offer

7:32several other courses including powerbi

7:36finance and valuation and much more so

7:39if you're interested head over to the

7:40link in the description below so far

7:43we've been comparing two different

7:45sheets within the same Excel file but if

7:47you want to compare two separate Excel

7:49files here's how you can see right here

7:52I have a version one and in a separate

7:55Excel file I have a version two we

7:57somehow want to compare these too for

8:00that it's fairly similar just by

8:02pressing the plus sign we'll use the

8:04same if statement again this time I'm

8:06going to go a bit faster as I've shown

8:08it already and The Logical test is that

8:11this figure right here so B2 has to be

8:14equals to this other one in this

8:18separate sheet you can see it's called

8:19file 2. xlsx so it's a separate Excel

8:23file entirely we want to get rid of

8:25those dollar signs though so it remains

8:27Dynamic comma and the value if true we

8:31simply want to select the B2 as that

8:33means there's going to be a mat so it

8:34makes no difference comma and for the

8:37value if falls in quotations we'll put

8:39that the v1's answer closer quotations

8:42and an % would be just this figure right

8:46here ERS again now we need the V2 so

8:50we're going to put a dash to compare and

8:53we'll also put the V2 answer would be

8:56space Clos the quotations and

9:00and it would be this figure just up here

9:02again let's get rid of those dollar

9:04signs like so and finally we can close

9:06up parentheses and hit enter so we get

9:09the order ID and we can make this side

9:12full screen now I'm just going to drag

9:14it along all the way to Row 18 and drag

9:17it sideways as well let me autoit that

9:20with alt hoi and you can see we get the

9:23exact same format with the changes but

9:26this time we're comparing two different

9:28Excel files I'd say this is a pretty

9:30elegant way to go about it but in this

9:32bonus section I want to show you a

9:34built-in method that very few people

9:36know about and actually if you take a

9:39look over here there's what's known as a

9:41spreadsheet compare within your computer

9:44let's go ahead and open that one up

9:46you'll get a separate window looking

9:48like this and what we want to do is go

9:50to compare files here we just want to

9:53upload the two files that we have

9:54available so it might be the version one

9:57and the version two for me I'm just

9:59going to go over to desktop and select

10:01them one is my file one and the other

10:04one is just my file two and click on

10:07open then I can just click on okay and

10:10I'm going to start to see the

10:11differences between them you can see

10:13it's highlighted here in green all of

10:15the parts that are different between the

10:18two options and over on the left hand

10:20side I can filter by what type of

10:23difference I want in my case let's say

10:25I'm only looking for a difference in

10:27values so that's all that it's going to

10:29select over here awesome so that's a

10:31cool feature that you probably didn't

10:33know if you want to learn to sell

10:35reference properly which is one of the

10:37harder things in Excel check out this

10:39video over here or take our Excel course

10:42over here hit the like and that

10:44subscribe and I'll catch you in the next

10:46one