The origins of the first mainstream spreadsheet can be traced back to the late 1970s (yes spreadsheets are nearly 50 years old) when a primitive version of what we know of now was available to Apple ll and IBM PC users under the banner of VisiCalc which contained all of the core features we still use today. This version of the spreadsheet was born out of a desire to automate the calculations that were traditionally performed manually on paper-based accounting sheets as well as having the benefit of being stored digitally.
Fast forward to present day and the word ‘spreadsheet’, which initially referred to the spread of data across a large sheet of paper organised in columns and rows, is now interchangeable with Microsoft Excel in much the same way that ‘Google’ is often used in place of ‘Search Engine’ or the brand name ‘Hoover’ is instead of ‘Vacuum Cleaner’ (don’t worry if you’ve just learnt that!)
Why are they used so widely and frequently?
Historically spreadsheets have been a great way of storing, modelling, and manipulating data, displaying it in a user-friendly format and as a result inform decisions. The powerful features within a spreadsheet coupled with the fact that the majority of companies provide a version of Excel as part of their standard desktop computing offering make it very easy as well as, for the most part, intuitive to start using spreadsheets from day 1.
Here are some good uses for spreadsheets in organisations and/or in general:
• Modeling some mathematical concepts
• Storing some short-term, one-off data
• Formatting some data
• Creating some nice looking charts for a presentation
• Creating a list of things to do
• Storing some budgets to keep to
• Managing a plan and costs for a group holiday
• Calculating a savings plan for said holiday
• The ACTUAL holiday spend
• Budget required for the next one
What are the risks?
Most organisations understand that spreadsheets carry some level of inherent risk and there is a certain level of acceptance of this but not everyone understands exactly what those risks are. Here are some of the obvious ones:
• Highly susceptible to human error
• A lot of duplication of data and effort
• Excel has its own programming language which not everyone can decipher
• Often contain stale business logic
• The bigger and more complicated they get, the more volatile they become
• Large use of magic numbers
• Significant lack of documentation
In short, it means they are likely in breach of regulations especially if they are in the financial sector and certainly with GDPR rules if they contain sensitive personal data.
How to reduce the risk within spreadsheets?
Most organisations have teams dedicated to managing and reducing risk but often avoid spreadsheets or user tools altogether. The reasons for this aren’t clear but it’s probably due to the difficulty of deciphering these spreadsheets and the fact that there are so many of them spread across teams. However, there are some logical and quite simple ways organisations can reduce risk or at least begin to.
- Here are a few:
- Firstly ACTUALLY identify which ones are important and risky
- • Put them in a secure location with relevant team permissions
- • Create a change management process around the riskiest ones
- • Figure out what they are doing! Inputs, outputs and everything in between.
- • Mitigate fat finger syndrome by protecting cells
- But there are many, many more…
Where to begin?
Just because spreadsheets are part of many processes doesn’t mean nothing can be done to remove the super risky ones.
For example, if you had the symptoms of a cold would you carry on going out without that extra layer of clothing on, or have that Gin & Tonic after work? Well, maybe the G&T but you’d probably limit it to one and then go home, make a warm drink and have some paracetamol. The point is, just because there are some symptoms or risks it doesn’t mean they can’t be managed, reduced and the causes eliminated in the long term.
As well as most computers having a native way to search for and group different file types there is software available to assist with the searching as well as the categorisation of the files. Such software runs scans on drives and for spreadsheets can also determine:
- How many formulae are present
- The amount of embedded VBA code
- The amount of formatting used in sheets
- Any external data sources it has and so on.
The policy should at the very least contain things like guidelines on the storage and creation of these spreadsheets, standards for design, specifics on the best way to handle data inputs as well as pointers for creating scalable solutions if a spreadsheet is the right place for it.
Another way of reducing risk is creating a policy for people to adhere to. Again using the virus analogy, you want some rules like:
- Keep wrapped up warm during cold weather
- Exercise at least 3 times a week
- Take multivitamins daily
- Avoid people with colds etc
The policy should at the very least contain things like guidelines on the storage and creation of these spreadsheets, standards for design, specifics on the best way to handle data inputs as well as pointers for creating scalable solutions if a spreadsheet is the right place for it.
Summary
Spreadsheets are a necessary evil. They serve a purpose in allowing people, en-masse, to manipulate, model and display data but can grow into unmanageable, risky applications storing sensitive and critical data that could lead to data and monetary loss.
Most spreadsheets are used for the right purposes i.e. to perform data modelling work, to temporarily store some one-off data, or planning that holiday. However, they end up being core to a lot of businesses day to day operations and become misused over time. Even though they are critical to the day to day running of many organisations and people know they are not the right tool for the job they continue to use them anyway because there is a perception that there is just no other way to perform that task.
Fortunately, there are a number of tools and techniques emerging that can help elevate this problem and a growing number of companies are deciding to approach this problem head on. And here is when an Einstein quote comes to mind:
“The definition of insanity is doing the same thing over and over again, but expecting different results”
For more information on how, as a company, you can improve the impact spreadsheets have on your business head to our EUC Page.
