Safer business spreadsheets - a practical approach that works

If, like me, you've worked in organisations where the quality of many spreadsheets is terrible, you'll want to make them safer to reduce business risk caused by errors or misuse. 

I believe there is a practical and simple approach which can greatly reduce risk - an approach that is much more human than technical - which came out of research carried out within my previous consulting firm a few years ago, to improve its own spreadsheets.

I'd like to stress that this approach is aimed at ordinary business users, and not professional Excel developers, whose needs can be completely different.

But before laying it out for you, I need to share some of the research findings, because they are important. I've tried to include references where I can, but I can't remember where everything came from. This post is a bit long, but it's an important subject.

Understanding errors

1. Everyone makes mistakes

There is some spreadsheet-specific research. Ray Panko has spent many years working on spreadsheet errors, and the EUSPRIG group has put a lot of effort into how errors occur, and how to prevent them. There has also been a lot of work published on errors in general computer programming, testing programs, etc.

This research shows that everyone makes mistakes, at about the rate of 2-5%, whether they are experts or beginners. I'll say that again - everyone makes mistakes.

What this means in a professional business organisation is that no work can be trusted, so it all needs to be properly checked. This sounds obvious, but it is absolutely crucial to the approach suggested below, because checking is too often given a low priority in time and effort.

As one illustration of how bad checking is in practice, here is a paper on project finance models - you know, for projects like airports and tollways for millions of dollars - spreadsheets that need to be right. Look at the last page for the error rates on a number of actual models - it's scary.

2. Checking is hard

While some errors, like linking to the wrong cell, are relatively common, research has shown that there is a very wide variety of errors in Excel. I think this is the flipside of Excel's flexibility - just as there are many ways to build a spreadsheet, there are many, many ways to go wrong somewhere. What this means for business is that a standard set of checking rules is not going to cover all the bases.

There's worse to come. General research on programming errors shows that checking your own work is ineffective, and even checking by colleagues only picks up about 80% of errors. When I read this, I wondered why we didn't see more disasters in business spreadsheets, and I decided it was probably that most users use common sense to tell them when a spreadsheet gives crazy answers, and this is our main protection. Where we are most at risk is with a spreadsheet which is so complex that we can't estimate the right answer, and so we don't know if it looks wrong.

So, we all make mistakes, Excel gives us many ways to make them, and it's hard to find them all through checking. That's not an encouraging start.

The user

It doesn't get any better when we look at typical users. It may be different for you, but I see new graduates starting work with very poor spreadsheet skills. They are taught the basics (enough to do the work due today, anyway) by the person who was in their job before them, rather than by a senior person with experience and understanding of risk control. To me, this is like getting 18 year olds to teach 17 year olds to drive - do that for a few years and see what you get.

I also remember seeing research that showed that spreadsheet users typically over estimated their skills. It's no wonder we have problems.

This happens even in highly professional firms with excellent training programs. I suspect it's partly because many senior managers can't mentor youngsters because they never had the opportunity to develop strong spreadsheet skills themselves; partly because training programs focus on technical skills and not risk control; and also because spreadsheet issues fall between two stools - managers think they are an IT problem, and IT thinks they are a problem for the business units.

So on top of the difficulties in preventing errors, we often have relatively untrained and over confident users, often working without much guidance.

A practical approach to risk control

So what can we do to improve quality and reduce risk, given these formidable obstacles?

When we started researching, I expected that quality would correlate strongly with high skill levels and discipline, ie that it had a strong technical bias. Expert programmers would produce the best stuff, so what we need most is lots of technical training. Oh, and quite strong controls to keep people in line.

I was very wrong.

Instead, we found that quality came from understanding the truism that spreadsheets don't make mistakes - humans do - and that minimising the opportunity to make mistakes is the most effective way to reduce error. Think of how we try to prevent road accidents. Yes, we train and license drivers to give them technical skills, but skills are not enough. We also provide a number of tools and guides to make driving safer - cats eyes, road signs, traffic lights, speed limits, enforcement and penalties, etc. The authorities also try to change our attitude with ads and publicity campaigns, to prevent us taking risks like speeding and drinking.

In a similar way, spreadsheet users need more than just technical skills. Of course, this is widely recognised, and we discovered several good practice guides which provided lists of things to do (or not to do), but lists are...well, lists. It's hard to really put a list into practice, even if you memorise it or hang it on your wall. We needed something that our users could "get" more intuitively, to help them know what was good and what was bad.

For us, a big clue came from reading the advice of various well known professional software developers, who singled out complexity as the biggest enemy of good code. We thought about this. Clearly, checking is easier if the spreadsheet is simpler.

And then we thought about checking again, and we had the crucial insight that in business you don't build spreadsheets for yourself - you build them for other people - the checker, and other users. These other people are critical, because the real business risk is that they may miss an error or abuse the spreadsheet.

And so we found a single objective that - for us - brought everything together. And here it is:
Spreadsheets should be easy to check, and safe to use

"Easy to check" means anything that helps the checker to understand the design, check the factual details, follow the logic of the more complex parts of the spreadsheet, etc. Examples include

