Replacing a Spreadsheet That Runs the Business

What replacing a critical spreadsheet actually involves

These files are usually the most valuable software in the company and the least defended. They have no tests, no version history worth the name, no access control below the file level, and exactly one person who understands why column M is red. That person is also the reason the migration is hard, because most of the requirements are in their head and they will describe about a third of them if you ask directly.

The other thing to accept early: some of these files should not be replaced. A sheet used by two people once a quarter is fine as a sheet. What not to automate has the test. The candidates worth the money are the ones with many editors, a recurring deadline, and a number that leaves the building.

The four systems hiding inside one file

Separate the four before you design anything, because each one has a different replacement, a different owner and a different risk if you get it wrong. Teams that skip this step build a web form over a database and then discover the ritual is still happening in a sheet on someone's desktop.

The ritual is the one that gets missed. If the finance team's month end starts when a file lands in their inbox at 09:00 on the first working day, then that email is a load bearing part of the system. Removing it and replacing it with a dashboard they have to remember to open is a downgrade, whatever the dashboard looks like. Map the ritual the same way you map the process, using something like mapping a process before automating.

The autopsy, or how to find requirements nobody wrote down

Interviews will give you the happy path and nothing else. The file itself is a far better witness, because every exception the business has ever handled left a mark in it. Read the artefact, then use the interview to ask about what you found.

The data traps that will corrupt the import

Assume the export lies to you, because in several specific and well documented ways it does. Every one of these has shipped bad data into a production system, and all of them are cheap to test for before you import a single row.

• Dates disagree between products. Excel's serial date system treats 1900 as a leap year, a compatibility quirk inherited from Lotus 1-2-3, so serial 60 maps to a date that never existed. Google Sheets counts from 30 December 1899. Dates before March 1900 can shift by a day when a file moves between them. Rare in transactional data, fatal in anything historical.
• Locale reinterprets ambiguous dates on open. A cell showing 03/04/2026 becomes a different day when the file is opened under a different regional setting. Export dates as ISO 8601 strings before you import them anywhere.
• Identifiers get coerced. Leading zeros are stripped from codes, long numeric ids lose precision past fifteen digits, and short alphanumeric codes that resemble dates are silently converted into them. Import every identifier as text and validate against the source.
• The displayed value is not the stored value. A column formatted to two decimals still stores full precision, and some export paths write what is displayed. Your totals then disagree with the sheet by small amounts that are impossible to explain and impossible to ignore.
• Hidden rows and active filters distort the total. SUM includes hidden rows, SUBTOTAL does not, and the person reading the file has been quoting the filtered figure for two years. Establish which number is the real one before you promise to reproduce it.
• Merged cells drop data. Only the top left cell of a merged block holds a value on export. Everything else comes through empty and looks like missing data rather than a formatting choice.

Replace it in slices, never in one weekend

Use a strangler pattern: stand the new system up beside the sheet, move one workflow at a time, and leave the sheet in place as an output long after it has stopped being an input. Big bang cutovers on a file this central fail for a boring reason, which is that you cannot test the exception you have never seen, and month end always produces one.

1. Mirror, read onlyweek 1 to 2

   The new store ingests the sheet on a schedule and shows the same numbers on a screen. Nobody changes how they work. This proves your parser, your types and your key, and it surfaces the duplicate rows immediately without risking anything.

2. Move one write pathweek 3 to 5

   Pick the write path with the most volume and the least judgement, usually a bulk import or a form that one team fills in. Entry moves to the new system, and the system writes back to the sheet so every downstream reader keeps working unchanged.

3. Dual run through a full cycle2 to 3 cycles

   Both systems hold the truth and the reconciliation script runs every cycle. Differences are investigated, not averaged. Expect the sheet to be wrong sometimes, and expect that to be a conversation about a person rather than about code.

4. Flip the directioncutover

   The new system becomes the source and the sheet becomes a generated export, same layout, same column order, marked read only. Nobody has to change their reporting habit on the same day they change their entry habit.

5. Retire the ritual lasta month later

   Only once entry, rules and reporting have been stable for a full cycle do you replace the Monday email with a notification. Changing the ritual first is how a technically correct migration gets quietly abandoned.

Reconciliation is what earns the trust

Gate the cutover on a script that compares both systems row by row and exits non zero on any difference. Trust in a replacement is not won by a demo, it is won by several consecutive cycles where a machine confirmed the numbers matched and a person did not have to look.

Compare at three levels, because each catches a different class of bug. Row presence catches import filters and permission gaps. Field equality catches type coercion and rule differences. Totals catch rounding and duplicated rows that cancel out at row level. A migration that only checks totals will ship the day two errors happen to offset.

# reconcile.py
# Run once per business cycle for the whole dual-run. Exit code is the gate.
# Nobody is allowed to eyeball two tabs and declare them equal.

import csv, sys
from decimal import Decimal, InvalidOperation

