As each worksheet grows in size and complexity, and as workbooks accumulate more and more worksheets, it becomes increasingly difficult, if not impossible, to compare Excel workbooks to see how it has been modified.

It might take days or weeks to perform the tedious task of manually comparing the formulas or values on one worksheet with those on another. Even then, you would not be able to prove to someone else that you had not made a mistake while performing the comparison.

Some scenarios where you would want to compare excel workbooks

• You have made several changes to your workbook and saved it as a new version. After a week you realize that one of the changes you have made has resulted in unexpected side-effects.  But in order to figure out which one, you need to know all of the differences between the current and previous versions.
• You are part of a team building an Excel model. One of the other team members is out of the office for the day and you need to ascertain how the model was altered yesterday in order to decide what you need to do today.
• You have finished building a model and are running a simple sensitivity analysis. Although you have a good idea about which outputs should change when you alter specific inputs, you want to be absolutely sure you don’t miss anything.  This is a good way to test the logical flow of your workbook.

So why wouldn’t a straight cell by cell comparison work?

One of the most common changes in a workbook are row insertions and/or deletions, as well as columns – but most frequently rows when the new cash flows are inserted or a new subtotal row is required for a group of costs.

But while we can rely on Excel to automatically update any affected calculations by updating all cell references that shifted due to the changes on the spreadsheet structure, comparing the exact same cell reference before and after the changes will most definitely by flagged as a difference when, in fact, the difference is only due to the repositioning and not to a genuine difference in the formula.

This is why an alignment between the comparing worksheets is absolutely vital to identify those real differences.

• Read more: How to compare worksheets with OAK

How to manually align two worksheets before comparing formulas and values

Presuming that most of the cells on the first columns of a spreadsheet are used for descriptions and labels, it is reasonable to assume that these ‘descriptions’ do not suffer major changes between the compared workbooks – a ‘Revenue’ label on an initial version of the spreadsheet will be most probably remain as ‘Revenue’ on future revisions even if it’s location changes – the same can not be said from the ‘Revenue’ formula.

So, if we compare the labels between versions it will be possible to identify the differences originated by row shifting. Here’s how we can make this comparison:

Aligning two worksheets manually

For simplicity, let’s name the workbooks to be compared as ‘Version1’ and ‘Version2’.

1 – Open ‘Version1’ and select the worksheet to compare.

2 – Select the contents of all cells that contain labels – as mentioned previously, these are usually the first columns – and copy:

3 – Open a new workbook and paste values (Ctrl+Alt+v and choose v for values followed by Enter) in a new worksheet.

4– Now open the ‘Version2’ and repeat steps 2 and 3 but pasting the contents to the right of the existing contents of the new workbook:

5 – We need now to find the differences between labels so let’s create a simple formula for each column returning True if the label is identical or False if there’s a difference.

Copy down and across the same formula.

6 – To identify all the ‘False’ values that are signalling different labels, the use of Excel’s conditional formatting is recommended – type ‘False’ as the value to be found and formatted:

At this stage, the “comparison” workbook has now all the differences between labels perfectly-identified (in our example, with a Light Red fill and Dark Red text)

While it is clear that the first ‘False’ signals a mere change of labels between the two versions, the label ‘Other revenue’ is now a row down to its original position – so there is a new row on the latest version stating ‘Different wording’ (boxed in green).

To have both aligned a new row above row 32 needs to be inserted on the first version:

7 – Because our alignment workbook does not have any links to the original labels, it is now needed to copy & paste the very same labels again to it and check the test columns – this should be the result after updating the version 1 labels.

The process must now be repeated in all labels that do not have a match on version 2 until all ‘False’ flags are due to simple label changes and not row offsets.

This is the alignment workbook with all the necessary rows inserted:

The remaining ‘False’ tests are signaling label differences, but all rows are now matching between the two compared worksheets.

A comparison on a cell-by-cell basis would now be able to identify only the genuine differences and not those caused by row offsets between versions.

While this method does reduce the risk of changes being reported as such due to misalignments, it is extraordinarily time-consuming (especially when applied to the entire workbook) and still subject to human error – hence the logical alternative of using an Excel add-in for Comparing Workbooks.