The ICAEW’s David Lyford-Smith discusses the issue of spreadsheet risk and how it can be identified and tackled.
This past month has been an interesting one for me at Chartered Accountants’ Hall. I have had several Excel discussions and meetings, including hosting a webinar, running a training course, and joining a meeting of our active volunteers on the Excel Community Advisory Committee. While all the topics have been different, there is one theme that always comes up in these discussions – and that is spreadsheet risk.
We have been talking about spreadsheet risk since the Excel Community was founded. I was a volunteer myself at that time, and the discussions (now around four years ago) eventually led three years ago to the publication of the Twenty Principles for Good Spreadsheet Practice that I summarise each month in this blog.
Reaching a definitive list of guiding principles for spreadsheet use wasn’t an easy task, but we persevered with it because we believe that poor spreadsheet use costs the economy a considerable amount. There’s little research on the specifics, but we all have encountered moments where we learned about a great tool just a little too late to avoid some hefty work, or encounter a mystifyingly complex spreadsheet that takes us hours to unpack and fix.
In large part, a lot of the issue with how much poor spreadsheet practice costs is how few people and organisations take it seriously. Many believe that spreadsheets aren’t a significant part of their systems, but my experience points to Excel as the first destination for middleware – either tidying up input data, or making quick analyses of exported information from the ‘official’ software.
Spreadsheets put powerful tools in the hands of non-programmer users, and that accessibility ensures their enduring popularity. But it also ensures that poor spreadsheet knowledge and habits continue to affect organisations of all sizes.
Various research has shown that users of all kinds make errors in individual spreadsheets cells between 3-7% of the time, which in aggregate leaves us with ~90% of spreadsheets containing errors, and often significant ones. Self-review only catches so many of these errors; working in teams and peer-reviewing is the only reliable way to significantly reduce errors, as well as training in good practice.
Even with the Twenty Principles going strong, ICAEW has more to do here to provide leadership on how these risks can be identified and reduced. It’s something that will keep us busy for years to come.
Tip of the Week highlight – FREQUENCY
This week, we are taking a quick look at the FREQUENCY function, based on a more detailed post in Excel Tip of the Week #183.
FREQUENCY is a function that can easily produce histogram-style data from a table, telling you e.g. how many items fall into each of a number of “bins” of given sizes. It’s a bit unusual, as it’s an array function – one that spreads over several cells. Let’s take a look at an example:
The function gets written in a special way – you highlight the range that will contain the function, then enter the formula, and the press Ctrl + Shift + Enter to confirm the array formula.
The syntax for FREQUENCY is as follows:
=FREQUENCY(data, ends of bins)
You can see in the example above that the formula is written over one additional cell compared to the bins – that’s so that all the data which is too large for any bin can be left in the final overflow cell.
Principle of the Month
This month’s selection from the Twenty Principles for Good Spreadsheet Practice is #5:
Before starting, satisfy yourself that a spreadsheet is the appropriate tool for the job.
When all you have is a hammer, every problem looks like a nail. Excel is a massively powerful tool, with a great deal of flexibility. As a result, it can be tempting to default to it, and use it in any situation and for any issue. But Excel is a spreadsheet program and has its limitations – there are plenty of cases where other dedicated software will be more appropriate. A particular weakness of Excel is in printability – with rows and columns differing wildly in size, Excel struggles to reliably print in a legible and efficient manner. It’s also not a great word processor for similar reasons, with text wrapping being very variable depending on screen size and often differing between users.
Don’t struggle to make the thing you’re doing in Excel if there’s an alternative that would do it better – and keep Excel for the data- and analysis-driven applications at which it truly excels.
David Lyford-Smith is a technical manager within the ICAEW’s IT Faculty and blogs for the ICAEW Excel Community. The Excel Community is an Excel content hub that encompasses webinars, blogs, member Q&A, fortnightly bulletin updates, and more. Find out more here