KEY     = ["order_id"]                       # the real key, proven unique, not "row number"
COMPARE = ["customer", "status", "amount", "due_date"]
MONEY   = {"amount"}
TOL     = Decimal("0.01")                    # per-cycle total tolerance, not per row

def norm(field, v):
    v = (v or "").strip()
    if field in MONEY:
        try:
            return str(Decimal(v.replace(",", "") or "0").quantize(Decimal("0.01")))
        except InvalidOperation:
            return "NAN:" + v                # a text cell in a money column, surface it
    return v.casefold()

def load(path):
    rows = {}
    with open(path, newline="", encoding="utf-8-sig") as fh:   # -sig eats the Excel BOM
        for r in csv.DictReader(fh):
            k = "|".join((r.get(c) or "").strip() for c in KEY)
            if not k:
                sys.exit(f"BLANK KEY in {path}")
            if k in rows:
                sys.exit(f"DUPLICATE KEY in {path}: {k}")      # the sheet key was never unique
            rows[k] = r
    return rows

sheet, app = load("sheet_export.csv"), load("app_export.csv")

missing = sorted(sheet.keys() - app.keys())
extra   = sorted(app.keys() - sheet.keys())
diffs   = [(k, f, norm(f, sheet[k].get(f)), norm(f, app[k].get(f)))
           for k in sheet.keys() & app.keys() for f in COMPARE
           if norm(f, sheet[k].get(f)) != norm(f, app[k].get(f))]

tot = lambda d: sum(Decimal(norm("amount", r.get("amount"))) for r in d.values()
                    if not norm("amount", r.get("amount")).startswith("NAN"))
delta = tot(sheet) - tot(app)

print(f"rows      sheet={len(sheet)} app={len(app)}")
print(f"missing in app={len(missing)}  extra in app={len(extra)}")
print(f"field diffs={len(diffs)}  total delta={delta}")
for d in diffs[:20]:
    print("   ", d)

sys.exit(0 if not missing and not extra and not diffs and abs(delta) <= TOL else 1)

When the two disagree, resist the instinct to make the new system match. A large share of the differences you find will be the sheet being wrong: a formula that stopped at row 480 when someone added row 481, a filtered total, a stale manual override. Each of those is a finding, and it belongs in a document with the date, not in a silent code change that quietly reproduces the bug so the numbers agree.

The arithmetic, on your own numbers

The case for replacing a spreadsheet is usually not the time spent typing into it. It is the time spent checking it, fixing it after a bad paste, and rebuilding trust in a number that turned out to be wrong. Put all three in before you decide, and take a real baseline first, as in the baseline you must take before any automation.

That payback figure is optimistic on purpose, because it ignores the running cost of what you build. Add hosting, the support rota and the hours someone will spend on change requests in year one before you present it to anyone. If the honest payback is beyond two years and no number leaves the building, keeping the sheet and putting validation around it is the better engineering decision.

Where these migrations go wrong

Four failures account for most of the abandoned projects, and only one of them is technical.

Cutting over during the busiest cycle

Month end, quarter end and the annual audit are exactly when the exceptions appear and exactly when nobody has patience for a new screen. Cut over in the quietest week you have, keep the previous path warm for one full cycle, and write the rollback down before you start rather than improvising it at 19:00.

Permissions narrower than the sheet

The file was open to forty people because file level sharing is the only control a sheet has. Your app has proper roles, so twenty of those people lose access to something they were quietly using. They will not raise a ticket. They will export what they can reach into a new spreadsheet, and you have re-created the problem with an extra hop. Before cutover, list who currently opens the file and what each of them takes from it.

Nobody owns the replacement

A spreadsheet has an obvious owner: whoever is in the file. An internal tool has none unless you name one. Six months later the import breaks when a supplier changes a column heading, nobody knows whose job it is, and the team goes back to the sheet inside a week. Settle this before launch, using who owns automation after launch.

Taking the sheet away

Deleting the file feels like completing the project and it is the fastest route to losing the room. Keep generating it, read only, from the new system, with the same tabs and column order. It costs almost nothing, it gives sceptics somewhere to check, and it makes rollback a matter of changing one direction of sync rather than a recovery operation.

Definitions

Terms used precisely on this page

Spreadsheet to app migration

The replacement of a business critical spreadsheet with a system that holds the data under a schema, expresses its rules in tested code, and presents a task shaped interface, while continuing to produce the outputs the organisation already depends on.

Override ledger

A record of every cell in a spreadsheet where a person typed a literal value over a formula result, captured with who did it and why. It is the most reliable source of undocumented business rules in any such file.

Strangler pattern

A migration approach in which a new system is built alongside the old one and takes over one capability at a time, with the old system left running until every capability has moved, so there is never a single irreversible switch.

Dual run

A period in which the old and new systems both process the same work and their outputs are compared every cycle. It ends when reconciliation passes cleanly for an agreed number of consecutive cycles, not when a date arrives.

Reconciliation gate

An automated comparison of two systems across row presence, field equality and aggregate totals, whose exit code decides whether a cutover may proceed. It replaces human judgement about whether the numbers look right.