Almost every company has a file that nobody ever purchased and without which nothing works. It came into being years ago because a question was open that no existing system answered: which orders are still outstanding? Which machine is booked when? What did the last job really cost? Someone opened a spreadsheet, created three columns and solved the problem in an afternoon. Today the same file has forty columns, several sheets, nested formulas and a file name with a version suffix. It has become a core system without anyone ever deciding that it should be one. This article describes why such files appear, what they genuinely do well, at which five points they become dangerous in day-to-day operations and how to tell whether replacing one pays off. It also covers the opposite case, because not every spreadsheet should be replaced: for drafts, one-off calculations and single reports it remains the right tool. At the end there is a transition path that avoids standstill, because the old and the new version run side by side for a while. Anyone taking that path starts not with selecting a system but with a sober record of the workflow. The entry point for that is a process analysis.
Key takeaways
- A grown spreadsheet becomes a risk as soon as more than one person has to work with it: once two people need access at the same time, copies appear, and from the first copy onwards it is no longer clear which version applies.
- The five weak points repeat across companies: no concurrent editing, no change history, no graded permissions, formula errors that go unnoticed, and knowledge that sits with a single person.
- Not every spreadsheet should be replaced: for a handful of cases per year, one-off reports and drafts it remains the more economical tool. The line runs where other workflows draw their figures from that file.
- Before any system is selected, the rules currently buried in formulas have to be written out in plain language. Without that list, replacement turns into rebuilding a file whose logic nobody fully understands any more.
- The transition runs in stages with a parallel run and an abort criterion agreed in advance: only when both routes produce the same results over several weeks is the spreadsheet archived read-only rather than deleted.
How a helper file becomes a shadow core system
At the beginning there is rarely a project, but an open question. The inventory system knows stock levels but not the order of installation appointments. The accounting system knows invoices but not the hours booked to a job. So someone creates a spreadsheet, because that takes an afternoon and needs no approval, no budget and no rollout. The solution works, so a column is added, then a sheet for next year, then a formula that derives a third column from two others. After two years the file holds prices, dates, responsibilities and interim states that exist nowhere else.
The shift to core system does not happen on a single day and is documented nowhere. You notice it when operations stall because the file is unavailable: because a colleague is on holiday and still has it open, because the drive is unreachable, or because nobody knows whether the version on the drive or the one in the email attachment is current. A system whose failure holds up the business is a core system, regardless of whether it was ever procured as one.
This development is not a sign of carelessness. Lack of time and lack of staff are among the most frequently named obstacles to digitisation in mid-size companies (Bitkom), and the vast majority of companies in Germany have fewer than 250 employees (Statistisches Bundesamt). At that size there is no dedicated department collecting requirements and turning them into a system. The spreadsheet is the obvious response to a gap that nobody else closes.
How to spot a shadow core system
What a spreadsheet does well, and why that is no accident
Anyone discussing replacement should know the strengths, or they will remove them by mistake. A spreadsheet has no rollout time: whoever opens it can start working. It combines capture, calculation and presentation in one tool, with no switching between screens. It is freely shapeable, because a new column takes seconds and asks nobody for permission. And it is open in the sense that every intermediate step stays visible: you see how a figure came about instead of taking it from a report.
These very properties explain why bans do not work. If the file is prohibited without answering the underlying question in another way, a new one appears within weeks, this time in a private location and without a backup. A grown spreadsheet is a symptom, not misconduct. It shows which requirement the existing system does not cover, and is therefore the most precise requirements list available for the replacement.
Settle the question first, then the file
Five weak points that get expensive in practice
The weaknesses of a grown spreadsheet are not random faults of individual files but properties of the tool. It was designed for the calculation work of one person, not for a workflow with several participants, evidence obligations and cover arrangements. The following five points appear in recordings across trades, manufacturing, wholesale and administration (project experience).
- No concurrent editing: as soon as two people have to work at the same time, a copy appears, whether as a second file, a printout or an email attachment. From the first copy onwards there is no single valid version, and merging is done by hand.
- No history: a changed cell looks exactly like a cell that was never touched. Who corrected which price, date or quantity and when cannot be reconstructed afterwards. When customers, auditors or your own management ask, the evidence is missing.
- No graded permissions: whoever may open the file sees everything and can change everything, including fields that are none of their business, such as purchase prices, margins or personal data. Sheet protection with a password does not replace permission management, because the password gets passed around.
- Formula errors go unnoticed: a shifted sum range, a row inserted outside the reference or a formula overwritten by hand change the result without any warning. The file keeps calculating and delivers a figure that looks plausible.
- Knowledge sits with one person: as a rule there is exactly one person who fully understands the structure. If they are unavailable, move department or leave, what remains is a file that can be used but no longer developed. That is the most expensive of the five risks.
The five points reinforce each other. Missing permissions lead to unintended changes, the missing history prevents them from being found, and the lack of concurrent editing means a second version appears in which the error does not occur. The result is two figures for the same matter, and the discussion about which one is right costs more time than the original data entry.
This becomes most visible in job costing. Hours, materials and subcontracted services from several sources come together there, and the spreadsheet is the only place where they are combined. If the result differs from expectations, it is initially unclear whether the job went badly or the file is calculating incorrectly. That uncertainty is the real damage, because it delays decisions.
Formula errors: the mistake nobody notices
Software is tested before it goes live. A grown spreadsheet is not, because it was never regarded as software. There is no test environment, no second version for comparison and no point at which someone checks whether the result still holds. When an error does surface, it usually comes from outside: a customer disputes an invoice line, the tax adviser reports a difference, or a report does not match the bank statements.
Sum range ends one row before the last line item
Row inserted: the reference still points at the old position
Formula overwritten by hand - the cell looks identical
Text stored instead of a number: the value is quietly skipped
Rounding per row instead of once at the end: gap to accounting
Link to a second file that someone has moved
Filter left active: the report shows only part of the data
# Cross-check: total across rows against total across columns
# Sample: recalculate five cases by hand every quarter
# Neither replaces a validation rule, but both catch the crude casesWhile the spreadsheet is still in use, two simple measures help. First a cross-check that reaches the same result by a second route, for example the total across all rows against the total across all columns, or against a figure from accounting. Second a sample: once a quarter recalculate five cases by hand and record the outcome. Neither is quality assurance in the strict sense, but both catch the error classes with the greatest everyday impact.
For the replacement decision, the effect of an error matters more than its frequency. A wrong figure in an internal scheduling list costs a phone call. A wrong figure in job costing distorts pricing for months without anyone looking for the cause. Wherever the file feeds into quotations, invoices or reports, it belongs at the front of the queue.
Replace or deliberately keep: the decision in three questions
Replacement is not an end in itself. It costs money, attention and adjustment, and all three are scarce in mid-size companies. So a short and honest check is worth it before any system is discussed. Three questions carry the decision: do several people work with the same file? Do other workflows depend on its figures? And what happens if the person who understands the structure is unavailable? Anyone answering all three with yes or unclear does not have a tooling problem but an operational risk.
| Question | Argues for keeping | Argues for replacing |
|---|---|---|
| Who works with it? | One person, occasionally | Several people, daily |
| How often does the case occur? | A few cases per year | Several times a week or daily |
| Where do the figures go? | Only into personal preparation | Into quotations, invoices, reports |
| Is evidence required? | No evidence needed | Traceability is expected |
| How stable is the logic? | Changes with every case | Same rule over a long period |
| What does an outage cost? | A delay of hours | Operations stop or figures are wrong |
This overview gives a direction rather than a score. In practice the decision rarely applies to a whole file but to individual sheets: the order list is replaced because it affects four people, while the quotation costing sheet stays because one person uses it for individual cases. A partial replacement is not a compromise but often the more economical route.
Four target pictures for the replacement
Once it is settled that a file will be replaced, the question is where to. The most common mistake at this point is jumping to system selection. It makes more sense to determine the target picture first, because that decides whether any purchase is needed at all. Four target pictures cover most cases in mid-size companies (project experience).
Use a function in the existing system
Often the inventory or order system can do exactly what the spreadsheet was built for, only under a different name or in an area nobody has set up. This check comes before any purchase, because it is the cheapest option.
Capture through a form and a database
Where data is created in a structured way and several people need access, a lean form with a database behind it replaces the file. Mandatory fields, selection lists and validation rules prevent part of the errors at entry.
A reporting layer on existing data
If the file only calculates and presents while the data already sits in other systems, no new data entry is needed but a report that fetches the values itself. That removes the monthly gathering exercise.
An interface instead of a collection file
If the file serves as a transfer point between two systems, replacing it means handing data directly between those systems. The workflow gets shorter because one station disappears entirely rather than being digitised.
The four target pictures are not mutually exclusive. An order list can be kept in the existing system in future, while the reporting on top of it becomes a view of its own and the handover to accounting runs through a data integration. If the underlying system can no longer provide the required function at all, the spreadsheet belongs in the wider question of replacing legacy systems — it is then not replaced on its own but dissolved as part of the changeover.
A transition path without standstill
A replacement must not halt day-to-day operations. That works when the new solution first runs alongside the spreadsheet and only takes over once both routes deliver the same results. The following sequence has proven itself in companies without their own IT department (project experience); depending on scope, the stages take between a few days and several weeks.
Stage 1: record and freeze
The current version is backed up and fixed as the starting point. At the same time it is agreed that nothing fundamental about the structure will change before the switchover. Content keeps being maintained, the structure stays put, so the rebuild is not chasing a moving target.
Stage 2: write out the rules
Every formula and every convention is described in one plain sentence: what is calculated, under which condition, with which exceptions? This is where the special cases hidden in footnotes and cell comments come to light. That list is the real outcome of the preparation.
Stage 3: build the target with real data
The new solution is not built with sample data but with an extract from the real stock. Only then do inconsistent spellings, empty mandatory fields and special characters show up that would otherwise cause work during the migration.
Stage 4: parallel run on real cases
For a defined period the same cases are handled on both routes and the results compared. Deviations are not clicked away but explained: either the old formula was wrong or the new rule is incomplete. Both findings are a gain.
Stage 5: cut-off date and switchover
The switch happens on an announced date, ideally at the turn of a month or quarter so that reports separate cleanly. Before that date it is settled who is the contact for questions in the first month and what a fallback to the spreadsheet would look like.
Stage 6: decommission rather than delete
After the switch the file is archived read-only in a named location, with a date and a pointer to its successor. It stays readable as evidence for the past but is no longer maintained. Two live versions are the surest way back into the old muddle.
The current version is backed up and fixed as the starting point. At the same time it is agreed that nothing fundamental about the structure will change before the switchover. Content keeps being maintained, the structure stays put, so the rebuild is not chasing a moving target.
Every formula and every convention is described in one plain sentence: what is calculated, under which condition, with which exceptions? This is where the special cases hidden in footnotes and cell comments come to light. That list is the real outcome of the preparation.
The new solution is not built with sample data but with an extract from the real stock. Only then do inconsistent spellings, empty mandatory fields and special characters show up that would otherwise cause work during the migration.
For a defined period the same cases are handled on both routes and the results compared. Deviations are not clicked away but explained: either the old formula was wrong or the new rule is incomplete. Both findings are a gain.
The switch happens on an announced date, ideally at the turn of a month or quarter so that reports separate cleanly. Before that date it is settled who is the contact for questions in the first month and what a fallback to the spreadsheet would look like.
After the switch the file is archived read-only in a named location, with a date and a pointer to its successor. It stays readable as evidence for the past but is no longer maintained. Two live versions are the surest way back into the old muddle.
The parallel run deserves a firm agreement: period, comparison points and an abort criterion. A sensible span covers at least one full month-end close, because that is where the rarer cases appear. A workable abort criterion reads like this: if more than one unexplained deviation per week remains after four weeks, the switchover is postponed and the cause investigated. Without that criterion the parallel run gets extended out of habit, and the double effort stays with the team.
A transition is complete when nobody opens the old file any more to verify a figure. As long as that keeps happening, the new solution is not finished, merely additionally present.
Evidence, permissions and data protection
Grown spreadsheets often hold more than business figures. Staffing plans contain absences, commission overviews contain performance data, customer lists contain contacts with phone numbers. As soon as personal data sits in a file that anyone on the network can open, access is effectively unrestricted, and there is no way to report who saw which data and when. The German federal agency for information security recommends traceable access rights and a regulated approach to backups in its baseline protection guidance (BSI) — both are hard to implement with a file on a shared drive.
On top of that come evidence obligations. Where figures feed into invoices, annual accounts or grant reporting, it must remain traceable how they came about. A file without a change history cannot deliver that. If a system also records the behaviour or performance of employees, works council codetermination applies. This overview does not replace legal advice: whether and to what extent obligations apply in a specific case should be checked professionally before the switchover.
What the spreadsheet is allowed to keep
A replacement that forbids everything creates resistance and, in the end, new shadow files. It makes more sense to give explicit permission for the cases in which a spreadsheet remains the right tool. That permission should be written down so it does not depend on the mood of the day.
- One-off calculations and drafts: an investment appraisal, a price comparison, a rough costing. The case does not repeat in the same form, so building a workflow does not pay.
- Preparation and modelling: anyone developing a rule tries it out in a spreadsheet first. Once the rule holds, it belongs in the workflow, not in the file.
- A report for a single purpose: a special evaluation for one meeting that is not needed afterwards. If it repeats every month, it is a case for metrics and reporting.
- Handover to third parties: an export for the tax adviser or an audit that comes out of a system rather than being maintained by hand. The direction is what matters: out of the system, not into the file.
One simple rule separates the two worlds: a spreadsheet may be a result, but not a source. As soon as another workflow draws its figures from a manually maintained file, that file is on its way to becoming a core system again. The rule is explained in a few words and lasts longer in daily practice than a detailed policy.
What prevents the next shadow spreadsheet
After the switchover it becomes clear whether the company holds the ground it gained. Shadow files appear where a new requirement turns up and the official route is too long. If an extra column in the system needs a quarter of lead time while the spreadsheet needs five minutes, the spreadsheet wins. The most effective protection is therefore organisational: a named contact for small change requests and a commitment on how quickly they are decided.
Equally important is that the new workflow is described. A short piece of process documentation with one page per workflow records where which data is created, who maintains it and what to do when something goes wrong. It replaces the knowledge that previously sat with one person. Alongside it belongs induction on a real case rather than on a manual; training and rollout decides success more often than the technology does.
Finally, a regular look back helps. A short round once a year asking which files are being used for recurring workflows again costs little and finds the new candidates early. Ask the question before the file has forty columns and you still have a choice. After that, a project is due.
Related Articles
Choosing Business Software: Requirements Come First
How to decide before you buy: measure the volume baseline, write a lean requirements document in a week, score vendors by weight and check the contract terms.
Bringing staff along with new workflows: a rollout plan
Involvement before the decision, key people, training on real cases and a supported transition: how a new workflow actually gets adopted in a business.
Text recognition in practice: what it reads, what it guesses
Text recognition realistically assessed: clean sources versus carbon copies, stamps and handwriting, measuring quality, fields to extract, effort per type.