We run this process in a spreadsheet: when is it time to build software?

Deciding to move a process out of a spreadsheet has almost nothing to do with how many rows the file has. Four questions settle it: does more than one person edit the same data at the same time, does a wrong entry get caught the moment it is typed, can you answer "who changed this cell and when" two months later, and does the sheet decide money, stock or a promise made to a customer. Two yes answers and you are not running a spreadsheet, you are running a production system with no controls on it. The fix is not to ban Excel. It is to take the system-of-record job away from it.

The problem is not the spreadsheet, it is the role it drifted into

For exploration, modelling and one-off calculations, a spreadsheet is still the fastest tool anyone has. One person builds it in half an hour, asks nobody for permission, and it works. That is genuinely valuable. The trouble starts when the half-hour artefact is accepting orders, setting prices and feeding payroll three years later.

What you need from a system of record is a different list: rules enforced at entry, concurrent access, permissions per row, change history, undo, restore from backup. Microsoft 365 co-authoring solved the file-is-locked problem well enough. It did not add a rule saying this quantity cannot be negative, or a constraint saying a sales rep cannot see another rep's accounts. Those rules do not live in the file. They live in the attention of whoever happens to be using it.

The failure is silent, which is the whole problem

In October 2020, Public Health England reported that 15,841 positive COVID cases never made it into the daily figures between 25 September and 2 October. The lab-result template was being collated in the old XLS format, which caps a sheet at 65,536 rows. When the files filled up, new results were dropped without an error. The cases existed in the lab data. They just never reached contact tracing.

The lesson is not the row limit. The current XLSX format allows 1,048,576 rows and 16,384 columns per sheet, so that exact failure will not repeat. The lesson is the silence. A database rejects the write and tells you why. A spreadsheet accepts almost anything and says nothing, and by the time somebody notices, the wrong numbers have been in three reports and one board pack.

Your data gets converted without being asked

Excel guesses the type of every value you paste, and sometimes it guesses wrong. Genetics gave up arguing about it in 2020: the HGNC renamed 27 gene symbols that Excel kept turning into dates, so MARCH1 became MARCHF1 and SEPT1 became SEPTIN1. The scale had been measured first. A 2016 study found corrupted gene names in supplementary files of 19.6% of 3,597 papers. A broader 2021 follow-up found the same class of error in 30.9% of 11,117 papers, so publicity about the problem had not reduced it.

Microsoft added an "Automatic Data Conversion" section under Options in 2023 (version 2309 on Windows, 16.77 on Mac), where you can stop mixed letter and number strings being read as dates. It has to be set before the data goes in, and it does not catch every case. Most businesses meet the same behaviour in a less exotic form: leading zeros stripped off phone numbers and account codes, long IBANs and barcodes flipped into scientific notation, SKUs read as dates, and decimal separators swapped on CSV import between locales. All of it silent.

One keystroke, ten thousand tickets

For the 2012 London Olympics, four synchronised swimming sessions were oversold by 10,000 tickets. A staff member had typed 20,000 into the availability spreadsheet instead of 10,000. The mistake surfaced weeks later, and organisers had to contact roughly 3,000 customers to swap their tickets for other events.

In software that error is one constraint: tickets sold cannot exceed capacity. A spreadsheet has no opinion about what is plausible, because it does not know what the number means. You can add data validation rules, and you should, but the next person who pastes a block of cells usually pastes straight through them.

When copy and paste becomes the process

JPMorgan's internal review of the 2012 trading losses, published in January 2013, described how the risk model actually ran: a series of Excel spreadsheets that had to be populated manually, by copying and pasting data from one sheet into the next. The review found operational errors in that chain, including data copied from the wrong cells, and the model understated the risk it was supposed to measure. The losses came from the positions, not from the spreadsheets. The warning system that should have flagged them earlier depended on a copy-paste step.

The version of this you have at home is smaller and just as real. If somebody downloads a report from one system every morning and pastes it into another, that person is your integration layer. On the day they are on leave, ill or distracted, the process either stops or produces something wrong quietly. Until it becomes a real connection, like proper data sync between your ERP and storefront, you pay that cost again every month.

