Migrating your Excel data: this is where it always goes wrong
Importing the sheet is an afternoon of work. Getting the data in it straight is not. What you run into, and what you can tidy up right now.
On almost every project that replaces a spreadsheet, the build is predictable and the data migration is not. Not because importing is hard, it is not, but because a sheet allows things a database refuses. Below is what I keep running into, in order of how often.
One column holding three facts
"Jan de Vries - 06-12345678 - paid 12/3". A human reads that fine. To a system it is one text field you cannot filter, sort or calculate on. Moving it over means pulling it apart, and that only works if everyone filled it in the same way. They never did.
The same customer, four times
Bakery de Jong, Bakery De Jong, Bakery de Jong Ltd and Bakery de Jong (old). A sheet has no idea these are one company, so your revenue per customer sits spread across four rows. Deduplication is partly automatic, but the last few per cent are a human decision: only you know whether that branch in Almere is the same customer or a different one.
Dates in four notations
12-03-2026, 3/12/26, 12 March, and a cell that looks like a date but is text. That last category is the nastiest, because Excel does not flag it. As soon as two notations are mixed, nobody can tell whether 03-04 falls in March or in April. This is why migrations run long: not the import, but the question of which date was meant.
Colour as status
Red is urgent, yellow is waiting on the client, green is done. Everyone in the office knows it, it is written down nowhere, and on export it disappears completely. If you do one thing today: turn your colour coding into a real column with a word in it. It helps you now, and it saves a day of guesswork later.
What happens below the table
Underneath the rows there is usually all sorts: a total line, a note, three test rows from last year and a block someone pasted in once. On import that comes along as customer number 4,312 with an empty name. Merged cells do the same: they look tidy and produce empty rows on export.
A sheet forgives everything. That is its strength and exactly why the migration is the hardest part.
How I handle it
- The data model first: what is a customer, what is an order, what hangs under what. Only then does anything get imported.
- The import runs several times on a copy, not once on the real system. Every round produces a list of what could not be placed automatically.
- That list goes to you, not to me. I can see that two customers look alike; only you know whether they are one.
- What is genuinely unrecoverable comes along as a text field and stays readable. We do not throw it away, even when it is junk.
And the old sheet stays. Not as a working file, but as a read-only copy you can open when a question comes up six months from now about an order from 2024.
What you can prepare yourself
You do not have to clean up before we start, that is my job. But three things genuinely save time, and so money: turn colour coding into a column, agree on one date notation, and remove the loose notes under your table or move them to their own tab. The rest I sort out along the way.
How the rest of such a project runs is on from spreadsheets to software. If your old sheet still needs feeding from your accounting or the other way around, custom integrations belongs with it. And as far as you are concerned that sheet does not have to go at all: you do not have to get rid of Excel.