Have you outgrown spreadsheets? Part 1: Inconsistent data

Have you outgrown spreadsheets? Part 1: Inconsistent data

business / spreadsheets

Spreadsheets are a great way to start adding structure to previously completely manual processes and they allow information sharing between staff. After a while, though, the honeymoon period ends and they start to become a bit of a liability.

This article is the first in a multi-part series explaining how spreadsheets can start to become a liability as your company grows, and how investing in bespoke systems can alleviate these issues, as well as bringing additional benefits.

I have seen a number of problems that usually begin to take hold as the value of spreadsheets is seen and they are shared with multiple colleagues.

Naming and terminology

People have their own way of referring to things and these differences creep into the data, making it unreliable. For example, if you have a spreadsheet to record the status of invoices for all your customers, perhaps one member of staff enters the client as “Acme Inc.” but another enters it as “ACME Incorporated.” Now you have two records that really refer to the same thing and your reports become unreliable.

Making master lists can help alleviate these problems but they require expertise to set up these features and maintaining these lists amongst all the different spreadsheets that require that information.

Software systems designed for your exact means allow integrating separate processes and taking advantage of the common pieces of data to reduce duplication and make keeping data accurate much easier.

Formulas

One of the great things about spreadsheets is that formulas can be easily included to remove the effort of manually calculating results for each unique piece of information. This can become a problem as formulas change but they are not updated in every place, or perhaps someone needs to make a slightly different formula that gets copied somewhere else. Formulas can also become very complicated very quickly, making them prone to error.

The business rules become part of the spreadsheet because the rule has to be added to each spreadsheet and cell that it relates to. It then becomes exceptionally difficult to keep the rules current and consistent because of the opaque nature of formulas and the multitude of places they could be.

With software systems, your business rules are recorded in a single place and can be shared amongst all the functionality that forms the system. Rather than having multiple, separate spreadsheets that each duplicate certain critical rules and pieces of information, a bespoke system can bring all these aspects together to remove inconsistencies and duplication. If a rule needs to change this is done in the central system, automatically updating all relevant areas.

Multiple spreadsheet versions

A common workflow I have seen is for the spreadsheet owner to send copies to other people for them to fill in the data they are responsible for and those people then send back their versions so that the owner can create a master version with all the data.

This can be okay when only a small number of people are involved but it tends to increase all the problems above and also means that there are multiple sources of truth, which aren’t widely accessible.

A central system means there is a single place that all desired employees can access. They have can update their information whenever it is appropriate and everyone else who needs to be able to read that information has access to the most up-to-date information.

Want to see how I can help?

Owner and chief coder

With nearly a decade of professional development experience, I have worked in the educational sector with the University of Cambridge, and with many private sector companies, including 118 118.