When to replace an Excel file with an internal tool.
Replace an Excel file with an internal tool when several people change it every day, the business stops without it, and its rules exist only as formulas that one person understands. If one person owns the file and uses it to calculate or analyse, keep Excel. In between, a shared workbook or a standard product is often enough.
Signs that a spreadsheet has become a risk
A spreadsheet becomes a risk when the business depends on it and nobody controls what happens to it.
Errors are part of that. In a review first presented in 2000, Raymond Panko of the University of Hawaii collected seven field audits of 367 spreadsheets used in organisations. They found errors in 24% of them. In the audits from 1997 onwards, which Panko describes as more thorough, it was 91% of 54 spreadsheets, and most audits counted only serious errors.
Five signs show that a file has reached that point.
- Several people enter data, and copies travel by email
- One person understands the formulas, and nobody else dares to change them
- Nobody can say who changed a figure, or when
- The file holds personal data about customers or employees
- Work stops if the file is lost or damaged
When Excel should stay
Excel is the right tool when one person owns the file and uses it to calculate, plan or analyse: a budget, a price calculation, a one-off evaluation. Do not replace a file like that. A custom tool for it costs more than the problem is worth.
The options in between
There are three steps before custom software. Take the smallest one that removes the risk.
A shared workbook in Microsoft 365. If the file is stored on OneDrive or in SharePoint Online, several people can edit it at the same time. Version history lets you open and restore earlier versions, and Excel for Microsoft 365 shows who changed what and when for the past 365 days. That ends the copies by email. Two limits remain. When two people change the same cell, the change saved last generally wins. And Microsoft states that worksheet protection is not intended as a security feature: it only prevents changes to locked cells.
A standard product. If the file does a job that many companies have, such as customer management, time tracking or stock, look for a finished product first. You adapt your process to it and do not have to own software.
A no-code tool. Here someone on your team puts together tables, forms and simple workflows without programming. Use one when the process is simple and one person in the company looks after it.
A custom internal tool. This is a web application built for your process. It pays off when the process is specific to your company, no product fits and a mistake costs money.
What a custom tool adds
I build web applications and internal tools with accounts, roles and payments. A custom tool adds four things the other options lack.
- Accounts and roles: every person signs in and sees and changes only what their role allows
- A record of changes: who changed which value and when, kept as long as you decide
- Rules that cannot be bypassed: the software enforces a required field, a price limit or an approval for everyone
- Connections to other systems: data reaches accounting or the online shop without retyping
Once personal data is in the file
The Swiss Federal Act on Data Protection defines personal data as any information relating to an identified or identifiable natural person. A list with the names of employees or private customers contains such data. Article 8 requires data security appropriate to the risk, through suitable technical and organisational measures. Article 12 requires a record of processing activities, with exceptions for companies that have fewer than 250 employees and whose processing poses a negligible risk. Under Article 24, a breach of data security that is likely to lead to a high risk for the person concerned must be reported to the Federal Data Protection and Information Commissioner as quickly as possible. Accidental loss and access by unauthorised people both count as a breach.
These measures are hard to show for a file that anyone can copy and forward.
Moving without stopping the business
Do not switch on a single day. The file keeps running until the new tool has shown that it works. I build in short phases, with working software throughout.
- Write down what the file does today: who enters what, which formulas matter, which reports it produces
- Start with the part that causes the most trouble, for example data entry by several people
- Import the existing data and compare the totals in the tool with those in the file
- Run both side by side for a fixed period, then lock the file and archive it
What you can use
- List your spreadsheets and mark the ones the business cannot work without
- For each, note who edits it, who understands it and whether it holds personal data
- Store shared files on OneDrive or SharePoint, so that version history works
- Look for a standard product before you have anything built
Questions
What does a custom internal tool cost?
You get a fixed price within 48 hours of a free 30-minute call, with approach, timeline and milestones.
Can we still analyse the data in Excel afterwards?
Yes, if the tool is built for it. I plan an export for every list from the start. The data and the rules are then kept in the tool, and Excel works with a copy.
Who looks after the tool once it is finished?
After launch I run it for you or hand it over to your team, with complete documentation. 30 days of support after launch are included.
How do I find out which option fits us?
My assessment tells you what your team can set up itself, what needs custom software, and what each option covers and costs.