Accounting Mechanics

Simon Murphy’s blog is an interesting read for those of us keen to make the best use of Excel. Identified in his post on the disadvantages of spreadsheets is that not separating “data” from “view” is a bad thing. This is the key reason why I recommend keeping data in clean lists, without contamination from the layout, subtotals, etc. that belong in a “view”.

MVC

Many people will be unfamiliar with the “Model-View-Controller” pattern used in programming, so I’ll expand briefly on the idea and reasoning.

The basic idea is that a complex program is usually more robust and easier to work with when composed of separate, well-defined modules that work co-operatively. In the MVC pattern, the “model” broadly stands for the program module that holds data and the “view” is the program module that presents the data to the user. The pattern involves making these separate and, in particular, ensuring that the data is independent of how it is viewed.

Achieving this separation provides several huge benefits, including:

• data can be tested for accuracy and completeness independently of any other factors, such as whether a report has ‘read’ the data correctly,
• the work involved in maintaining and updating views/reports is limited to only one part of the program; the data itself remains intact and safe,
• it becomes very easy to provide multiple and different views of the same data, without having to duplicate or modify the data in any way.

Implication for Excel

Excel provides blank worksheets, inviting you to structure your data however you like. Learning from the MVC pattern, we should provide good structure, even though Excel doesn’t require it. Generally this will involve capturing data in clean lists and using separate sheets, pivot tables, etc. to provide the output that we want.

For small, one-off workbooks, don’t bother. But if you have lots of data, or will be using the same workbook over a period of time, then consider keeping your data and your reports separate.