I built a beautiful spreadsheet that was required for tracking a series of workshops I was facilitating. Foolishly, I made considerable updates to an older version and once I realised, my heart sunk. Unlike Word, Excel doesn’t have a native file comparison tool, which is sensible due to the complexity of a spreadsheet. However, Windows does come with a tool called Spreadsheet Compare which allows you to compare two workbooks to see the differences between them, line by line. You can see how values, formulas, and formatting differ – line by line, and cell by cell.
Accessing the Program
At the time of writing, Spreadsheet Compare only came with Office Pro 2013. Simply type Spreadsheet Compare in the search box and if it is installed, it will show.
Opening the Files
Once you have opened the program, click compare files. Choose the two files you want to compare and they will be loaded into the tool.
Comparing the files
Once both files are open, you can compare sheet with sheet, cell with cell. Any differences will be highlighted and allow you to perform further investigation. You can also choose the level of difference to look for, for example, compare text, calculated values or formulas. It even will tell you structural changes such as formatting and column widths!
Export the Results of the Compare
Once you have compared the files, you can then export the changes. I couldn’t see a way that you could make the changes in the tool and export the consolidated file, but you can export the cell references and then do some trickery using the
indirect() formula to update automatically.
This tool is very comprehensive and a much better way of comparing two files then manually eye-balling the data. It’s no substitute for good version control but if an accident happens, be sure to try out this tool. If you have any questions, let me know in the comments.