The essential spreadsheet skill that has nothing to do with spreadsheets

There's a lot for any accountant to think about before taking on a spreadsheet project. As the ICAEW 20 Principles for Good Spreadsheet Practice set out, even before you start, you need to establish that a spreadsheet is an appropriate solution to the problem you are seeking to solve. (Principle 5: Before Starting, satisfy yourself that a spreadsheet is the appropriate tool for the job). Fortunately, the difficult ethical question of whether you possess the necessary skills to undertake a spreadsheet project has been made much clearer by the introduction of the ICAEW Spreadsheet Competency Framework. The framework identifies four different types of spreadsheet user: Basic, General, Creator and Developer, and clearly sets out the skills required for the different levels of spreadsheet involvement, with essential 'core' skills differentiated from 'beneficial' skills.

Recently, I've been working on a substantial project that started off with a set of several dozen interconnected spreadsheets. The initial aim was to overcome some existing usage and performance shortcomings, reduce the possibility of error, simplify the processes involved and extend the reporting capabilities.

There would have been some strong arguments for applying Good Spreadsheet Practice principle 5 and rejecting the idea of using spreadsheets at all. However, as is often the case, the client's familiarity with spreadsheets and their desire to avoid significant changes for the system's users, meant that a complete move away from spreadsheets would have been difficult.

It would have been possible to adapt the existing system to tidy up some areas and build in additional checks and controls to address reliability concerns. Because of the amount of data and the complexity of the structure, it would have been much more difficult to address the reporting issues. The need to collate data from hundreds of different sheets, across dozens of different workbooks, would have challenged the use of standard Excel formulae and functions, and would probably have required some extensive VBA coding.

I would never claim to have come across the best possible solution, but the solution that was eventually implemented did reduce the number of formulae involved by a factor of thousands and greatly simplified the data entry process. It also enabled reporting across the entire data set, while keeping the data entry and reporting within the familiar spreadsheet format.

It will probably come as little surprise to anyone who has read some of my more recent articles that the solution adopted relied heavily on Power Query, with a few lines of VBA and maybe a couple of dozen different Creator level formulae.

Looking back at the project, I realised that one particular skill had been vital in coming up with a workable solution and, as indicated by the title of this article, it wasn't a typical spreadsheet skill. Instead, it was the ability to work with relational databases. Without some knowledge of how databases work and, in particular, knowing how creating and joining tables of data could enable the types of calculation that the client needed to perform, I'm not sure that we could have structured the system to achieve the stated goals within the spreadsheet environment.

In fact, the majority of significant 'spreadsheet' projects I have been involved in in recent years have either combined spreadsheets and a database to create the solution or have replaced the spreadsheet entirely. This may well be because I only tend to get involved once people have exhausted their own spreadsheet expertise, and the problem has gone beyond using a few advanced functions and sprinkling of VLOOKUPs. Often, the issue that marks the transition between spreadsheet and database is the need to cope with dynamically-changing numbers of rows and columns or the need to cope with more dimensions of data than allowed for by a single grid of rows and columns.

Just to return to the ethics issue, there are many potential projects for which extensive spreadsheet expertise and experience would enable some sort of spreadsheet solution to be created, possibly involving complex formulae and with VBA filling in any area where formulae couldn't cope. However, in some of these cases, a solution depending more on database techniques than spreadsheet techniques, might produce a quicker, simpler, more robust and more adaptable solution. Where a spreadsheet project involves tables of data, whether in external systems or within the spreadsheets themselves, making the correct decision about Good Spreadsheet Practice principle 5 may have as much to do with a knowledge of database structure as with spreadsheet expertise alone.

This blog is brought to you by the Excel Community where you can find additional blogs, extended articles and webinar recordings on a variety of Excel related topics. In addition to live training events, Excel Community members have access to a full suite of online training modules from Excel with Business. There is also an online forum where you can ask questions and share ideas with other community members.