• laying out the spreadsheet clearly, logically and simply
• using a sensible and mutually agreed colour scheme to highlight key parts
• being consistent, eg in the choice of functions, or in sheet layout, to make the spreadsheet feel familiar to the checker. Teams often develop their own accepted ways of doing things, and even if there may sometimes be a better approach, this consistency makes things easier
• including explanations in cell notes, textboxes, etc
• pasting in extracts of other documents, or hyperlinks, to make it easy for the checker to confirm things
• anticipating what the checker will need and providing it as far as possible - eg if you have struggled to build a difficult part of the spreadsheet, the checker will probably also struggle with it, so it is worth spending a little time thinking about how to make it easier
• not being "clever" - eg where you have put together an ingenious but complex long formula in a cell, with about 10 functions, it might be much better to break it into several different cells with a clear explanation of each part

What I find interesting is how many of these guidelines are about people rather than about spreadsheets, and they could be summarised as "don't make the checker think" about things that are not central to checking, such as that obscure function you used, or your weird formatting. It's crucial for the checker to focus fully on what matters.

"Safe to use" means protecting the spreadsheet user from error. This includes things like

• data validation to check for invalid or strange inputs
• VERY clear labelling throughout
• removal of any ambiguities
• anomaly and/or reasonableness checks to highlight unusual results
• protection of key formulae where applicable
• including identifying information such as dates or version numbers on results sheets that may be printed
• anticipation of ongoing maintenance - eg if you have tables of data that may need to be extended, think about how this can be done and put a note in the spreadsheet, or even better, leave some room for new data. Maintenance can also mean changing formulae, so if you've had to use more than one formula in a column, mark it clearly so that nobody will change the top cell and mistakenly copy down all the rows

(Please note that all of this does not mean dumbing everything down to a beginner level - it means simplifying and clarifying to the level that makes life easy for your particular checkers).

Once you have a simple goal like the one above, you can revisit the various lists of good practices and weigh them up - do they make things easier or safer? They are no longer mantras to be followed blindly, but sensible suggestions which you can weigh, and accept or reject. I'm not going to try to give you my own best practices "list", because it is specific to my business needs, and I think it is best for each team to develop its own guidelines, with individuals feeling involved and actively participating.

User attitudes

I believe the most important attitude change here is for users to realise that they are building the spreadsheet for someone else, and that needs to be constantly in their mind.

Back in the days when we thought the answer lay in imposing guidelines, I once had a young user say "The guidelines take all the enjoyment out of my work. Excel was the one place where I could express myself at work". To me, that reaction says two things about users (and especially inexperienced users)

1. users need guidance, because spreadsheets are not artistic creations where you can experiment with colours, layouts, exotic functions and show off how clever you are - they are business tools which need to give the right answer. Period.
2. users need to feel challenged and involved, rather than just told to follow orders, otherwise they are going to fight you all the way.

Does it work? 

I had a taste of the results even before we did this research. I was working with an office in another city, and we found we had to document and comment our emailed spreadsheets more fully because we couldn't explain them in person. Even that one step on its own made a big difference to quality, and what impressed me was how quickly the younger users came on board and did really good documentation and suggested better ideas. So that was a good sign.

Since we did the research a few years ago, I've eaten my own dog food, applying the "easy to check and safe to use" approach to everything I do, and spreading the message to younger users. To my surprise, I find it has increased my own enjoyment in building spreadsheets, because it gives me a constant challenge to keep making things simpler, more checkable, and clearer. It's also made my own work much, much safer.

I've found young users to be very receptive, because the goal doesn't necessarily mean dull boring spreadsheets, but involves colours and design and user needs and other non-technical issues, and when they see all the design choices open to them, and they have a clear purpose, they find it stimulating and challenging. As I said above, I think the key to gaining user acceptance is changing it from an instruction into a challenge, which is to keep finding better ways to make checkable and safe spreadsheets. We all love challenges...

I also like that when I sit down with younger users and review their spreadsheets, I never have to say "I know better than you". Instead, I put our goal on the whiteboard, and I remind them that everything we do is aimed at meeting it. I find I can discuss different options freely without intimidating them, and they can often improve on my suggestions.

Another advantage of a simple goal is that it can be understood by people at all levels, even managers with little spreadsheet knowledge. We may not all know how to build a good spreadsheet, but we should know a bad one when we see it. Managers are important because the goal needs to become part of a team's way of doing things (what some call "culture"), so that people are actively rewarded and singled out when they do something that promotes safety or checkability, and so that people know where management's priorities lie.

What about skills training? We found it was still important, but it was no longer necessary to aim for very high levels of expertise for everyone. What matters is that everyone has sufficient skills for the job, and given the aim of simplicity, the barrier is not excessively high. It also matters that the whole team uses the same techniques as far as possible, for consistency, and this needs to be embedded in the training, as well as the realisation that you don't build spreadsheets for yourself, but for other people, so you need to understand their limits and their expectations.

Finally..

I found this research fascinating, because it opened up a new way of looking at spreadsheet quality - a surprisingly human approach for such a technical tool.

And on a personal note, I found it gave me a new and different enthusiasm. Whereas before, I was always trying to do new "clever" things with Excel, now I try to find simpler and safer ways to do things, which is equally challenging.

For example, I used to write a lot of VBA code, but now I have a rule that business rules and logic (eg industry specific pricing formulae or financial projections) don't go in code, because it is a black box which few people can check. I reserve code for automation and generic utility functions,and the rest has to go in the spreadsheet - and stay simple. This has not been easy in some cases, but it has been a lot of fun. And the quality is definitely better.

I'd be interested in hearing from anyone who has similar or different experiences in improving spreadsheet quality in a business environment.