The creep of spreadsheets into areas that were previously the domain of ‘proper’ system developments is a big cause for concern.

I have written before about the myriad of ways in which spreadsheets – specifically the poor use of spreadsheets – causes problems. But there is more to get off my chest on this topic, especially about the way spreadsheets can form the basis of routine, repeated tasks on which organisations depend; when the spreadsheet becomes the system.

Computer systems are everywhere; they support processes throughout organisations across the globe and historically, have resulted from development projects involving a range of skills including business analysts, data architects, software engineers and testers. But the rapid increase in IT power means that much of what was previously only achievable with a big system development project can now be undertaken on spreadsheets. The temptation to throw together a spreadsheet to support a business process is often irresistible.

You can think of a traditional system development as being made up of three layers: a data layer, a business layer and a presentation layer. The data layer will be structured and managed to store the system data in a way that ensures conformance with specification and facilitates the application of any quality assurance that is necessary to ensure that the system operates correctly. The data specification will be modelled to ensure maximum flexibility and integrity and to minimise (or eliminate) duplication.

The business layer contains the processes that act upon the data; inputs, outputs and algorithms that transform and apply the rules of the process. These algorithms are the business logic and they will be created with performance and maintainability in mind. They can be tested with data sets that, when processed, should give expected results. Any change to the business layer should be followed with tests relevant to that change as well as regression tests using previously created test data sets.

The presentation layer provides the human interface to the data and business layers. The design and testing of the presentation layer follows a very different paradigm to the data and business layers. The presentation layer brings together the elements of the system that makes the most sense to the human user and allows easy navigation and operation, often through a number of different presentation platforms.

This separation of system layers allows for different approaches to be taken to the design and management of each layer. Each layer can be designed in a way that is optimal for quality, for performance and for maintainability.

When systems are developed in spreadsheets, the data and the business logic are normally placed on the spreadsheet in a way that provides the optimal user-interface; the presentation layer becomes the dominant design paradigm acting upon data and business logic. This normally results in the structuring of data and business logic in ways that are incredibly difficult to maintain and quality assure. Things inevitably go wrong.

I believe that this collision of design paradigms is the biggest single driver that causes 88% of spreadsheets to contain material errors.

Authors note This article was originally drafted with the title “More on spreadsheets”. The combination of a typo and my computer’s auto-correct functionality improved on that title.