It can be very useful to compare two versions of the same workbook to see what has changed. Here are some tips to help you do just that.
- Open both files
Open the old and new version of the workbook and select the same sheet. If you know where to look e.g., the assumptions sheet, select that sheet. Otherwise select an output sheet such as the financial statements.
- Check workbook settings
If you have scenarios or other settings that affect outputs, ensure both versions have the same settings, so you are comparing apples with apples.
- Make them look the same
Make sure the selected sheet in both workbooks is the same size e.g., 100% or 85%. You can find and change this in the lower right-hand corner. Ensure you have the same view in both e.g., by using the shortcut Ctrl Home/Pos1 to make sure the selected cell is the same in each workbook.
- Then make them look different
So you can easily tell which workbook you are looking at, and also to avoid making changes in the old version by mistake, change the colour scheme in the old workbook under Page Layout, Colors, select color scheme – I find the garish “Red Orange” great for this purpose.
- Flick between the two workbooks
Simply use the shortcut Ctrl Tab as often as you want. The human eye is very quick to identify changes so you can quickly identify what has changed. If necessary, do a single page down (shortcut Ctrl Pagedown) or page across (shortcut Alt Pagedown) in both workbooks to view and compare the next section.
- Alternatively, create a difference sheet
Copy the sheet you are comparing and in the copied sheet, in the first cell with a number (e.g. D8), enter a comparison formula e.g.,
='Fin stats'!D8-'[2023-02-08 ProCam model v2.xlsx]Fin stats'!D8
Then copy this to all relevant cells to see the changes.
(Note: when you link to a single cell in another file, Excel fixes the cell referred to with $ symbols, please remove these before copying the formula).
- Drill-down
Once you have found a difference and want to understand what is causing it (assuming it is not an input), trace one or more precedents in both workbooks and compare these using step 5 or 6.
To trace precedents, use the Formulas ribbon, Formulas Auditing, Trace Precedents tool or use my all-time top tip: double-click to jump to the first precedent. For this to work, please check your settings: File, Options, Advanced, Editing options (first section), “Allow editing directly in cells” must be switched off (remove tick mark). See my video for details.
Tool tip: Microsoft also offers an Inquire tool that can compare workbooks for you, but unfortunately it is not available in all versions of Excel. Here are details from Microsoft on how to find out if you have it and how to use it, if you do.
