Very useful if you are using it in collaboration with others
Office 2013 Professional Plus has a couple of new features including a new program called Spreadsheet Compare.
Spreadsheet Compare takes two Excel files and tell you the difference between them in a very nice table layout. You can quickly see the differences in formulas between two spreadsheets and the differences in values also.
It’s a very useful program, especially if you use Excel a lot in collaboration with others. If you email someone an Excel file and they make changes to a few formulas, etc., you can use Spreadsheet Compare to quickly see what edits were made to the original. In this post, I’ll walk you through the program interface and how you can use it.
Note: SpreadSheet Compare from Microsoft is only available if you have Office 2013 Pro Plus or Office 365 ProPlus. It is not included with Office 2016 for some reason. There is also an open source spreadsheet compare program on SourceForge, but it is different than the Microsoft one.
How to Use Spreadsheet Compare
When you first open Spreadsheet Compare, you will see the main program screen, which looks like this:
There is only one ribbon tab that has a couple of options like showing formulas, resizing cells to fit and exporting results. In the center is the split screen view of the two Excel files you will be comparing.
At the bottom are three windows, one for which options you want to see, one for viewing the actual differences in a table view and one that displays visually where most of the differences take place.
Click on Compare Files at the top left and then choose the two Excel files you want to compare.
Now you can quickly see by the color-coded cells what values or formulas are different.
As you can see, entered values are green, formulas are purple, calculated values are bluish, etc., etc. These are my example spreadsheets where one takes 5 pairs of numbers and adds them and the other just multiples them.
Since the only difference is one column is using a sum and one is using multiplication, the values are highlighted in purple, which means the difference is in the formulas. You can also click the Show Formulas button in the ribbon to quickly see the difference in the formulas.
The table at the bottom will quickly give you a detailed overview of the changes. Note that you can uncheck any of the items at the left in case there are hundreds of entries. You can filter to only see changes in entered values, calculated values, formulas, etc.
The graph on the right will quickly tell you how many of each type of change was recorded in the spreadsheet:
Finally, you can either export the results out to an Excel spreadsheet or copy the results to the clipboard. The exported results showing the differences basically just exports the table that is shown above.
So that’s about it for Spreadsheet Compare. It’s a nice handy tool for those people working with multiple versions of the same spreadsheet and it can definitely save you a lot of time and energy. Enjoy!