Replacing spreadsheets with a proper system, step by step

A spreadsheet stores whatever is typed into it. A proper system stores records: each client, job or order exists once, with fields that must be filled in, values that have to make sense and stages it moves through. People sign in, see what their role allows, and every change is kept with who made it and when.

Size is rarely the reason to move. An Excel worksheet holds 1,048,576 rows by 16,384 columns (opens in a new tab), and a Google Sheets file up to 20 million cells (opens in a new tab). What runs out long before that is control: which copy is current, who changed a figure, and who should be able to see it.

Whether the new system should be a product you buy or one built for you is a separate question, answered in build, buy or keep the spreadsheet. The six steps below apply either way.

Find every spreadsheet the process touches, including the copies on laptops and the versions sent around by email. For each one, write down:

• what it is for, in one sentence, such as “tracks every job from booking to completion”
• who adds to it, who changes it and who only reads it
• which decisions and reports depend on it
• where its data comes from, and where it gets copied to next
• which tabs and columns are still used, and which nobody has touched in months.

Then read the formulas. They are business rules written as cell references: when a job counts as overdue, how a total is worked out, which orders need a second approval. Each one becomes a rule in the new system, so each needs a plain-English description and someone who can confirm it is right.

Note the work people do around the spreadsheet too: the reminder someone sends every Friday, the figure someone checks by hand before it goes out. Those are often the first things worth automating.

A spreadsheet row often mixes several things: a client, a job and a payment on one line, with the client's details typed again on every row. In a proper system each of those is its own record, entered once and linked to the others.

For each kind of record, decide:

• its fields, which of them are required and what each one holds: a date, an amount, a choice from a fixed list
• the stages it moves through, and what has to be true before it moves on
• who can create it, change it, approve it, or only see it
• what should happen on its own, such as a reminder, a document or an update in another system.

Do not copy the spreadsheet's layout. Its columns were added one request at a time; the new system should follow the work. With steps 1 and 2 written down, choosing between a product, a configurable platform such as Lark Base and a custom system gets much easier, because you can test each one against your own list.

Data that was good enough for people to read is rarely good enough for a system to check. Expect to fix:

• duplicates, such as the same client typed three different ways
• mixed formats: dates stored as text, amounts with notes in the same cell
• free text where a fixed choice belongs, such as one status spelt five ways
• merged cells, colour used as data (a red row meaning “on hold”) and totals typed in by hand.

Clean a copy, not the live sheet, and write down every fix as a rule, such as “merge clients with the same ABN”. The final export will need the same treatment, and rules can simply be run again.

Then decide what moves at all. Open work and recent history usually do; older records can stay in the archive from step 6. Most small businesses are not covered by the Privacy Act, but some are (opens in a new tab). If your business is covered, the move is also the time to deal with personal information you no longer need: Australian Privacy Principle 11.2 (opens in a new tab) requires reasonable steps to destroy or de-identify it, unless an Australian law or a court or tribunal order requires you to keep it.

Set up the records and rules from step 2, then import a full copy of the cleaned data, not a sample. Check the result the way an auditor would:

• the number of records matches the number of rows that should have moved
• the totals match: open amounts, jobs in each stage, records per client
• the awkward records look right: the oldest, the largest and the ones full of notes
• the people who use the spreadsheet every day can do their normal tasks in the new system.

Run the import as many times as it takes, the same way each time. The import on switch-over day should repeat one that already worked.

If some of the data already lives in another system, such as the accounting software or the CRM, connect the two through their APIs instead of importing a copy that will drift out of date.

Running the spreadsheet and the new system side by side for weeks feels safe, but it doubles the typing and splits the truth in two. Pick a date, tell everyone, and on the day:

1. make the spreadsheet read-only, so nobody can type into it
2. run the final import, the same way as the tests
3. check the counts and totals one last time
4. start all new work in the system.

If a figure matters a lot, such as a total that goes to customers, compare the system's result with the spreadsheet's for the first cycle. Compare the results; do not keep typing into both.

Do not delete the spreadsheets once the system is live. Keep them read-only, somewhere the right people can find them, and note in the new system where they are.

Tax is one reason. The Australian Taxation Office says most business records must be kept for five years (opens in a new tab), generally counted from when you prepared or obtained the record, or completed the transaction it relates to, whichever is later. The same page says records must be stored in a way that protects them from being changed, and that you need to be able to reconstruct your original data if your record-keeping system changes over time.

Read-only matters for a second reason: an old figure that can still be edited will, sooner or later, be edited. And where an old file holds personal information that no law requires you to keep, the rule from step 3 applies to the archive as well.

• rebuilding the spreadsheet's columns one for one, instead of following the work
• moving everything, including years of records nobody reads
• running both side by side with no end date
• leaving the formulas undocumented, so nobody can confirm the new rules are right
• no owner in the business to decide what each field means and settle the arguments about it.

None of these is technical. The move goes well when someone who knows the work makes the calls, and the system follows.

Vetrox is an Australian IT company that designs and builds custom systems for businesses across Australia, including replacing spreadsheets. We go through the spreadsheets with the people who use them, keep the columns and rules that matter, and move the data into a database with screens built for each task. You check the moved data in a test environment before anyone switches over, and it can still be exported to Excel afterwards. If a product or Lark fits better, we will say so on the first call.