Excel Compare Worksheets using Compare File

This Excel tutorial explains how to compare worksheets using Compare File function.

You may also want to read:

Excel VBA compare worksheets

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).

Excel VBA compare worksheets 01

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

broken_links_04

 

Select Inquire > Press OK

broken_links_05

 

Now you have a new tab INQUIRE, in which you can find the Compare Files function.

broken_links_06

Excel Compare Worksheets using Compare Files

Assume that you want to compare the 2013 vs 2014 budget, as well as 2013 vs 2014 compensation.

Excel VBA compare worksheets 06

 

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.

excel compare worksheets compare files 001

 

Click on INQUIRE tab > Compare Files

excel compare worksheets compare files 002

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.

excel compare worksheets compare files 003

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

https://support.office.com/en-us/article/Basic-tasks-in-Spreadsheet-Compare-f2b20af8-a6d3-4780-8011-f15b3229f5d8

 

Wyman W
Wyman is a Business Analyst based in Hong Kong, specialized in business analysis, project management, and also creating custom Function and Sub solutions, and is proficient in report automation with Access.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *