From spreadsheet to system: moving years of rows

A spreadsheet that has run your business for years holds more than rows. It holds decisions, workarounds and habits that nobody ever wrote down. The risky part of moving to a real system is rarely the technology. It is losing something the old sheet did quietly.

The approach below is how we avoid that. It is slower at the start and much faster than recovering from a bad cut-over.

#Inventory the sheets and who edits them

Start with a list, not with code. Write down every spreadsheet that touches the process, where it lives, and who changes it. Include the copies on someone’s desktop.

For each one, note:

• What it is for, in one sentence.
• Who edits it, and who only reads it.
• Which columns are typed by hand and which are formulas.
• What other sheets or tools it feeds.

Formulas matter more than people expect. A column of calculated totals is a business rule in disguise, and the new system has to reproduce it exactly or explain the difference.

#Clean and normalize before you move anything

Old data is never as tidy as it looks. Three problems show up almost every time:

• Duplicate customers. “Acme Supply”, “ACME Supply Co.” and “Acme supply” are one customer with three histories.
• Dates in three formats. Some typed as 03/04/2025, some as March 4, some as text that only looks like a date. Decide whether 03/04 means March or April by checking against rows where the answer is obvious.
• Free text that should be a list. A “Status” column with forty spellings of “done” needs to become five fixed values.

Do this cleaning in a copy, and keep a log of every rule you applied (for example, “trimmed spaces, lowercased emails, merged customers with the same email”). That log is how you explain a changed number later.

Duplicates are easy to find once the data is in a table. A query like this lists every email that appears more than once:

01SELECT lower(trim(email)) AS email, count(*) AS copies02FROM staging_customers03GROUP BY 104HAVING count(*) > 105ORDER BY copies DESC;

A person should review the list before anything is merged. Two people at the same company sharing an inbox are not duplicates.

#Import into a staging copy and compare totals

Never import straight into the live system. Load everything into a staging copy first, then prove the data arrived intact.

The checks that catch the most problems are boring ones:

1. Row counts per sheet match row counts per table.
2. Totals match: sum of invoices, count of open orders, balance per customer.
3. A sample of 20 or 30 records, picked by the people who know the business, looks right when opened in the new screens.
4. The oldest and newest records are both present.

If a total is off, do not shrug and move on. Find the rows that explain the gap. Often it is a hidden row, a filtered view someone forgot, or a formula that quietly skipped blanks.

#Run old and new side by side

Before the switch, let the team use both for a while. New entries go into the new system, and someone confirms they match what would have gone into the sheet. This is where you find the things nobody mentioned: the color-coding that meant “call before shipping”, the column used as a private notes field.

Keep this period short and specific. Decide in advance what “good enough to switch” means, such as no unexplained differences in the totals for a full billing cycle, and who has the authority to say so.

Side by side costs your team extra effort, so plan for it. It is still the cheapest insurance in the project.

#Pick a cut-over day with a rollback plan

Choose a day when the business is quiet, not the end of the month. On the day:

1. Freeze edits to the old sheets and tell everyone when.
2. Run the final import from the frozen copy.
3. Repeat the count and totals checks one more time.
4. Have the people who will use the system check their own work first.

Write the rollback plan before you need it. It should say what triggers a rollback, who decides, and what happens to anything entered in the new system in the meantime. If the plan is “we will figure it out,” it is not a plan.

#Keep the old sheet, read-only

After the switch, do not delete the spreadsheet. Set it to read-only, rename it so nobody mistakes it for the live one, and keep it somewhere everyone can find. It is your reference when someone asks why a 2022 number looks different.

Also say in writing that the sheet is no longer the source of truth. Otherwise someone will keep updating it “just in case”, and you will have two systems again.

We keep a “Spreadsheet-to-system checklist” that walks through these steps, and we fill it in with the client for each migration. The point is that every sheet, editor and total gets looked at before the day, not after.