One of the most powerful processes when working with spreadsheets is the ability to compare spreadsheets in Excel to identify what was changed and how it affected the end result. Often you will email out a spreadsheet and when you get it back, the results have changed and the user did not note every change they made. If you try and replicate the changes your results differ and now you need to see what else has changed!
Excel’s inbuilt tool to compare one spreadsheet to another
Unbeknownst to most users there is a free built in add-in to help you compare one spreadsheet to another.
The easiest way to see if you have it is to check your addins.
As shown below click on the File ribbon and then on Options
Click on the Add-Ins menu item, then change the Manage dropdown to COM Add-ins and click the GO button.
In the subsequent dialogue box, see if you have an option called Inquire (with an I not an E). If you do, tick it and click OK. If you don’t you need to check your version of Excel. You can see more on the Microsoft Inquire page about which version is correct.
If you have it and have loaded it, you should see a new ribbon item called Inquire and you will see the button that allows you to compare spreadsheets in Excel.
The types of comparisons you can do
We will go into more details in other posts but this tool allows you to find changes between 2 spreadsheets of many types:
Below the ones we find most useful and important, and what we watch out for.
- Entered Values- What changes were made to input cells e.g. changing the WACC rate, increasing growth rates etc.
- Calculated Values- What results have changed from the previous version? Where have the revenues gone up, costs come down?
- Formulas- which formulas have changed? Did someone add a + 1 000 000 to a formula to make it look better.
- Structural- Has anyone inserted rows/ columns which means that your other spreadsheets that rely on this spreadsheet are now looking at the incorrect places?
- Macros- were any changes made to the VBA code?
- Cell Format- has the format been changed? Has the WACC been slightly changed but the format of the numbers (less decimals) as well so that it is not so noticeable.
- Cell Protection- which cell protections have been changed (either protecting or unprotecting)?
- Sheet/ Workbook Protection- Has the sheet or workbook protection changed?
By being able to run all these comparison checks between Excel versions quickly, you will have more comfort and understanding about what happens from version to version (keeping version control is import- look at our Free Excel spreadsheet best practice).
Related
Financial Modelling, Budgeting and Forecasting in Excel (Course)