When we carry out a requirements or task analysis on a new project, it’s quite common to hear something along the lines of: “Oh yeah, we use spreadsheet ‘x’ to store this information, then we copy this section to spreadsheet ‘y’, email that to Jim who fills in some data and emails it back…”. Sound familiar? Yes, here at Purple Crane we come across a lot of spreadsheets. Our customers use them and we use them every day ourselves for both internal work and on customer projects. But, are you or your team relying too heavily on them? And are you using them efficiently?
When we carry out a requirements or task analysis on a new project, it’s quite common to hear something along the lines of: “Oh yeah, we use spreadsheet ‘x’ to store this information, then we copy this section to spreadsheet ‘y’, email that to Jim who fills in some data and emails it back…”. Sound familiar?
Another common situation we come across is where a dozen or so people have each built and maintain their own spreadsheet using pretty much the same data to do pretty much the same tasks. Maybe that one’s familiar too? All that duplication of effort! Not to mention the potential for inputting data incorrectly.
Yes, here at Purple Crane we come across a lot of spreadsheets. Our customers use them and we use them every day ourselves for both internal work and on customer projects. But, are you or your team relying too heavily on them? And are you using them efficiently?
Are spreadsheets putting your organisation at risk?
Spreadsheets are extremely useful, flexible and powerful business tools, and arguably it would be hard to get by without them. But is your business using them to manage important business processes or to store critical data?
If your answer is yes, then you need to assess your spreadsheet situation, because if you choose a spreadsheet over a more appropriate tool, you may be inviting unnecessary risk (and hidden costs). Here are a few red flags to look out for:
- Is there someone in the organisation who has overall knowledge of who uses which spreadsheets, why they use them, what they are used for, and how they are backed up? Is this knowledge documented? Unless the spreadsheets and their use are well documented some people might not know they’re there or what they’re for. This can result in people creating their own versions and duplicating effort. If your answer to any of these is ‘No’, you have a problem.
- Do multiple people use the same sheet(s), perhaps passing them around via email or shared network folders? Versioning can mitigate some of the problems, but the flexibility of spreadsheets can be their downfall. The more users the more likelihood of input mistakes or someone deleting or changing something they shouldn’t. Cloud services like Google Docs have made sharing and versioning easier and reduced the chances of losing data, but they don’t have the same flexibility as Excel spreadsheets.
- Are you using spreadsheets for data storage i.e. as a database? Do people enter a lot of data into your spreadsheets? Except for very simple cases this is an indicator of misuse. The tabular format of spreadsheets may not be appropriate for data entry, validation and viewing individual records. It can make the process of getting data into them less efficient than it could be.
- Yes, it may be possible to develop a whole system to run your business and store all of your data using Excel. However, is it the best tool for the job? Flexibility often requires complexity and an expert to build and maintain. Do you have the technical skill in-house? Does everyone understand how the spreadsheet works? This is especially relevant for more complex spreadsheets, which may have been created and developed by an expert who has now moved on. They now contain specialized business knowledge that nobody else understands. Not a good situation.
- Do your spreadsheets contain sensitive information? It can be hard to control access and to your spreadsheets and keep them secure.
Alternatives to spreadsheets
So what are your options when it comes to replacing your spreadsheets?
There could be an existing software package out there that could do the job better than your spreadsheets. Also, cloud-based software applications (software as a service), which are delivered over the Internet, can be relatively cheap to run. They have little or none of the up-front and administration costs that are traditionally related to running software packages on your own network.
Have a system custom-built for your organisation. This may be appropriate for more specialised areas of your business where off-the-shelf products aren’t suitable. It may even involve using spreadsheets. Yes, it may at first seem like a costly option, and it can indeed be expensive. However, when you consider all of the risk, potential problems and the inefficiencies the misuse of spreadsheets introduces, it is often the case that custom-developed software pays for itself in a matter of months. Not to mention other “intangible” benefits well-designed time-saving software can bring, such as improved staff morale or increased customer satisfaction.
What spreadsheets are good for
To reiterate, spreadsheets are an excellent tool when used appropriately. Some examples of uses for spreadsheets that make good sense are:
• Data analysis – e.g. ‘What if’ type queries.
• Reporting on and visualising data – which can be very useful to support decision-making
• Organising and transforming data. Sorting, grouping, filtering. Pivot tables.
• Ad-hoc data exchange. (It’s when this becomes part of a regular process that it can become a problem.)
• Calculations. An obvious one, but spreadsheets offer very powerful functionality in this area, much more than most people will ever use.
Spreadsheets when used appropriately are useful and in many cases essential. However if used incorrectly they can introduce inefficiencies and risks.
As people, we often get so used to our day-to-day work tasks that it can be hard to step back and think about what we’re doing and why we’re doing things. We see just the task, rather than the process.
At the very least rationalise your use of spreadsheets: rethink your processes and combine or discard duplicated efforts. Document who, why and what. Make sure all of your spreadsheets are backed up. Use versioning. And review access and security.
There are many alternatives to spreadsheets though, and we may be able to help you identify, built or integrate suitable tools. If you think your organisation may be relying too heavily on spreadsheets please get in touch. We're happy to do a free initial consultancy and give you a short follow-up report.