This Excel tutorial explains how to compare worksheets using Compare File function.
You may also want to read:
Excel Compare Worksheets using Compare File
Compare worksheets is to compare worksheets of similar structures but different values. Excel has a built it function called Compare File in Office Professional Plus 2013 or Office 365 ProPlus (click here if you are using older version). After comparison is complete, two worksheets are compared side by side and you get the below result indicating all the differences (change).
In other versions of Excel, you need to create a Macro to do the automated comparison. Click here to see my another post using VBA to compare worksheets.
Enable INQUIRE Add-In
Compare File function is under INQUIRE tab. By default, INQUIRE tab is not enabled.
In Excel 2013, navigate to Files > Options > Add-Ins
In dropdown box, select COM Add-ins > Press Go
Select Inquire > Press OK
Now you have a new tab INQUIRE, in which you can find the Compare Files function.
Excel Compare Worksheets using Compare Files
Assume that you want to compare the 2013 vs 2014 budget, as well as 2013 vs 2014 compensation.
The Compare Files tool does not allow you to specify which two worksheets you are trying to compare within the workbooks, it only compares worksheets of the same name. Therefore, you can either rename those worksheets you want to compare to the same name, the function also works if you only leave only 1 worksheet in each workbook. Refer to my previous post if you want to specify the which worksheets of different names to compare.
Now rename the worksheets as below.
Click on INQUIRE tab > Compare Files
The two workbook names are automatically populated since you have already opened the two workbooks.
The Swap Files button is move up the 2014 workbook and move down the 2013 workbook. The upper one will be called “File 1” and lower one will be called “File 2” in the comparison result
Now click on the Compare button
The result of difference between worksheets of the same names is generated.
In the upper section, the workbook on the left is File 1, on the right is File 2, differences are highlighted in green. If you refer to the bottom left section, green color means the “Entered Values”. One important thing to note is that in File 2 row 6, the additional values (Mavis with 50000 salary) are not highlighted, instead the middle bottom part shows a change description of Added Row 6. This representation of the results is more like a “track changes” function rather than simply identifying all the differences.
Other than the value comparison, you can see the tool can also find out other types of differences as well, but I am not going to test one by one because the descriptions are already self-explanatory.
Outbound References
