Moving off spreadsheets: a migration checklist that proves nothing got lost

An Australian distributor asked r/smallbusiness what sensible modernization looks like at $3M revenue and 5,000 orders a year: HubSpot for CRM, MYOB for accounting, FileMaker for sample loans, and "a graveyard of Google Sheets for dispatch, credit card payments, overseas supplier payments". Forty-three comments later, the advice ran to which system to buy and what made the business special — nobody laid out the part that decides whether the move actually works: how do you get years of spreadsheet data into a system and prove nothing got lost or bent on the way? So here is that answer, up front. A migration is a reconciliation. It is not done when the importer says success — it is done when the new system ties to the frozen spreadsheets on a control set you wrote down before you moved: record counts, control totals, open items, and opening balances. Miss that step and you will spend the next year not trusting the new system for the same reason you did not trust the sheets. The full checklist is below, and there is a printable version to take with you.

Why do spreadsheet-to-system migrations go wrong?

Because "imported" gets mistaken for "correct." An importer reports what it processed, not whether what arrived still means what it meant in the sheet. Data migration has a whole verification discipline for exactly this reason: after the transfer, data verification checks that records were translated accurately and completely — because the failure modes are quiet. A key column that lost its leading zeros or flipped to scientific notation on the way through CSV. A date column read in the wrong locale. A currency column that silently dropped its negative signs on refunds. None of these stop the import. All of them break every match you try to run afterward.

The other classic failure is duplication rather than loss. An operator on r/QuickBooks — mid-move, "reconciling accounts monthly using a very dense spreadsheet, and I'm hoping to move everything to QuickBooks Online" — got the sharpest description of it from a commenter: "you have two separate import streams creating duplicate records of the same transaction in QBO, and there's no automatic way for QBO to match them since they came in through different doors". Two sheets that both mention the same order become two records the moment you import both. The system did not make a mistake. The migration plan did — by never deciding which sheet owns which record.

What should you clean before you export?

One commenter in that thread — from a CFO-services firm, so read it as a practitioner with something to sell — pushed back on the buy-something reflex, and was half right: "don’t buy an erp because your process is messy. at $3m and 5,000 orders, the win is killing duplicate entry and paper handoffs first." Right — a system inherits your mess with better fonts. But "clean it up first" needs a concrete meaning, and for a migration it is this:

• One owning sheet per record type. Products, customers, open orders, open invoices, on-hand quantities: each gets exactly one sheet declared the source of truth for the move. Every other sheet that mentions those records is reference material, not import material. This is the single decision that prevents the two-doors duplicate problem above.
• A primary ID that survives the trip. Every row needs a stable, unique key — order number, SKU, invoice number — formatted as text so Excel and the importer cannot reshape it in transit. If rows have no natural key, mint one now and keep it through the import; it is how you will trace any missing row back.
• Deduplicate before you export, not after. Near-miss duplicates — the same vendor spelled two ways, the same invoice keyed twice with slightly different numbers — import as clean separate records and are much harder to unpick inside a system. Fuzzy-match them in the sheet while it is still cheap.
• Normalize the fragile formats: dates to ISO YYYY-MM-DD, one currency convention, no merged cells, no color-as-data. If the meaning lives in formatting, it will not survive a CSV.
• Migrate facts, not formulas. Derived columns — running balances, lookups, subtotals — get recomputed by the new system. Exporting them just gives you two versions of a number that can disagree later.

What does "the migration is done" actually mean?

Write the definition down before you import anything, because afterward it is too easy to declare victory. Done means: the new system reconciles to the frozen sheets on a small set of controls, every control passes or has a named, explained exception, and someone signed it. That is the same standard an auditor holds a reconciliation to — not "it looks right," but "here is the evidence." The control set is short:

Opening balances deserve the extra sentence, because the accounting platforms are explicit about them. Xero calls them conversion balances and expects you to bring the trial balance from your previous system as of the day before your conversion date; QuickBooks Online has the same concept as opening balances, offset through an opening-balance-equity account. If your "previous system" is a stack of sheets, the closing totals of those sheets on cutover day are your trial balance. They have to tie — that number is the foundation every future reconciliation builds on.

How do you prove the import, step by step?

1. Pick the cutover date. First of a month, immediately after a close you trust. Xero requires the first of a month for its conversion date; it is the right convention everywhere else too.
2. Freeze the sheets. Make a dated, read-only copy of every owning sheet. This frozen set is the migration baseline: every check below compares against it, not against a live sheet someone can still edit.
3. Record what is leaving. For each frozen sheet: row count, and the sum of every amount, quantity, and balance column. Write them in a control log.
4. Clean per the list above, in a working copy — never in the frozen baseline.
5. Export and import. Keep the key column formatted as text end to end.
6. Count what arrived. Pull record counts and the same sums from the new system's reports. Compare against the control log.
7. Find the misses by name. If counts differ, anti-join the frozen sheet against a system export on the primary ID — COUNTIF / MATCH or XLOOKUP does it in minutes — so you get the exact rows that dropped or duplicated, not just a discrepancy.
8. Tie the opening balances. Enter them from the frozen sheets' closing totals, run the system's trial balance, and confirm every line. Do not plug a difference to make it balance — a plug today is an unexplainable number forever. Investigate: it is a dropped row, a duplicate, or a bent field, and step 7 will name it.

Control log, per exported sheet
-------------------------------
rows_out    = COUNTA(A2:A5001)        row count leaving the sheet
total_out   = SUM(F2:F5001)           per amount / qty / balance column

After import, from the system's own reports
--------------------------------------------
rows_in     = record count for the imported type
total_in    = same sums, from a system report

Pass condition
--------------
rows_out = rows_in   AND   total_out = total_in   (to the cent / unit)

If not — name the rows. Anti-join on the primary ID:
----------------------------------------------------
=COUNTIF(SystemExport!A:A, A2)        0    = this row never arrived
=COUNTIF(A:A, A2) > 1                 TRUE = duplicate in the source

Should you run the old and new systems in parallel?

For one full cycle, yes. Parallel running — operating both, feeding both the same transactions, comparing the outputs — is the classic way to prove a new system before you depend on it, and comparing those outputs is just reconciling two systems, which after this migration you are already set up to do. Honesty about the cost: it is double the work, which is why the textbook caveat is that it gets prohibitive at scale. So scope it. One month-end in both. If your operation is bigger, parallel only the processes where an error is expensive — cash, open orders, inventory on hand — and cut the rest over directly. What you are buying is one close where a disagreement points at a configuration mistake you can still fix cheaply, instead of a mystery you discover in production.

What happens to the old spreadsheets?

They get archived read-only, and they never get deleted. The frozen sheets are the evidence behind your opening balances — the thing you point at when a number is questioned two years from now. When a poster on r/FPandA described a "full reset" of their finance stack that took the close from 8 days to 2, the replies went straight at the trail: "Did you actually migrate the ledger or just start a new 'Company' file in QB? Because starting fresh is cheating lol", and a more pointed one — cutting a close by 75% "usually means you’re cutting corners on the audit trail". That is the reputation a bridge-less migration earns. The frozen baseline plus the signed control log is what makes yours defensible instead.

One more thing, because it is the part everyone skips in the relief of being done: the checking does not stop at go-live. The day-one control set — counts, totals, anti-join on the key — is exactly the check you should keep running between the new system and whatever still feeds it, because integrations drop records too, and they do it silently. The migration taught you to verify a transfer instead of trusting it. Keep the habit; it is worth more than the new software.

← All writing