Someone added a column and everything moved
The report worked for months. Then the CRM admin added a new field, or the supplier's export gained a column called Region, or someone renamed Qty to Quantity. Now the lookups return the wrong values, the pivot table has blank rows and the macro stops halfway with an error nobody understands.
The person who fixes it knows the drill: find which column shifted, update the references, run it again. Next month it happens with a different column.
Worse is the month it does not error at all. The numbers simply come out wrong, and nobody notices for a while.
Why the report is so fragile
Most fragile reports were built quickly from an export and grew from there. They depend on things nobody promised would stay the same.
| Hidden dependency | What breaks it |
|---|---|
| Column position (column F is price) | Any new or removed column earlier in the file |
| Exact header text | A rename, a trailing space, a new capital letter |
| Row order | A different sort in the export |
| Hard-coded ranges | More rows than last month |
| A manual export with custom settings | Someone else running the export |
None of these are unusual mistakes. They are the natural result of building on a CSV export, which is a presentation of the data rather than an agreement about its structure.
Macros make it worse. A VBA routine that selects column G and copies it to another sheet has no idea what column G contains. It will happily copy a customer name into a price field if the layout shifts, and the output still looks like a finished report.
The cost of a report that breaks
- Time spent fixing the same kind of break over and over
- Reports delivered late while they are repaired
- Silent errors when a column shifts but the formulas still find a number
- Fear of changing source systems in case it breaks reporting
The last cost is easy to miss. When the business starts avoiding useful changes to its CRM or ERP because the reports might break, the reports are running the business instead of the other way round.
How we make the report hold up
- Where the source has an API, we read fields by their internal name or ID, which rarely changes, rather than by column position in an export.
- Where only files are available, we read columns by header with a mapping table that allows known aliases, such as Qty and Quantity.
- Before any processing, a schema check compares the incoming structure with what is expected. New, missing or renamed fields are detected up front.
- If a required field is missing, the run stops and a named person gets a clear message saying which field and which source. Nothing half-processed is published.
- New optional fields are logged and ignored until someone decides to use them, so additions never break anything.
- The transformation logic lives in version-controlled code or SQL with tests, not in a chain of cell references.
Where your report lives in Power BI, Excel or Looker Studio, we usually keep it there and fix the layer feeding it.
Change the source system without holding your breath
When the CRM admin adds a field, nothing happens to the report. When someone renames one, the check either maps it automatically or sends a message that says exactly what changed. The fix is one line in a mapping table rather than an afternoon tracing formulas.
Teams become willing to improve their source systems again, because reporting no longer punishes them for it.
The person who used to repair the report every month gets that time back, and the knowledge of how it works is in documented code that someone else can pick up, rather than in one head.
Recognise this pattern?
- The same report breaks after routine changes to a source system
- Fixing it means finding which column moved
- You have found wrong numbers that ran for weeks unnoticed
- People are told not to touch certain fields in the CRM
- Only one person can repair the report