> Business Tools
Agile Spreadsheet Development
Many programmers use Agile Software Development, a collection
of techniques that reduce errors and speed software development.
Here's how Excel users can use similar techniques to gain similar
by Patrick R. O'Beirne
Spreadsheets are too easy to use...at least from the perspective of
Easy spreadsheet development permits an unplanned approach that
often results in unstructured models with non-validated data. Easy
development encourages too many errors.
The European Spreadsheet Risk Interest Group  has held five
annual conferences about this subject. As the concern about business
exposure from end-user development has grown, each conference has
been bigger and seen better-researched papers than before.
The danger is real. Because spreadsheets are easy to change, they
lack version and audit controls. (AllFirst fraud .) And poorly
documented spreadsheets permit easy operator error. (Transalta $24M
This article suggests how certain methods, many of which are
attracting more attention by programmers, can help Excel users
improve the Excel-development process, save time by making fewer
mistakes, and endure less of the cost and pain of bad spreadsheet
Two Heads Are Better Than One
Having two people working together at the keyboard has the effect of
continuous peer review.
Ray Panko, in his study of spreadsheet error rates  finds that
“[pairs] reduced errors by about a third”. XP advocates
 say “It
is counter-intuitive, but two people working at a single computer
will add as much functionality as two working separately except that
it will be much higher in quality.”
A good practice is to deploy spreadsheet champions or “gurus” whose
experience can be passed on to others in a
Experienced spreadsheet builders know that time spent planning the
spreadsheet up front saves time re-working a bad structure. They
save their work often and keep backup copies so they can revert to
previous known working positions quickly.
Master modelers have rules of thumb such as: Put constants in their
own cells. Create a structure that is easily auditable. Create
formulas with relative and absolute addresses to make block copying
easier and safer. They know how to use Excel's built-in functions so
they can avoid re-inventing the wheel or producing wobbly wheels.
A “moving pairs” team structure helps such techniques get passed
around quickly, spreads knowledge across teams, keeps thinking
fresh, and avoids bottlenecks. When new people join a task, the
questions they ask to get up to speed show what needs to be
clarified or simplified in the task, which ultimately makes the
system easier to maintain.
Some financial industry environments have performance bonuses or
competitive pressures. There, working as pairs may be unacceptable
to the Excel jockeys, even though there are risky conditions and
intense time pressures -- in merger and acquisition analysis, for
In these conditions, the managers need to decide the extent to
which the spreadsheets are personal scratchpad tools that always
disappear after a project, or corporate assets that are re-used.
Bear in mind that many "one-off" creations are handed on and become
embedded in the decision making process, precisely because they
proved so useful. In such cases, they should really be treated with
the same care as other Information System assets.
Test Early And Often
Naïve spreadsheet creators often lack a concept of testing, except
to ask, "Does it look right?" However, the mere appearance of
correctness may deceive in a couple of ways.
An answer that is expected, unfortunately, may be accepted as
verification of their intuition – even though both the model and
their intuition may be wrong and a nasty surprise lies in wait for a
business decision based on such an output.
An answer that is obviously different from expectations might either
give some business insight (which is often the purpose of a
projection from present knowledge) or the spreadsheet author might
decide that their pre-judged answer has to be right and will bend
the model to fit. That might be regarded as mere political show to
obtain apparent "objective" support, and risks being found out in
the most embarrassing possible manner.
When you do not know what the expected answer is – that is why you
are building the spreadsheet – then there still is a need to perform
some parallel calculation to provide some kind of independent check.
Good practice, then, is to build in tests from an early stage. The
simplest and most common test used in accounting models is to check
that totals across and down agree. More sophisticated tests use IF
functions to display a message beside an output cell when a result
is not as expected.
Input cell validation can be specified in Excel to check for
typed-in values being whole numbers or within a given range. But it
is easily defeated by copy-and-paste operations, so it cannot be
VBA can be used to automate test suites that perform basic batch
tests such as clearing all input cells, and setting them all to
known values, and checking a final result cell.
Debugging and Maintenance
Tools are available to audit spreadsheets for unusual patterns, such
as numbers or inconsistent formulas appearing in the middle of
blocks of formulas. They can be used to compare workbooks and report
changes other than simple expected data changes.
Excel has built-in auditing tools which now include a multi-level
formula evaluator that permits tracing the precedents of a cell to
see where the data comes from; and the dependents to see where a
given cell is used.
As spreadsheets grow, people become afraid to touch certain parts of
them. You can tell that this is happening when you see notes added
to say things like, “If you change this, be sure to change that
other cell as well.” Such sprawling monsters are growing out of
control, so good practice here is to simplify the model continually.
There is a natural reluctance to spend time on clean-up because
there is no immediate reward - the model simply (or perhaps I should
say ‘at best’) works as before. Take a longer-term view to
understand the future benefits. How often have you found supposedly
‘once-off’ models that are actually too useful to throw away?
A spreadsheet often is used to incrementally explore tough technical
or design problems to reduce risk or get a better estimate of how
much work is going to be needed. We normally hate to throw away such
work. But it is often cheaper in the long run to treat the prototype
as a "mock-up" and build a more robust and scaleable solution in a
structured modeling language or around a database.
Spreadsheets usually “just grow” by incremental development. They
start off as simple tables used to summarize data for reporting, and
are extended as the knowledge of the business develops.
A large system developed or evolved from multiple spreadsheets is
much more like a conventional systems development project, so there
is a need for IT to offer support with their experience of
integration testing. This article is addressed to those who develop
spreadsheets for their own use. Those who develop for others are
really getting into professional services and need to adopt a much
more disciplined approach to producing robust and secure products.
This means requirements analysis, design before development,
testing, and reviews. Professionals also apply specific techniques
such as password worksheet protection and menu reduction to reduce
errors from accidental changes in data or structure.
Here are some good ways to clean up worksheets.
- Extract constants into their own cells.
- Add cell comments.
- Document worksheets.
- Add worksheet protection with a password, while making sure that
some other responsible person has a written copy of the password.
- Add range names to make formulas self-documenting and readable.
- Set up styles for color mapping and for currency and quantity
- Establish other standards to aid comprehension and alert data entry
users to the meaning and usage of cells and areas.
- Break long and complex formulas into separate cells.
- Apply naming conventions and modular structures in VBA code.
- Restructure calculations into a logical flow that reads top-left to
bottom-right, and the front spreadsheet to the back one.
- Provide batch testing macros.
- Save commonly used models as templates to help good structures
Agile developers avoid the “I’ll just do this too as I’m at it”
syndrome, adding functionality before it is scheduled. They believe
that these guesses at what might be needed in the future are not
justified, so they should just add what is needed for today.
If you are developing for yourself, you are probably better placed
to judge what you will need in future, but the advice of starting
with a simple, sound structure is still good. The aim is always to
have a working model, even if it is initially at a low level of
resolution. That way, if the schedule is suddenly curtailed
(surprise!) you at least have a usable snack rather than a
half-baked heap of spaghetti.
This drive towards simplicity starts with the imperative “Do the
simplest thing that could possibly work”. It's always faster and
cheaper to replace complex logic now, before a lot of time is wasted
Much of the delay in conventional project delivery comes from a lack
of coordination among the developers. Where there is not constant
interaction to set and reset: expectations, acceptance criteria,
scope, priorities, and schedules, then customers and developers can
turn a simple project into a complex one.
In agile methods, this interaction is brought to weekly or even
daily interaction. In end-user development, there is no delay but
also, there often is little reflection and pause to think how things
might be done better.
It is possible to escape the end-user development nightmare by
adopting good advice and making use of training and experience to
assist people to do their work with fewer delays and problems.
Copyright © 2004 Patrick O’Beirne, Systems Modelling Ltd., Ireland.
 http://www.eusprig.org European Spreadsheet Risks Interest Group
fourth annual conference, 2003.
gre.ac.uk/~cd02/eusprig/2001/AIB_Spreadsheets.htm [this link is no
longer is active...Charley Kyd]
“The role of spreadsheets in the AIB/Allfirst currency trading
fraud” Ray Butler.
$24-million spreadsheet "clerical error" article in The Register
panko.cba.hawaii.edu/ssr/Mypapers/whatknow.htm [this link is no
longer is active...Charley Kyd] “What We Know About Spreadsheet
Errors” Raymond R. Panko University of Hawai’i
http://www.extremeprogramming.org/rules/pair.html “What is