Why reviewing it by eye does not work

The research on spreadsheet errors is unusually consistent. Ray Panko's survey of the field lands on three conclusions: errors are rare per cell but large workbooks are very likely to contain at least one wrong bottom-line value, those errors are extremely hard to detect and correct, and the people who build spreadsheets, along with the companies relying on them, are badly overconfident about their accuracy.

The third one costs the most. "We check it" usually describes a habit rather than a control. The software equivalent of checking is a rule that lives in one place in the code and runs on every record, whether or not anyone remembers it exists.

Five signals that it is time

• More than one person edits the file, and the "who saved last" argument happens more than once a month.
• File names carry versions: quote_final_v3_really_final.xlsx. The one person who knows which is correct can go on holiday.
• A human moves data between two systems. The process runs on their calendar.
• You cannot answer when a row changed or who changed it. There is no history and no audit trail.
• The sheet sets money directly: pricing, stock, commission, payroll, invoicing. The cost of an error is a number you can calculate.

Two of these and it is worth a conversation. Three and it deserves a date in the plan.

Once the file leaves the building it does not come back

Email a spreadsheet and you cannot revoke it. You cannot see who downloaded it, who forwarded it or how many copies sit on which laptops. Six months after somebody leaves, a customer list from last spring is still on a personal drive and nobody knows. Inside a system, access is switched off in one place, who sees what is defined by a roles and permissions model, and every read and write can leave a trace.

Macros deserve a separate note. Office on Windows has blocked VBA macros in files downloaded from the internet by default since July 2022, Excel included. If a process you rely on is automated with macros, plan for the fact that it will eventually ask a user to click past a security warning, which is exactly the habit you spend money teaching people not to have. And if the file holds customer data, losing it can carry a notification obligation on top of everything else.

What you are actually buying

Software gets demonstrated with screens, which is misleading, because the screens are not the purchase. You are buying six things: validation at entry so invalid records never exist, concurrency so two people can work without overwriting each other, permissions so access matches the org chart, an audit trail recording who changed what and from which value, integration so data stops being carried by hand, and reporting you can recompute so the same question gives the same answer every time.

Notice what is missing from that list: speed. For a power user who knows the spreadsheet by heart, the new system feels slower for the first few months. The gain shows up in hours not spent reconciling and correcting, not in seconds saved on data entry. Say that out loud before the project starts, because the thing that usually kills these projects is the expectation, not the software.

You do not have to move all of it

The goal is not a spreadsheet-free company. The goal is that the system of record is no longer a file. Analysis, scenario modelling, budget work and one-off list comparisons can stay exactly where they are. What moves is the data other people transact on.

There are three legitimate destinations: an off-the-shelf SaaS product, an internal app on a low-code platform, or custom software. The process decides. Work that is standard across your industry, like accounting, HR or support ticketing, is usually done better and cheaper by a product someone else maintains. Work that is genuinely how you compete is where custom development pays. Low-code sits in between and is often the right first step, as long as you ask about per-user licence cost, governance and what you would be left holding if you needed to move off it later.

That spreadsheet is already your specification

The best input to the new system is the file you have been using for years. The formulas are business rules. The rows highlighted in yellow are exceptions, and usually one person knows why each of them exists. Getting those written down is most of the work, and interrogating the existing sheet line by line is far faster than trying to write a requirements document from a blank page.

Expect the data itself to be the slow part. The same customer spelled three ways, mandatory fields left empty, 1900 dates standing in for "unknown". The usual rules of migrating legacy data apply here too: rehearse the load, reconcile the counts, and run the sheet and the system side by side for one period until the differences are explained rather than tolerated.

A first step that fits into this week

Open one page and list the spreadsheets that make decisions in your company. Four columns per row: what breaks if this is wrong for a week, how many people edit it, does it contain customer or staff data, and who owns it. The list is rarely longer than fifteen rows, and two or three of them will stand out immediately.

Then take the riskiest one and answer a single question: what is the smallest change that stops this file being the system of record? The answer is usually not a project that rewrites the whole process. It is moving one form and one validation rule somewhere else. If you already know which spreadsheet worries you, that is the one to start the conversation with.