Ten mistakes that make an Excel file unreliable
Most Excel files don't break through one spectacular mistake. They slowly lose their reliability, and usually nobody notices until a result is wrong and no one can explain why.
These are the ten we see most often.
1. Hard-coded values buried in formulas
A VAT rate, a margin, an exchange rate — typed somewhere in the middle of a formula. If the number changes, someone has to go through every formula it appears in. One always gets missed.
A value that can change belongs in its own cell, with a name next to it.
Back to top2. References that break when someone inserts a column
Formulas that point to a fixed column number are correct for exactly as long as nobody changes the layout. The day a column gets added, everything shifts — and no error appears, just a wrong result.
Back to top3. Ranges that don't grow with the data
A formula sums across row 2 through 500. At row 501, the new line no longer counts. The file keeps working; the totals don't add up.
Back to top4. No separation between input, calculation and reporting
Raw data, intermediate calculations and the tidy view all mixed on one sheet. The moment someone adjusts something in the overview, something changes unintentionally in the calculation. This is the most important of the ten, because it makes all the others worse.
Back to top5. Merged cells
They look tidy, and make sorting, filtering and programming considerably harder. There are better ways to get the same look for formatting purposes, without the side effects.
Back to top6. Numbers that are actually text
Happens on import, when the data uses different regional settings from yours: a different decimal separator, or a space as the thousands separator. The cell looks like a number and doesn't count in the sum. A total that comes out too low without anything turning red.
Back to top7. Papering over errors instead of fixing them
Wrapping IFERROR around an entire formula is tempting: the message disappears. But the
error is still there, just no longer visible. Only use it where you know exactly which error you're
catching, and why.
8. Copying files as version control
budget_v3_final.xlsx, budget_v3_final_JAN.xlsx. The moment it matters,
nobody knows which one is correct — and two people have been working in different copies.
9. Links to files on someone's local drive
A reference to C:\Users\... works perfectly on one computer. For everyone else there's
an error message, or worse: an old value that never updates.
10. No input validation and no protection on formula cells
Without input validation, sooner or later text ends up in a date field, or a negative quantity in a stock row. Without protection, someone overwrites a formula with a number — and that formula doesn't come back on its own.
Back to topWhat actually needs to happen
Almost all of these mistakes come from one thing: the file grew, it wasn't designed. It started as a tool for one person and became a process a whole department depends on.
What needs to happen then differs from file to file. Sometimes a repair is enough: take the mistakes out and straighten the structure. Sometimes it makes more sense to rebuild the file as an application, with a clear separation between who enters data, who administers and who develops, with input validation, documentation and a setup the next developer can take over. And sometimes a package is the better choice.
Which route it should be depends on the setup, the risks and the work involved, and so on which of these mistakes you recognise. A file with ten small blemishes is a different case from one where nobody can say any more where a number comes from. We look at your file first; after that you get a proposal with a cost estimate.
Recognised a few of these?
Tell us about your situation. You'll hear what's possible and roughly what it costs.