Probably the most powerful feature of Excel when it comes to managing the data used to run your business is the ability for computer savvy users to put together business specific calculations and functionality using formulas. It’s exceptionally valuable to be able to reduce manual effort, and duplication, as well as adding some structure. The downside of this is that the formula grow and grow as more features are added and you end up with a big blog of business process rules all over your spreadsheet.
This article is the fifth 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.
Each change and addition is another chance for these rules to become inconsistent or for subtle errors to sneak in. They also create an opportunity for staff to accidentally break the fragile formula when using the spreadsheet for their daily work.
Locking the spreadsheet mitigates many of these problems—making the formulas themselves off-limits to anyone who isn’t authorised, and making only specific cells editable by normal users. With a bit of careful management and forward planning, this can be a reasonable approach. It can be difficult to work out ahead of time exactly how much access is needed, and layout decisions made at the beginning can make certain things completely impossible to restrict appropriately. It also removes some of the autonomy and ownership because only certain members of staff are able to make modifications to certain parts of the spreadsheet, potentially causing delays whilst waiting for a change to be made.
Custom systems certainly don’t resolve all of these problems but they can definitely mitigate what I consider to be the most impactful ones:
As discussed in part 2, user access is determined by your business requirements and not the restrictions of the software you use. If you need certain staff to be able to manage a specific piece of business data, they can. In addition to having the ability to manage the data, data entry screens are developed in a way that makes sense to staff and makes tasks more efficient. This reduces training requirements and means that more technical users don’t have to provide support for their colleagues.
A small, but important, change is that staff trust the system to only allow them to perform actions that are valid and there isn’t a way that they can accidentally break things that will affect their work and the work of others in the company.