I was once told by a database engineer that every spreadsheet represented a failure by the organisation to provide appropriate tools. I’m far too much of a fence-sitter to give my unqualified agreement to that statement, but I definitely think he had a point worth considering – particularly for organisations hoping to reduce risk and eliminate waste.
Spreadsheets can hold and organise data, perform calculations, and provide insights through totals, filtered views, and attractive charts. As a tool, they’re readily available and easy to start using. And when I’m helping an organisation improve its business operations, discovering a spreadsheet will often lead me to identifying a number of opportunities! Spreadsheets are used when better technology isn’t available, and as they become indispensable to the operation, they also bring both waste and business risks.
In this article, I’ll look at three ways spreadsheets are used, examine why they’re a poor solution, and explore how organisations can seek out better options.
What’s wrong with using a spreadsheet?
1 – Using spreadsheets as data repositories
In my work in the legal industry, I frequently see spreadsheets used for holding records – of transactions, bills, cases, or people. The spreadsheet becomes a primary repository, and expands organically over time to incorporate countless new columns (that all seemed essential when they were added).
Using spreadsheets as data repositories creates information security risks, as it’s hard to ensure confidentiality, availability, and integrity.
Spreadsheets are frequently held on network drives, in file shares, or even (heaven forbid!) locally on laptops! While most organisations will have great controls in place to manage permissions and access to data on key technology platforms, it’s much harder to control who has access to spreadsheets in obscure locations. Teams often struggle with shared access (with the common cry of “is someone in the spreadsheet..?”), creating waste through avoidable delays.
It’s hard to ensure data is captured correctly in spreadsheets. Typically, data is keyed in manually (always a risk!). Often, users fail to complete all the required columns or tabs. While data validation rules are sometimes applied, such as enforcing selection of values from a list, frequently spreadsheets are used that allow any type of data to be entered. I’ve seen spreadsheets that expected to calculate a total from a column that had text comments scattered throughout!
Often, these spreadsheets become a parallel repository alongside another tool. I’ve seen spreadsheets dutifully maintained by teams alongside records in the core case management system. Frequently this is because key data attributes needed by this time (or with this demanding client) aren’t in the main system. Endless re-keying of data is wasted effort, and inevitably the data gets out of sync causing confusion and errors.
Spreadsheets used as data repositories often represent a huge business continuity risk. Version control rarely exists. It’s easy to delete or change data inadvertently – and without any record of doing so! If a spreadsheet is stored somewhere obscure, it’s unlikely to be included in backup snapshots.
In addition, holding data in its own silo, disconnected from other systems and processes, prevents joined-up thinking and insights that could unlock future value for the organisation and its customers..
2 – Using a spreadsheet as a processing tools
Many spreadsheets come into being as a workaround when a new process is introduced or an existing process is changed. At the time, they often feel like a minor miracle – someone creates a clever tool that will perform a calculation, apply a category, or can be used to run mail merges. I know that in the past, I have been that “talented amateur” that came up with a smart formula or knew about conditional formatting!
Over time, these solutions start to feel rather fragile as further tweaks are applied to respond to new demands or rules, or the original creator moves on and the team becomes reliant on half-remembered “folklore”. They become increasingly difficult to maintain, and the team dreads the day when the spreadsheet finally falls over.
As with using a spreadsheet as a data repository, the need to enter data manually is both risky and wasteful.
3 – Using spreadsheets to control risks
Spreadsheets are frequently used as a form of detective control to manage risks and oversee operational performance.
Spreadsheet applications such as Excel offer some great tools for exploring and aggregating data. You can apply filtered views, pivot tables, and a huge range of charts to look for insights and issues. This information is rarely “real time” though, and it can be really challenging to switch between summary views and particular cases or items.
I’ve seen many weekly or monthly reports that are essentially data dumps, scrutinised by a team leader or manager line-by-line to check for anomalies and outliers. While there may not be a better option with available tools and systems, this is a hugely wasteful use of talent – and is often too late to do much more than damage limitation!
Moving away from reliance on spreadsheets
Of course, freeing your organisation from the limitations and risks of spreadsheet usage is not always easy (or cheap!).
Digital transformation or standalone solutions?
Many organisations conduct digital transformation programmes to streamline and future-proof processes. These initiatives are a golden opportunity to address reliance on spreadsheets, so ensuring these are identified and included in scope is crucial.
Attempting to eliminate a particular spreadsheet as a standalone initiative can be difficult, as purchasing or building a replacement tool can be expensive and time-consuming.
Assembling a robust business cases can be a challenge! Taking time to thoroughly explore and document the organisation’s exposure to risks and waste enables you to build a strong business case for better solutions, and ensures decisions around trade-offs in functionality and cost of solutions are better informed.
Things to think about when choosing solutions
If the information in a spreadsheet is derived from or overlaps with an IT system, it’s worth exploring whether that system could be adapted or expanded to cover the uses currently addressed by the spreadsheet. This is often cheaper and easier than finding or building completely new solutions. If the system is an in-house development, ask to get the work on the system roadmap. For off-the-shelf products, you may be able to obtain support or development work from the supplier.
When introducing new systems, it’s essential that existing reports aren’t given a “free pass” to replication in the new world. Instead of creating a report, think about whether other controls would be more useful. Preventative controls built into systems and upstream process can eliminate the risks you’re trying to identify with a report. Alternatively, automated alerts and notifications can act as a detective control, bringing exceptions and outliers to your attention in a timely manner. Dashboards are another great tool for keeping an eye on key metrics and trends.
When exploring a potential system purchase or build, ensure requirements definition work includes support for preventative and detective controls and for obtaining timely insights from data.
Be careful not to rely too heavily on spreadsheets themselves when establishing data requirements; spreadsheets are often created by people who aren’t experts in defining entity relationships or structuring data attributes. You want to avoid baking in poor structures, data that can be derived from other data, or obsolete attributes. Stakeholder’s own articulation of their needs is your best source of requirements, and the spreadsheet can be used to verify, challenge, or complete your understanding where needed.
Can we still use a spreadsheet at all?
I think I’ve come to the conclusion that using spreadsheets for one-off tasks or projects is fine – but if you do create one, be clear about its limited purpose, and think about when it can be retired. If you find yourself building a spreadsheet that you think will be integral to an ongoing process or activities, do consider if there might be better, more sustainable, options.