How to Compare Two Excel Sheets (and find the differences)

hello and welcome to this excel tips video I am cement pencil and in this video I am going to show you how to compare two sheets in Excel now these two sheets could be in the same excel workbook or these could be in two separate workbooks Excel has some inbuilt features that you can use to compare these two sheets and then there is also conditional formatting and a formula technique that I am going to show you in the end of this video so let's get started here I have this file called January 2020 and I have this data and I also have another file open with the name Feb 20 2007 to do is compare these two sheets that are in different Excel files by aligning them together so that I can manually quickly go through these numbers for January and February in Excel at one point in time you can only have one workbook that is the active workbook that is at the top so to align these I'm going to first go to the Jan sheet here and align these I would click on the View tab and here I have this option view side-by-side and when I click on it it is going to align all the open workbooks in the same view so see what happens when I click on it you can see that I have the Jan workbook here and I have the February workbook here and these are aligned so now if I want to I can quickly see manually the data that is here for January and February and I can see how it has changed in case you have more than one workbook open it is going to align all the workbooks together so in case let's say you have January February in March then all those three would be arranged now in this case if you do not get this arranged in a way I have it here then you can manually do that you go here get to the View tab and click on arrange all and when you click on it it is going to show you this dialog box where you can specify how you want this to be arranged so it's tiled at this moment but I can go for horizontal in case I am comparing rows or vertical so let's say if I click on vertical and click OK I'm still going to get the same view because this is aligned vertically but if I want to align it horizontally let's say instead of columns I have the data in rows then I can click on arrange all click on horizontal and see what happens when I click OK it aligns horizontally so if I have data in a row that I want to compare then I can do that let me revert back to vertical another thing that you need to know is that at this point in time it's a very small data set in in all likelihood when you are doing the comparison you may have a bigger data set so to compare a bigger data set you have to also scroll through and there is an option where when you scroll in one sheet it is also going to scroll at the same time in another sheet so here again in the View tab you have this option which is called synchronous scrolling and if this is enabled if I scroll here in let's say sheet Jan you can see that at the same time it also gets crawled in sheet Feb because these two have synchronous scrolling enable which means that at any point in time I would have the same cells visible and it makes the comparison easy in case you do not want that you can switch it off or in case it is off you can switch it on it's a problem button and now since because it's off see what happens if I come here in Jan sheet and I scroll it only Scrolls the chance sheet but if I enable this it is going to scroll both of these sheets so this is how you can compare two sheets that are in two different workbooks and once you are done with the comparison you can close whichever workbook you want to close and then you can expand and maximize the window so this is when you have it in two different workbooks but what if you want to compare two sheets that are in the same workbook so let's see how to do that here I have two sheets Jan and Feb and these two sheets are in the same Excel workbook now if I want to compare these two sheet and have the data in both of these sheets viewable in the same screen then that is not possible because at one point in time I can only select one sheet but there is a feature in Excel that allows you to open two instances of the same workbook so as of now I have this workbook open called compare two sheets and I can only open it once if I go back again where it's saved and I try now again it is not going to let me do that it is going to tell me that I can only open one instance of this but to make two instances of this open so that I can compare these two sheets I would go to the View tab and here I have this option new window and when I click on new window see what happens as of now the name of the workbook is compare two sheets dot xlsx and now when I click on new window it opens two instances and now the name of this workbook is compared to sheets - - which means that this is the second instance and I can go back and here is the first instance so I can come back here to the second instance I'll zoom this so that I have the data which looks same and in the second instance I am going to select February and in the first instance thread this one also and in the first instance I am going to select January now because I have two workbooks open I can align these together and I can view this data side-by-side so I would again go back to the View tab here and then click on arrange all and here I would click on vertical and click OK and as soon as I do this you can see now I have again the same data which has been aligned these two workbooks are now aligned and in this case I have selected January here and February here so it allows me to open different worksheets in the same excel workbook and now I can manually compare these two another thing that you need to know about this is that although these look like two different workbooks it is actually the same workbook so let's say in this case I'm comparing this data and I see that the number for January is a lot less than February then I can come here and I can highlight this and now when I highlight this data you can see if in the second instance of this workbook I go back the Jan sheet you can see that this change is also made here so what is happening is these are exactly the same instance of the same workbook these are connected anything you do in one of these workbooks would be automatically reflected in the second one and when you close it it is going to revert back to the original book which was compared to sheets dot xlsx so see what happens I have done the comparison as of now it scrolling separate you can go to the View tab click on view side by side and then you can click on synchronous scrolling and now I have synchronous scrolling as well now when I close this see what happens as of now it says 1 & 2 and when I close this and I expand this you can see it is back to compare two sheets dot xlsx so when you use the new window option it simply opens more instances of the same workbook so that you can open different worksheets or maybe you have data far off in the same worksheet and you want to see let's say the top-left part of the data and somewhere far off cells in the same screen then you can do that here I have two sheets Jan and Feb and I have a similar data set in both these sheets now what I want to do is compare these two sheets and highlight those data points that are different and I can do that using conditional formatting now remember that conditional formatting is only going to work if you have both these sheets in the same excel file it is not going to work if you have these sheets in two different Excel workbooks now in this case because I want to compare these two I'm going to select this entire data set in the January worksheet because this is where I want to highlight the different data points and in case you want to highlight it in the February one then you select February and then apply conditional formatting in this case I'm going to select January and now I'm going to go to the Home tab and click on conditional formatting and here I'm going to click on new rule and in the new formatting rule dialog box I'm going to select this option use a formula to determine which cells to format and I'm going to compare each and every cell in January sheet with the February sheet using a formula and because I have selected this specific data range the comparison would only be done in this data range but if you want you can select the entire worksheet now in this case my formula is going to be equal to this cell here and as soon as I select this cell it inserts a 1 but you see there are two dollar signs and these dollar signs make the cell reference absolute which means that no matter where I go in the worksheet it is always going to refer to cell a1 and I don't want that because I want to compare cell a1 in one sheet with a 1 in other feed but I also want then compared B 1 in one sheet with B 1 in other sheet so I want this to be flexible to automatically change as the cell changes so I'm going to get the cursor here and then press f4 three times and see what happens when I press it once it gives me this so the dollar in front of the column alphabet is gone I press it press it again and it gives me this one these are mixed references and now when I press it the third time it gives me simply a1 which is what I want because I want it to be completely relative as I in the worksheet I want this reference to change and I'm going to say if this is not equal to the same cell in the fehb shape so I go to the fact sheet I select this again it gives me absolute reference so I press f4 three times and this is my condition and if it compares each cell in the selected data set in January with each cell in February and I can format it if this condition is met which means that if these two values are different I can highlight it so I click on format I go to the fill tab and here let me select orange color and click OK and then click OK and you can see instantly it highlights these cells that are different so the first one is b1 because it says February here if you want you can leave the headers out of this comparison and these are the 3 cells where I have different data points so in case you have a huge data set and comparing these side-by-side and manually scrolling through it is not an option because manually doing it is error-prone and it takes a lot of time then using conditional formatting is a good way because it will instantly highlight all those data points that are different now let me also show you another method of comparing two sheets where you can quickly identify only those cells that have a different data points and you can also see it manually so in this case if I come here I know that this is different in January and February but I don't know what the value in February is I would have to go back here or I would have to probably align these and then go through it but there is a formula method where you can instantly see what's the difference by creating a formula and fetching these two values that are different so let's see how to do that so again I have the January data and the February data in two different sheets and I only want to fetch the data points that are different and at the same time see what's the difference so I'm going to insert a new sheet here and let me call this sheet difference where I'm going to only pull those cells that have a different data point and I'm going to pull that data from both these sheets so here in a single view I would be able to see that this is the where the data point is different and this is the difference so in this case I'm going to use a formula let me zoom this a bit and the formula is going to be if and I'm going to then compare if this cell here is not equal to this cell here and I'm using the headers as well if you want you can leave the headers out so this is my condition and if this is true which means that there is a difference then I want to fetch the value for both so here I can say Jan value and then sorry not Feb here Jan so this would be Jan value and then it fetches the giant value and then I can use care 10 which is something that gives me a line feed so I have giant value in one cell and in the same cell in the next line I would have the fair value so I would say and web value 10 percent and now I go to Feb here and select this cell and in case there is no difference then I'm going to return an empty cell so this is the formula and now when I hit enter you can see this cell is empty and I can drag this down and I can drag it to the right and you would see the difference here now as of now this doesn't look so good because there are no spaces so what you can do is select this entire sheet or this data set where you have these values and I wrap the text so you go to the Home tab and click on wrap text and when you do that it is going to instantly give you these in two different lines this is because I have used the cad function and let me quickly auto fit these and now i have everything in a simple view where i know that these are the cells where there is a difference in both these fields so first one is the head of the title again if you don't want to include this you can leave it out but then i can see here in this value jan value is 375 while feb is 540 so instead of going back and forth in checking how these values differ you actually get to see all the differences in one single shield this is a difference report that we have created in a different shade using a formula so if you're working with a huge data set and there could be minor differences then you can quickly apply this formula and it would instantly fetch all these differences and show you in a single sheet where these differences are so that you can go back and you can compare and if you want to change it you can do that so these are some of the methods that you can use to compare two sheets in Excel these could be in two different Excel files where you can compare these side-by-side or these could be in the same excel file where you can also compare this using side by side using the new window feature or you can use conditional formatting or a formula to highlight the differences or fetch the differences and show it in a different sheet all together that's it in this video I hope you found this useful also if you're liking these videos please subscribe to this youtube channel and click on the bell icon so that you never miss out on any new excel tips video I come up with thank you and have a wonderful day

Channel: TrumpExcel

Views: 394,233

Rating: 4.8109431 out of 5

Keywords: compare two sheets in excel for differences, compare two sheets in excel, compare two sheets in excel for matches, compare two sheets in excel for duplicates, compare two excel worksheets, compare two excel files for differences, compare two excel sheets and highlight differences, compare two excel sheets, compare two excel files, advanced excel, excel, excel basics, excel tips, excel tutorials, learn excel, sumit bansal, trump excel, microsoft excel, compare two sheets

Id: 8Ou_wfzcKKk

Length: 14min 59sec (899 seconds)

Published: Thu Jan 23 2020