Checksum tool: A simple routine to cure Excel hell

In a previous article, I wrote about how a simple checksum structure can greatly improve the auditability and robustness of financial models, and indeed Excel workbooks in general.

The focus of much current spreadsheet research centres on methods to reduce and identify errors. While checksums will not guarantee an error-free workbook, they will certainly perform a very useful ‘triage’ role for spreadsheet authors.

Checksums are particularly effective for large spreadsheets containing multiple worksheets because they alert users to real-time errors – so if a change to a single input cell produces an error elsewhere in the workbook this will be highlighted immediately.

As a result, checksums are especially useful during the construction phase of financial models, as well as for monitoring of workbook integrity when inputs and assumptions are changed later by users.

I was pleased to see readers liked the idea of using checksums in their Excel workbooks, but the feedback suggested that many accountants simply didn’t have the time to build a checksum structure into each of their workbooks.

This article sets out to respond to that issue by offering an automated way to create checksum structures within Excel workbooks that will also help avoid implementation errors.

Here’s how to do it:

1.   Open the VBA Editor of the workbook you are using (Alt + F11)
2.   Insert a new module into the VBA project like in the screenshot below

3.   Copy and paste the code from this text file into the new module you have just added

4.   Run the subroutine called sChecksumsSheetInsert by highlighting the title of the macro and clicking on the green Play button on the VBA Editor toolbar (under the option Debug as in the screenshot below).

5.   You will have now created the basic Checksums structure for every worksheet within the workbook. Time taken should have been around 30–60 seconds.

You will be alerted to any errors in the workbook by a conditionally formatted flag in Cell A1 of every sheet that turns red as soon as an error is detected in real-time on any worksheet within the workbook.

There is also a hyperlink for each worksheet that allows quick navigation to the worksheet with the error in question.

These are checksums in their simplest format. But you can also add more advanced custom checksums in the appropriate row of column F of the Checksums sheet, as indicated in the screenshot above.

You could, for example, create multiple checksums for a single sheet, perhaps referencing various important ranges rather than whole sheet ranges. This will help to pinpoint errors much more quickly and effectively.  Furthermore, you can include any kind of formula such as those to identify mistakes or to aid reconciliations. An example of this could be:-

“=IF(SUM(RANGE1)<>SUM(RANGE2),FALSE,TRUE)”.

Finally, if your workbook is in a format such as ‘.xlsx’ – which means it cannot contain VBA code – then you will need to remove the VBA module with the Checksums code before saving your workbook with the newly created Checksums structure and audit sheet.

Alternatively you can leave the module in and simply save your workbook in a macro-enabled or binary format (e.g. ‘.xlsm’ or ‘.xlsb’).

James Power is the principal and development director of FD4CAST. If you need any help in implementing the Checksums code you can contact him via email at james[AT]FD4Cast.com.