Latest posts by Barnaby Mercer (see all)
- In Pictures: Kildrummy At AACE 2015 - July 8, 2015
- Kildrummy 2014 Retrospective - January 16, 2015
- Seven reasons why Excel* sucks for Project Control - May 20, 2014
* Ok… I’m picking on Excel because it’s the spreadsheet of choice, but this article applies to whichever spreadsheet you care to name.
Don’t get me wrong. I love Excel and use it most days. It’s ubiquitous, flexible and powerful. There are so many cool things that can be done with Excel that it’s hard not to use it as the default tool for problem solving: we can calculate, program, store data, analyze and organize. As tools go, it’s one of the most creative and versatile I can think of.
But, as is often the case, strengths become weaknesses in the wrong environment.
When designing company processes, one’s automatic reliance on Excel to provide the glue can, ironically, cause things to become unstuck. Think back to your own working practices. How many times have you searched for a particular spreadsheet, only to find several similarly named versions in folders and email? Or perhaps you have spent hours hunting for formulaic errors introduced by errant overtyping? What about trying to understand someone else’s marvelous and sophisticated creation? Infamously, the problematic Reinhart-Rogoff calculations were at least partially due to a difficult to spot computational error.
Sure, these errors are fundamentally human. The tool is not at fault, and there are ways to prevent these problems: we can try to introduce document control, version stamping, locked and password protected cells, macros, testing, and peer-review (c.f. Reinhart-Rogoff), etc. But these solutions all shine light on the fundamental problem: Excel is just not suited for sophisticated system design. Let me put it another way: Just because you can use a Swiss Army Knife for making a cabinet, doesn’t mean you should.
It is often said that only a poor craftsman blames their tools. But surely a good craftsman selects the best tools for the job in the first place?
So here they are, seven reasons why Excel sucks for Project Control – and what you can do about it.
1) Lack of version control
Here’s the scenario: you create a fantastic spreadsheet and email it to your colleague. She reviews the data, notices a number of improvements and duly sends you back a revised version. Meanwhile, you’ve spotted some different errors and have updated your copy. You both now have slightly different versions of the spreadsheet. Meanwhile, your colleague forwards the spreadsheet to two others, who tweak the calculations to suit their own purposes, and save it (with the same filename) locally. Now four versions exist – two with similar but different data, and two more with subtly different formulae. All four are saved with the same filename. And the problems snowball from there.
Everyone tinkers. And just because multiple versions of the spreadsheet look the same, doesn’t mean they are. When many versions of a spreadsheet exist, only the most robust version control will ensure you’re working on the right one. In a team of any size, the tendency toward chaos increases, when similar versions of the same spreadsheet are emailed around and tweaked.
What can you do about this? Processes that require version numbering against filenames go some of the way, but these rely on people remembering to correctly update the filename. Better yet is a system such as TeamBinder, which can keep documents synchronized and controlled within your organization.
2) Too much freedom can be a bad thing
You can create formulae and routines to process the data in useful and creative ways; you can save time by recycling and tweaking existing sheets; and you can easily experiment with new ideas and concepts. All good. But how do you prevent errors? What happens when seemingly minor changes impact other cells on entirely different sheets? Checksums and manual testing provide some form of error checking, but they are limited – not every change can be checked for. And many aren’t. These techniques are also labor intensive.
So keep spreadsheets as simple as possible, and avoid the temptation to be too clever. Remember, you’re not necessarily going to be the one trying to understand how it works later.
3) Home-made, bespoke solutions are fragile
You’ve created a wonderful sheet that calculates and transforms the data in useful and impressive ways. Simply enter values here, here and here, press the button over here and ‘Voila!’ the (hopefully) correct numbers are displayed in a new sheet, ready for copying. But what happens when you go on vacation and your team need this data? Where are the instructions? There are instructions, right? Which copy is the latest version? Are the numbers correct? How are errors handled?
In an organization with creative and talented people, the reliance on a large number of clever spreadsheets will grow. And if (when!) someone leaves, their knowledge of the system goes with them. Don’t find yourself at month-end with a gap in the process.
Being reliant on bespoke solutions is risky, but how can you mitigate this risk? Documentation is obviously a good place to start. Ensure that all mission critical processes are well documented and clearly defined. Nominate a backup expert; someone who can pick up the process and carry on if the worst happens.
There’s a strong argument here for not using home-made solutions at all. Using a well-tested system supported by a strong development team means that you’ll always have the backup you need to answer questions and resolve problems. Look for tools that achieve your aims, and invest in the appropriate training to ensure your team are able to use the solution effectively.
4) Difficult to debug
Not only are problems difficult to fix, they are probably not easy to find in the first place. It is often not immediately apparent which cells are just values, and which are derived from formulae. Nor is it always clear which cells (perhaps in adjacent sheets) are affected by the value you’re about to replace.
Unfortunately, there are no easy solutions to this problem. Again, documentation helps to alleviate the problem; as does reducing the complexity of the spreadsheet, but let’s face it, you can’t always simplify your way out of trouble.
5) Wastes effort on repeated crafting and polishing
Be honest: how much time have you wasted getting your favorite spreadsheet looking beautiful? And the next one? Efforts invested in polishing one spreadsheet are not immediately transferable to another.
Try using templates to define a corporate style. This can give you a consistent look and feel, and it also reduces the time spent tweaking. However, it can be difficult to resist the urge to create a one-size-fits-all solution.
6) It’s not a database
Excel can store lots of data, but it does have limits. As the quantity of data added to a spreadsheet grows, performance drops. Not only this, but adding new fields, sorting, organizing and querying the information gets progressively more time-consuming. This effort creeps up gradually and we don’t notice the overhead.
Again there are no good ways to solve this using Excel alone. You simply need to accept that when you have a huge and complex data management task, you will suffer from Excel’s performance limitations.
7) It’s worryingly insecure
Excel makes only limited provision for data security. You can achieve rudimentary file-based security using your network file system, but any more sophisticated attempt to implement role-based access to the data will rapidly become cumbersome.
There is no auditability, so your spreadsheets are very unlikely to retain a detailed audit trail of who made what changes and when. An erroneous or potentially fraudulent data change can therefore go undetected – impossible to trace back to its source.
But it gets worse: it’s all too easy to place a copy of your corporate data onto a USB stick, carry it out of the building and then leave it behind in a coffee shop. There is always a temptation to attach your Excel files to an email, but the recipient might then accidentally forward that email to someone outside of the organization.
The only true solution to these problems is to use a fully featured enterprise database solution. These provide a robust solution to data security; they maintain data integrity and allow for automated backups; and they give you a consistent interface for entering and maintaining your data. They are the truly smart solution.
There are lots of things that Excel does really well, and for many ad-hoc calculations and analyses I’ll often turn to it. But if I’m designing a key part of my business process, I will always use a professionally designed database system.