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.
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
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
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
- 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
- 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
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)
- 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.
- 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.
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
Feb 11 2008, 04:17 PM