Moving From a Spreadsheet Without Losing the History
A spreadsheet is a pile of habits laid over the data. What to migrate, what to re-enter, and why importing a file nobody trusts makes the new system wrong on day one.
A business that runs on a spreadsheet does not have data. It has a spreadsheet, which is a pile of habits laid over the data, and the two are not the same thing.
The habits are the merges, the shading, the column that was renamed twice, the row somebody typed in on a Sunday afternoon, the total at the bottom of the sheet that a human computed and typed, and the second file that exists only because the first one got slow. The data is underneath all of that, and it is usually fine. The problem is that an import does not know the difference. It reads the merged cell, it reads the typed total, it reads the row from April alongside the four years around it, and it brings all of it across with the same confidence as the one clean line.
That is why importing a spreadsheet nobody trusts is not the same as starting empty. An empty system can be filled in correctly, one record at a time, by a person who knows what the record means. An imported system arrives already wrong, and it arrives wrong in a way that looks like settled history. Somebody has to un-do it later, and by then other decisions have been built on top of it.
So the question is not how to move the spreadsheet. The question is which parts of it are worth carrying, which parts are worth typing again, and which parts were never data at all.
The spreadsheet is a habit, not a database
Take a customer ledger of the kind almost every small business has. In the example we construct for this article, it has 214 rows and 23 columns, which is 4,922 cells. Check it: 214 multiplied by 23 is 4,600 plus 322, so 4,922. Nothing in that count is remarkable. Every one of those 4,922 cells carries a different promise, and a good number of them do not keep it.
A cell in a spreadsheet is a value and nothing else. A field in a system is a value with a meaning that was agreed in advance: one thing, one definition, one answer to the question what happens when it is empty. A spreadsheet cell has no declared meaning at all. The meaning is whatever the person who last typed in that column believed it to be, which is a question about a person rather than about the data.
That gap is where migrations go wrong, and it is why the same file can be right on Monday and wrong on Friday without anyone touching it.
Three things a spreadsheet is doing at once (illustrative)
Storing the data
The rows you would actually miss if the file disappeared: who the customer is, what was billed, what was received, and when.
Storing the working memory
The shading that means pending, the sort order that puts the big account at the top, the column position that the next person will look in first.
Storing the arithmetic
The total typed at the bottom, the carried-forward balance, the discount that was worked out long ago and pasted in as a number.
Only the first of those three is worth importing. The second is worth writing down, because it carries real institutional knowledge: the person who shaded a row yellow was telling the next person something, and if that something mattered, it should become a field or a status rather than a colour. The third is worth re-deriving, because arithmetic that a human performed and stored can be re-performed and checked.
What a cell actually holds
The illustrative table below lines up the six things that go wrong most often in a customer ledger, and says what each one does to an import. None of these is a rare accident. On a file that has been in use for a few years, at least one of them is already there.
Illustrative: what a spreadsheet habit does to an import
| What is in the file | What it looks like from outside | What an import does with it |
|---|---|---|
| A merged total row | One value sitting in the leftmost cell, blanks across the rest, bold text | Reads the value as a record and the blanks as empty fields for a customer who does not exist |
| A column that means two things | A header that was renamed in year two and never split | Stores both meanings under one field name, so every later query is ambiguous |
| A row added out of order | A transaction dated inside the current year but sitting at the bottom of the file | Imports it as the newest record when it is not, and the sequence stops meaning anything |
| A total typed by hand | A bold number in the last row, no formula behind it | Imports it as a fact, indistinguishable from a real figure |
| Dates stored as text | Left-aligned cells, or dd/mm and mm/dd both in use | Sorts alphabetically, so the file orders itself wrongly on contact with any sort |
| A second file for the same customers | A second sheet on a shared drive, last edited on a different date | Neither file mentions the other, so the same customer arrives twice |
The merged cell, and the total somebody typed
This is the most common single failure in a spreadsheet migration, and it is worth describing precisely because it is so easy to miss.
A ledger has 214 rows of customers. At the bottom, someone merged the cells across columns D to H and typed the column total there. The merge was cosmetic. The consequence is not: a merged cell in column D is a value, and columns E through H of that row are empty cells that merely look occupied. An importer reading row by row sees a 215th record with a name in one field and nothing in the rest, or sees a total where a customer should be.
Then there is the number itself. In the worked example we construct here, somebody typed 4,18,500 as the received total for the month. Adding up the received column by hand gives 4,07,250. The difference is 11,250. Check it: 4,18,500 minus 4,07,250 is 11,250. Nobody stole 11,250. Somebody excluded a column, forgot a row, or typed last month's figure into this month's total. The point is that the spreadsheet stored a number that a human believed, in a cell that looked like any other cell, and from that moment the file contained a fact and a mistake with identical appearance.
Both of those things are one row of damage that no downstream report can see. If the 11,250 came across as an opening balance, the new system is wrong on day one, and it is wrong in a way that looks like a real starting point rather than a typo.
A column that means two things
Here is a column that has quietly become two columns. The header is Rate. In the first part of the file it means the tax rate applied to that line. In the second part, added later by a different person, it means the rate as a percentage, inclusive of a freight charge that was folded into the price.
The constructed example we use throughout this article applies an 18% GST figure. That number appears only to keep the arithmetic checkable, not as advice. A line at a base of 1,00,000 with 18% added comes to 1,18,000: 1,00,000 multiplied by 0.18 is 18,000, and 1,00,000 plus 18,000 is 1,18,000. Now look at the same line in the second part of the file, where freight was included in the price. If the importer reads 1,18,000 as a base and adds 18% on top, it produces 1,39,240: 1,18,000 multiplied by 0.18 is 21,240, and 1,18,000 plus 21,240 is 1,39,240. That is a 21,240 error created by a column heading, on a line that was correct in the file it came from.
The rate that applies to a particular item, and the treatment of freight inside a price, are questions for your own chartered accountant. The migration point stands independently of the answer: a column that means two things must be split into two before import, or the meaning of the file has to be discarded and the line re-entered.
The row somebody added in April
In our constructed ledger, the entry dates show 14 rows written in a month different from the one the surrounding rows belong to. They are not errors in themselves. They are the record of somebody catching up, of a quarter that was entered late, of a phone call taken in a hurry and typed in weeks afterwards.
The problem is what an import does with them. A system that assigns sequence by file order will make those 14 rows the newest records in the file, which is a statement about the past that is simply false. A system that assigns sequence by entry date will interleave them correctly, which is better, and will still be working from a date column that is text in some rows and a real date in others, so a portion of the file still sorts by its first character.
Neither outcome is dramatic on the day it happens. It becomes dramatic the first time somebody asks for a month-end figure, gets an answer that includes transactions from the wrong period, and cannot tell from the screen whether the system or the question is at fault.
What is worth migrating, and what is worth re-entering
The decision rule is about how much damage a wrong value does, and how hard it is to notice. Historic financial records are worth migrating. They are numerous, they are the reason for keeping the file, and typing them introduces fresh mistakes into old data, which is a strange thing to do. Current state is worth re-entering. There are far fewer of it, it is what the business is judged on, and typing it means somebody actually looks at each record while the person who created it is still available to answer questions.
Illustrative: migrate the history, re-enter the current state
| The data | Why | What to do |
|---|---|---|
| Invoices and payments older than the current year | Too many to type, and a wrong figure here is hard to notice but expensive to explain | Migrate, then reconcile the totals against the old system before accepting |
| Customer names, addresses, tax details | The base of the record; mostly stable, and mostly right | Migrate, but clean the duplicates first rather than after |
| Open balances and who still owes what | This is what the business is actually run on, and it is short enough to read | Re-enter, with the person who knows each customer present |
| Price lists and rate cards | Small, current, and the thing staff will use daily | Re-enter from the current source, not from whatever the sheet says this month |
| Totals, subtotals, carry-forwards, balances | Not records at all; they are a stored answer to a question | Do not migrate. Re-derive them inside the new system |
| Shading, sort order, column position | Working memory, not data | Write down what each one meant, then discard it |
Re-entry is cheaper than it looks
The objection to re-entering is always time, so it is worth putting a number on it. This is a constructed example, not a benchmark, and the arithmetic is set out so you can replace the parts with your own.
Suppose the ledger has 214 customer rows and, of those, 61 customers have had a transaction in the last twelve months. That leaves 153 dormant rows: 61 plus 153 is 214. Now suppose the new system needs four fields per customer to be usable: name, contact, address, and the current balance. That is 61 multiplied by 4, which is 244 fields to type. At a constructed rate of 40 seconds per field, that is 244 multiplied by 40, which is 9,760 seconds. Divide by 60 and you have 162 minutes and 40 seconds, which is a little under three hours of typing for the entire active book.
Three hours, spread over a day or two, by the people who already know the answers. Compare that with the alternative: importing 214 rows including 153 dormant ones and 61 that turn out to be duplicates, and then spending the next month discovering which of them the business actually transacts with.
What to check in a spreadsheet before you import it (illustrative)
- Add up every total column by hand and compare it with the number sitting in the total cell. Record every difference before you move anything.
- Un-merge every merged cell, or note the row as excluded and say why.
- Split any column whose meaning changed over time into two columns, with the date the meaning changed.
- Decide, in writing, which system is the record for the overlap period before the overlap starts, not during it.
- Mark text-formatted dates and fix them, because any sort you do afterwards will use them as text.
- Write down what the shading meant. If it meant pending, pending has to become a status.
- Re-enter the current state rather than importing it, and do it with the person who created the records in the room.
- Reconcile the migrated history against the old system: same count, same total, same oldest and newest dates.
Why importing a spreadsheet nobody trusts is worse than starting empty
An empty customer list is honest. It says nothing, so it is wrong about nothing, and the first record you add is right because a person typed it. A list of 214 imported rows, 61 of which are duplicates and one of which is a merged total row, is not honest. It is confidently wrong, and it is confidently wrong in the specific direction where nobody is looking, because the people who could tell you a customer is a duplicate are the people who were not consulted during the import.
There is a second cost, and it is larger. Once the bad rows are in, they become inputs. Reports get run against them, balances get computed from them, and somebody makes a decision. Six weeks later the duplicate is discovered, and now the correction is not a data cleanup task; it is an investigation into which reports were run and what was decided on the strength of them. The cost of a bad import is paid at the moment it is found, not at the moment it is made.
If the spreadsheet is a customer list, start somewhere else
If what you are moving is customer records, ownership, and follow-up history, there is already a longer treatment of that specific move. Read CRM vs WhatsApp and Spreadsheets: Migration Checklist first — it deals with contact ownership, consent, stages and the open actions that have to be carried across, which this article deliberately does not repeat. Read CRM Basics for Small Businesses That Still Use WhatsApp and Spreadsheets next, for what a customer record is actually for before you decide what to keep in one.
For the billing side of a spreadsheet move, Moving From Excel to Billing Software covers data cleanup, opening stock, staged cutover, and what to ask for in writing about exports. This article is deliberately narrower and more general: it is about the shape of the file, not about billing software, and it has nothing to add about which counter workflow to run.
What both of those posts and this one agree on is the same thing. Migration is scoped work. It is not a button, it is not a self-serve import, and it is not something that can be done honestly to a file nobody has read.
What the new system can and cannot hold
Some of the things a spreadsheet does, a system deliberately will not do, and finding that out after the import is a bad time.
An invoice and a payment are different records. Paid is not a field on the invoice; it is a projection of what has been allocated against it, which is why a part-paid invoice and a settled one are the same kind of thing differing only in their allocations. A spreadsheet with a Paid column cannot express that, which is one reason the column is usually wrong.
There is no change-request record, no milestone record and no deliverable record, and there is no contract editor and no e-signature. A change of scope is a new quote raised against the same project, with its own number, which is a deliberate constraint: it means the agreed scope and what was asked for later are two records rather than two paragraphs inside one.
There is no timesheet record and no payroll module. Expenses are a Nox-Billings capability, and purchasing lives in the Commerce area, so a spreadsheet that tracked purchasing does not have a destination here. NoxOrigin does not file GST returns or any other statutory return, and where a rule matters the question belongs to your own chartered accountant. Shift close and day-end reconciliation are assisted-setup maturity rather than self-serve switches, which is the honest shape of the thing.
One last thing worth saying plainly. A spreadsheet is not a failed database. It is a tool that was the right size for the business two years ago, and it has grown the way tools do. The failure mode is not that anyone did anything wrong. It is that a file accumulated habits faster than it accumulated meaning, and nobody was ever asked to separate them.
The part of a migration most teams get wrong is deciding what not to bring. the data you should refuse to migrate
Frequently asked questions
Should I import my whole customer spreadsheet or start fresh?
Migrate the history, re-enter the current state. Old invoices and payments are too numerous to type and expensive to get wrong, so they come across and then get reconciled against the old totals. Open balances, current contacts and price lists are short enough to re-enter, and re-entry means a person who knows the customer is looking at every record while the new system is still being set up. What you should not do is import a file nobody has read and treat the import as a finished state.
What do I do about totals that do not add up in the old file?
Find them before you move anything, not after. Add up each total column independently and compare it with the number in the total cell. Where they differ, decide in writing which number you believe and why, fix the old file so the record is clean, and then import. Carrying a disagreement forward means two systems hold two versions of the same total, and from that point nobody can say which one the reports were built on.
Is the migration an import button or a project?
It is a project, and it should be scoped as one. Migration is scoped work rather than a self-serve import, because the hard part is deciding what each column means, which rows are duplicates, and what should stay behind. Any vendor that offers you a bulk import of a multi-year spreadsheet without asking what is in it is offering you the wrong thing.
Will the new system merge my duplicate customers for me?
Detection flags duplicates as candidates and a person reviews them. Match rules and merge behaviour are a setup decision, not a default, and nothing merges automatically. Two similar records can be one customer or two customers in the same family, and that is a judgement about a relationship rather than a question about string similarity.
What happens to a column that means two different things?
It has to be split into two columns before import, with the date on which the meaning changed, or it has to be discarded and re-entered. A single field cannot hold two meanings, and every report you run afterwards will be ambiguous in a way that is very hard to diagnose later because the data itself looks clean.
Should my accountant see the file before I migrate it?
Yes, if it contains tax rates, freight folded into prices, or anything that has to reconcile against filed returns. This article is not tax advice and does not decide what the correct rate on a given item is; that belongs to your own chartered accountant. What the article does say is that a column meaning two things must be resolved before import rather than after.