Fixing Your Spreadsheet Before Buying Software: What to Push Into It, and What Never To

Before buying a system it is worth trying to fix what already exists, because in most businesses the spreadsheet is not the problem - the problem is that it was built without structure. A well-structured sheet solves a considerable share of what a system would solve, at zero cost. But there are four things a spreadsheet should never hold at any company size, and there the question is not structure but tooling.

What to fix first

1. One row per record - no header rows in the middle and no merged cells.
2. An identifier column, unique per row, that never changes.
3. Dates stored as dates - not text, and in one format.
4. Closed lists for fields with fixed values, instead of free typing.
5. A separate sheet for reference data - lists, rates, prices.
6. No manual arithmetic inside a column that should hold a value.

The first two prevent most of the pain. Merged cells and mid-table headers make every filter and formula fragile, and the absence of an identifier turns every match between sheets into a guess by name - and names not being identifiers is the source of duplicates, as covered in customer master data cleanup.

A closed list: the cheapest fix available

A free-typed status column will contain, within two months, "paid", "paid ", "Paid", "pd" and "part paid". Every report counting on it will be wrong, and nobody will notice because the total still looks plausible.

Data validation with a closed list prevents that with one minute of setup. It is also what will let you export the sheet into a system later without cleaning it first - and that cleanup is usually the expensive part of a migration.

Four things never to push into a spreadsheet

The fourth row is the dangerous one. A formula that breaks in a sheet does not always produce a visible error - sometimes it simply stops including new rows, and the result looks perfectly normal. So any number somebody decides on needs a second check, even a subtotal compared by hand once a month.

What to do with a sheet that is already a mess

Do not rewrite it from scratch - that is the reliable way to lose data. What works is three steps: make a copy, tidy the copy, and compare totals between the two before switching.

The tidying follows a fixed order: first delete tabs nobody has opened in a year, then unmerge cells and remove mid-table header rows, then add an identifier column. Only at the fourth step do you touch formulas, because a formula fixed before the structure is sorted will need fixing again.

The comparison at the end is the step that prevents the quiet disaster. Two or three subtotals matching between original and copy are the proof nothing was lost, and it is a five-minute check that justifies itself the first time it fails.

What a spreadsheet does better than a system

• Fast change - adding a column takes seconds.
• A one-off calculation nobody will need again.
• Planning and scenarios - copy a tab and change numbers.
• A temporary list for a project that will end.
• Ad-hoc analysis of data exported from a system.

That is a genuine list, and worth remembering when somebody proposes moving everything into a system. Analysis and calculation will stay in a spreadsheet even in a business with a full ERP, and that is entirely fine - what should not stay there is the operational source of truth.

Formulas: what is safe and what is risky

Two formulas do most of the work in a business spreadsheet: looking up a value by identifier, and a conditional sum. Both are stable as long as the table is flat and there is a unique identifier - meaning the two fixes from the list above are also what makes the formulas stable.

What is risky is formulas pointing at a specific cell in another tab, and formulas linked across files. The first break when somebody inserts a row, and the second break when somebody renames a file or moves it to another folder - and in both cases the result is a wrong value that looks correct.

The practical rule: inside the file, reference ranges and columns rather than individual cells. Across files, prefer not to, and if you must, check the result once a month against a manual calculation.

How to know the fix was enough

After a tidy-up round, three things are worth checking: whether a report can be produced from the sheet without manual editing, whether two people can work in it at once without overwriting each other, and whether somebody else can understand it without an explanation.

If all three answers are yes, the sheet is enough for now and there is no reason to rush a purchase. If the second or third is no and fixing does not help, that is a sign from the family described in when Google Sheets stops being enough.

What about automation on top of the sheet?

You can, and often should - a form that writes to the sheet, a report sent from it, an alert on a new row. These are simple connections that return time with no purchase. What matters is knowing they are sensitive to structure: renaming a column or inserting one in the middle breaks them silently.

So the moment a sheet becomes a source for automation, its structure is effectively frozen. Changes go at the end rather than the middle, and column names do not change. That is a small price, but it has to be stated up front - otherwise somebody tidies the sheet on Tuesday and discovers on Wednesday that the report arrives empty.

Sources

• When Google Sheets stops being enough
• Microsoft - how to correct a #REF! error