Wednesday, March 5, 2014

How To Compare Two Excel Files using SpreadSheet Compare

Office 2013 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.
When you first open Spreadsheet Compare, you will see the main program screen, which looks like this:
speadsheet compare
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.
compare two excel files
Now you can quickly see by the color-coded cells what values or formulas are different.
excel differences
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.
compare two spreadsheets
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.
see differences
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.
table view
The graph on the right will quickly tell you how many of each type of change was recorded in the spreadsheet:
changes graph
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.
exported results
So that’s about it for Spreadsheet Compare 2013. 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!

0 comments:

Post a Comment