The revelation that nearly 16,000 Covid-19 cases in England had gone unreported brought the use of spreadsheets as enterprise data management tools back into the spotlight.

While many who work in finance, analytics and other data-centric disciplines will know spreadsheets aren’t the best tool for many corporate data storage and manipulation tasks, their use remains widespread. This is partly down to spreadsheets’ ubiquity: you’ll find Excel installed on virtually every corporate computer in the world, while online offerings such as Google Sheets are also easily available. Another factor is the spreadsheet’s ease of use: simple calculations are straightforward to implement with minimal training. More complex tasks can be achieved using macros, where the skill threshold is higher, but far from insurmountable for those adept at working with complex data.

And while spreadsheets are the ideal tool in certain situations, it’s important to recognise when it’s time to graduate onto something more robust and capable.

So what are some of the signs to look out for? And what should you be thinking about as you start to craft your spreadsheet replacement?

Sign you’ve reached the limits of spreadsheets

The first indicator is where spreadsheets have become the source of truth for important business data. This carries risks around data security, scalability, integrity, versioning, lineage and access.

The second is where spreadsheets are used to implement and routinely perform business-critical calculations. This typically happens when individuals need to bring together data from disparate source systems around the organisation. Combining and manipulating the data needs to happen somewhere, and the spreadsheet tends to be the default location. This can introduce challenges around performance, versioning, automation, reproducibility and maintainability.

A third indication is when it becomes common practice for these spreadsheets to be emailed between individuals or teams, as part of business-as-usual processes. Leaving aside the security risks of sending potentially sensitive data via email, this process results in multiple copies of the data, with all the dangers that entails.

A fourth indicator is questions about where data in a spreadsheet has come from becoming difficult or time-consuming to answer. If this lineage isn’t clear, it can lead to all kinds of compliance and trust challenges.

You’re outgrowing spreadsheets – what next?

Once you realise a task or process has outgrown the capabilities of the humble spreadsheet, where do you go next?

Different situations will demand very different solutions, so the key is to consider what the data is ultimately needed for, both today and in the future.

Are you seeking to feed real-time insights to front-line decision-makers? Do you need to gather strategic reports for a monthly board meeting? Are you periodically required to produce detailed, granular data for your regulator? Might you need to go back and recreate point-in-time data? Does the data need to go through a carefully controlled and auditable workflow, involving multiple steps or teams?

Once you’ve understood the need, there are other important considerations before you begin to implement a technical solution.

Data governance

The first is to ensure you have appropriate data governance and lines of accountability in place. Whole books have been dedicated to this topic, so we won’t go into detail here, except to stress how important this aspect is to the success of any technology solution.

Scalability

Next is the potential for growth. Think beyond today’s needs: what happens as your data volumes and usage expand as a result of increased customer numbers, new product lines, or mergers and acquisitions? Can the data platform and surrounding processes scale sufficiently?

Security

Data needs to be protected. A good data platform will provide a variety of built-in safeguards, and therefore by default represent an upgrade over spreadsheets. But its design and configuration will still require careful attention to detail, to align with your use case.

Is the data appropriately encrypted? How will access be controlled, and how granular does this need to be? Must certain data be ringfenced? How is data shared, and what safeguards are required around this process?

Data lineage

A further consideration is to ensure the solution supports data lineage requirements: where has a particular piece of information come from? The ability to quickly and confidently answer questions of this nature is essential for a number of reasons. It will build important trust in the data. It will also enable anyone working with or auditing the data to identify precisely what they’re looking at, to ensure it’s correct for their needs.

As important as the notion of where data has come from, is where it has gone to. It’s common to find customer data stored across a wide range of systems within an organisation. A good data solution will need to help keep track of this proliferation, both to enable the data to be used effectively for business purposes, and to meet requests made by individuals under the GDPR and other regulations.

Removing risk and unlocking opportunity

Spreadsheets will have their place in every organisation, but as soon as they start getting used for anything more than lightweight data curation and manipulation work, it’s time to move on to a more robust, fit-for-purpose data solution.

By providing enterprise-grade levels of robustness, security, auditability, process control and repeatability, you’ll be removing potentially enormous amounts of risk and cost from the organisation. You’ll also be empowering your decision-makers – from the strategic to the front-line – to make better-informed choices, ease the burden of compliance and unlock exciting new opportunities for innovative data-driven products and services.