Your Excel report does not break because of Excel

Just about every SME runs part of its reporting on Excel. That is nothing to be ashamed of and it is often the right call. Excel is flexible, everyone knows it, and you can build something in it without needing a project first.

What does stand out: those reports almost all break. Not immediately, but after three months, or six, or twelve. And almost never because of Excel itself.

Cause one: several people in the same file

This is the most common one. Two or three people work in the same file, each with their own habits. One inserts a row above the table, another types a date in a different format, a third pastes values over a formula.

Nobody is doing anything wrong in their own eyes. But a file with several authors and no agreements drifts slowly away from where it started.

Cause two: renaming a column

Someone finds "Cust. no." unclear and turns it into "Customer number". Entirely reasonable.

And then everything that leaned on that name breaks. The reference from another sheet, the pivot table, the web of lookup formulas hanging underneath. Sometimes it breaks loudly with an error message, which is the best case. More often it breaks quietly and simply returns different numbers.

Cause three: no unique identifier

This is the fundamental one. In many files there is no column at all that identifies a row unambiguously.

Customers are matched on name. But "Janssens bvba", "Janssens BV" and "janssens" are three different things to a computer and one thing to you. Articles are matched on description, and then a space changes.

Without a unique key you can never reliably join two lists. You will get a result, because Excel always gives a result. The totals just do not add up and nobody knows how much fell away.

Cause four: saving it wrong

Someone saves as a different type. Someone keeps a copy on their desktop and carries on working in that. Someone opens the file in a program that interprets the formatting differently.

From that moment on there are two truths and nobody knows which one is right.

How it went wrong at one client

At one client several people worked in the same report file. Someone added a column to it. Not renamed, not deleted: a column inserted in between because something had to be added.

With that, the entire file was contaminated.

Why an added column is worse than a renamed one is worth explaining. When you rename, whatever refers to that name breaks, and you usually notice straight away because of an error message. When you insert, everything to the right of that column shifts one place. Anything that worked on position instead of on name keeps running happily and pulls the wrong column from that moment on.

That does not produce an error message. That produces numbers. Different numbers than before, but numbers that look plausible.

It was discovered only when someone recognized a total that was not right. How long it had been wrong could no longer be established afterwards, because there was no version history.

That last part is the real lesson. Not that a column was added, because that is normal work and it will keep happening. But that there was no way at all to see when it changed.

Why a new tool does not solve this

This is the point where I often have to push back.

Look at those four causes again. Several people without agreements. Names that change. No unique key. No clarity about which version counts.

None of those four is about Excel. They are all problems of structure and agreement, and they simply move along to the next platform if you do not solve them. A more expensive tool does not make a bad data model better, it only makes it bad faster and on more screens at once.

What to do instead

If you stay in Excel, and you may, there are a few rules that take away most of the misery.

Separate your raw data from your calculation and from your display. Three separate sheets, and in the raw sheet nothing is ever typed by hand.

Make sure every list has a unique key. A customer number, an article number, something that does not change when someone edits a name. If your source system has it, carry it along, even if nobody ever looks at it.

Let the collection happen automatically instead of by copy and paste. Excel can pull straight from a database or an export, and that removes half the errors.

Establish who owns it. One person makes changes, the rest watch.

And know when you have to get out. When several people need to work at the same time, when you need more history than a file can hold, or when the same number sits in four different files, Excel has become your bottleneck instead of your solution.

The claim

If your Excel report breaks every six months, do not try to solve that by building the same report in a more expensive tool. You are just moving your mess to a newer address.