Hello and welcome to this Excel tips video. I am Sumit Bansal and in this video I'm going to show you how to compare two sheets in Excel. These two sheets might be within the same Excel workbook or these might 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'm going to show you in the end of this video. So let's get started.
how to compare two excel files
Here, I have this file called January 2020 and I have this data and I also have another file open with the name Feb 2020, and again it has a similar data. Now, what I want 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'll only have one workbook that's the active workbook. So to align these, I'm getting to first attend 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 even have the Jan workbook here and that 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've got quite one workbook open, it's getting to align all the workbooks together. So in case, let's say you have January, February, March, then all those three would be arranged.
Now, during this case, if you are doing not get this arranged during a way I even have it here, then you'll manually do this . You go here and obtain to the View tab and click on on 'Arrange All', and once you click thereon , it's getting to show you this panel 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'm rows. So let's say if I click on vertical and click okay, I'm still going to get the same view because this is aligned vertically. But if I want to align horizontally, let's instead of columns, I have the data in rows, then I can click on a range all, click on horizontal and see what happens when I click okay, it aligns horizontally. So if I have data in the 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, and in all likelihood when you are doing the comparison, you may have a bigger data set. So to match a much bigger data set, you've got to also scroll through and there's an option where once you scroll in one sheet, it's also going to scroll at the same time in another sheet.
So here again within the View tab, you've got this feature which is named Synchronous Scrolling. 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 scrolled in sheet Feb, because these two have synchronous scrolling enabled, 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 toggle button. Now since it's off, see what happens, if I come here in Jan sheet and I scroll, it only scrolls the Jan sheet. But if I enable this, it's getting to scroll both of those 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 often once 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 are 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 'Compared Two Sheets' and I can only open it once. If I go back again where it's saved and I try and open it 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'.
When I click on 'New Window', see what happens. As of now the name of the workbook is 'Compare Two Sheets.xlsx'. Now, when I click on new window, it opens two instances and now the name of this workbook is 'Compare Two Sheets.xlsx -2', 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'm going to select February and in the first instance. In the first instance, I am going to select January. Now, because I even have two workbooks open, I can align these together and that i can view this data side by side. So i might again return to the view tab here then click on arrange all. Here, I would click on vertical and click okay 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. In this case, I've selected January here and February here. So it allows me to open different worksheets within 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 workbooks.
Let's say in this, 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. Now,, when I highlight this data, you can see if in the second instance of this workbook I go back to 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 are doing in one among these workbooks would be automatically reflected within the other . When you close it, it is going to revert back to the original workbook, which was 'Compare Two Sheets.xlsx'. So see what happens, I've done the comparison. As of now it's scrolling separately, but 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 one and two, and when I close this and I expand this, you can see, it is back to 'Compare Two Sheets.xlsx'. So, once you use the new window option, it simply opens more instances of an equivalent workbook in order that you'll open different worksheets, or even you've got 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 would like to try to to is compare these two sheets and highlight those data points that are different. I can do that using Conditional Formatting. Now remember that conditional formatting is merely getting to work if you've got both these sheets within the same Excel file. It is not getting to work if you've got these sheets in two different Excel workbooks. Now during this case, because i would like to match these two, I'm getting to select this complete data set within the January worksheet because this is often where i would like to highlight the different data points. In case you want to highlight it in the February sheet, then you select February and then apply conditional formatting.
In this case, I'm going to go select January and now I'm going to go to the Home tab and click on Conditional Formatting. In 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. Because I've 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 A1.
I press it once more, and it gives me this one. These are blended references. Presently I know when I press it the third time, it gives me essentially A1, which is the thing that I need since I need it to be totally relative. As I move in the worksheet, I need this reference to change. I will say if this isn't equivalent to a similar cell in the Feb sheet, so I go to the Feb sheet, I select this once more, it gives me outright reference. So I press F4 multiple times and this is my condition. It thinks about every cell in the chose dataset in January with every cell in February and I can organize it if this condition is met, which implies that if these two qualities are unique, I can feature it. So I click on design, I go to the Fill tab and here let me select orange tone and snap alright and afterward click OK. You can see quickly it features these cells that are extraordinary. So the first is B1 on the grounds that it says February here.
In the event that you need, you can avoid the headers with regard to this correlation, and these are the three cells where I have diverse information focuses. So on the off chance that you have an immense dataset and contrasting these one next to the other and physically looking through it's anything but a choice on the grounds that physically doing it is blunder inclined and it requires some investment, at that point utilizing restrictive arranging is a decent way since it will immediately feature every one of those information focuses that are unique.
Presently, let me additionally show you another technique for looking at two sheets where you can rapidly recognize just those cells that have diverse information focuses and you can likewise see it physically. So for this situation, on the off chance that I come here, I realize that this is distinctive in January and February, yet I don't have a clue what the worth in February is. I would need to return here or I would need to most likely adjust these and afterward go through it. Yet, there is a recipe technique where you can right away see what's the distinction by making an equation and bringing these two qualities that are extraordinary. So how about we perceive how to do that.
So once more, I have the January information and the February information in two distinct sheets and I just need to get the information focuses that are extraordinary, and simultaneously see what's the distinction. So I will embed another sheet here and let me call this sheet Difference, where I will just draw those phones that have an alternate information point and I will pull that information from both these sheets. So here in a solitary view, I would have the option to see that this is the cell where the information point is unique and this is the distinction.
So for this situation I will utilize a recipe, let me zoom this a piece, and the equation will be on the off chance that, and I will analyze, if this cell here isn't equivalent to the cell here and I'm utilizing the headers too. In the event that you need you can forget about the headers. So this is my condition and assuming this is valid, which implies that there is a distinction, I will have gotten the incentive for both. So this would be Jan worth and afterward it brings the Jan worth and afterward I can utilize Char(10), which is something that gives me a line feed so I have Jan esteem in one cell and in a similar cell, in the following line I would have the Feb esteem. So I would say and Feb esteem and, and now I go to Feb here and select this cell.
In the event that there is no distinction, at that point I will return an unfilled cell. So this is the equation and now when I hit enter, you can see this cell is unfilled and I can drag this down and I can drag it to one side and you would see the distinction here. Presently as of now, this doesn't look so great in light of the fact that there are no spaces. So what you can do is select this whole sheet or this informational collection where you have these qualities and Wrap the content. So you go to the Home tab and snap on wrap text. At the point when you do that, it will right away give you these into various lines. This is on the grounds that I have utilized the CHAR capacity and let me rapidly or incline toward these and now I have everything in a solitary view where I realize that these are the cells where there is a distinction in both these sheets.
So initial one is the top of the title. Once more, in the event that you would prefer not to incorporate this, you can forget about it. Be that as it may, at that point I can see here in this worth, Jan esteem is 375 which Feb is 540. So as opposed to going to and fro and checking how these qualities contrast, you really will see every one of the distinctions in a single sheet. This is a distinction report that we have made in an alternate sheet utilizing an equation. So on the off chance that you are working with an enormous informational index and there could be minor contrasts, at that point you can rapidly apply this equation and it would immediately bring every one of these distinctions and show you in a solitary sheet where these distinctions are so you can return and you can analyze, and on the off chance that you need to transform it, you can do that.
But you'll 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 A1 in other sheet. But I also want then to compare B1 in one sheet with B1 in other sheets. 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. See what happens when I press it once, it gives me this reference where the dollar in front of the column alphabet is gone.
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 altogether. 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.
Topic Covered
how to compare two excel files
how to compare two excel sheets for differences in values
how to compare two excel sheets
how to compare two excel sheets using vlookup
how do i compare two excel sheets to highlight duplicates
how to compare two excel sheets data
how do you compare two excel sheets and highlight the difference
how to compare two excel sheets for differences
how to compare two excel sheets for duplicates
how to view two excel sheets side by side
how to compare two excel sheets and remove duplicates
how to merge two excel sheets together
how to compare two excel sheets using vlookup step by step
how to open two excel sheets side by side
how to compare two excel sheets for matches
how to compare two excel files windows 10
how to open two excel files side by side
how do i compare two excel files
how to compare two excel sheets online
how to compare two excel spreadsheets for duplicate names
how can compare two excel sheets
how to compare two columns in two different excel sheets using macro
how to compare two columns in different excel sheets using java
how to compare two excel sheets for similarities
how to compare two excel sheets using java code
how to compare two excel sheets using macro
how to compare two excel sheets 2007
how to compare two excel sheets data using vlookup
how to compare two excel sheets and find duplicates
how to compare two excel files in python using pandas
Post a Comment