When to replace a spreadsheet with an internal tool, and how

Signs the spreadsheet has become a system

Nobody decides to run a department on a spreadsheet. It starts as a quick list. Two years later it has 14 tabs, a color code only one person understands, and the weekly operations meeting runs off it.

Count how many of these are true:

• More than three people edit it each week.
• There are copies with names like "FINAL_v3_updated."
• Someone copies data from one sheet into another on a schedule.
• It drives money: invoices, pricing, commissions, payroll or purchase orders.
• Decisions get made from it without anyone checking the numbers.
• One person understands how it works, and their vacation is a problem.
• People say "don't touch column K."
• It has a status column, and people check it to decide what to do next.
• It's slow to open, or you split it because it got too big.

One or two: tidy it up. Three or more, especially the money ones: it's a system, and it deserves to be treated like one.

The risk is real, and well documented

Spreadsheet errors aren't about careless people. Spreadsheets hide mistakes. Formulas are invisible until you click a cell. Nothing stops a pasted value from quietly replacing a formula.

Raymond Panko of the University of Hawaii spent years collecting research on this. In his summary of audits of real business spreadsheets, the most rigorous studies found errors in 91% of the 55 spreadsheets they examined. His broader review concludes that spreadsheet errors are both common and non-trivial.

Some of them make headlines:

• In 2013, researchers rechecking an influential economics paper by Carmen Reinhart and Kenneth Rogoff found coding errors and excluded data in the calculations. Corrected, the paper's headline finding on debt and growth looked very different.
• JPMorgan's internal review of its 2012 "London Whale" trading losses found that a key risk calculation ran on spreadsheets with manual data uploads, a setup reviewers called "error prone" and "not easily scalable". Errors in that model muted volatility by a factor of two, so the risk looked smaller than it was.
• In October 2020, Public Health England left 15,841 positive COVID-19 cases out of its daily figures. Reporting at the time traced it to an older Excel file format limited to 65,536 rows per sheet. Current Excel allows 1,048,576 rows, but every tool has a limit somewhere.

Your spreadsheet probably won't make the news. But a wrong price, a missed reorder or a double-paid invoice costs real money, and you may not find out for months.

The options ladder

Replacing a spreadsheet doesn't always mean custom software. There are four rungs. Climb only as high as you need.

The first rung is free

Before anything else, tidy the sheet. Separate inputs from calculations. Lock the formula cells. Use dropdowns for status and owner so values stay consistent. Keep one row per thing, with no merged cells or subtotals in the middle of the data. Name one owner who approves changes to the structure.

Sometimes this is enough. If it is, you've saved yourself a project. If it isn't, you now have a clean picture of what the new tool must do, which makes every next rung cheaper. Our page on replacing spreadsheets covers what the later rungs look like in practice.

How to choose a rung

Five questions get you most of the way.

1. How many people use it, and do they need different access? A shared tool handles one team. Many roles with different access push you up the ladder.
2. Does it need to connect to other systems? If people retype from the sheet into accounting or your customer database, connections matter more than screens.
3. Are there rules that must never be broken? "No discount over 15% without approval" is easy to write down and hard to enforce in a spreadsheet.
4. What does a mistake cost? A wrong cell in a lunch order costs nothing. A wrong cell in a price list costs every order that uses it.
5. Who will maintain it? A tool nobody owns decays, whatever rung it's on.

If your answers point to the top two rungs, the next question is not which tool, but who will map the sheet and build around it. That's where the risk lives, and it's where an experienced builder earns their fee.

Migrating without stopping work

The spreadsheet is live. Orders go in it every day. You can't pause the business while a new tool gets built, and you shouldn't have to. A few principles keep the move safe.

Map before you build. Every tab, formula, hidden column, color and comment carries meaning. So does every person who opens the file, including the ones who only pull a number once a month. A good mapping turns up users and rules nobody mentioned.

Start with the most painful workflow, not the whole sheet. One tab done well teaches the team more than nine tabs done at once.

Run side by side. For a few weeks, the new tool and the old sheet both carry the work, and the results get compared daily. Each difference is either a bug or a rule nobody wrote down. Both are cheaper to find now.

Pick a switch date and lock the old sheet. Two places to enter data is worse than either one alone.

Never take away the export. People should be able to pull their data into a spreadsheet in one click, in the shape they're used to.

A made-up example: a 60-person field service company tracks jobs in a shared sheet with 2,000 rows and nine tabs. Mapping turns up three quiet users. Billing pulls completed jobs every Friday. A manager builds a weekly report. A lead technician checks parts orders. Job tracking moves first, the parts tab stays in the spreadsheet for now, and billing gets a Friday export in exactly the old format. Two weeks side by side reveal a missing rule about rush jobs. It gets fixed, the team switches on a Monday, and the old sheet becomes read-only.

How long each phase takes, how much history to bring over, and how to clean it on the way: those depend on your sheet. There's no template for it, which is exactly why the mapping comes first.

What to keep in spreadsheets

Spreadsheets are excellent tools. The trouble starts when they become a shared database. Keep them for:

• One-off analysis and what-if models.
• Budgets and forecasts that a small group owns.
• Exploring data exported from your new tool.
• Personal lists and drafts.
• Early sketches of a new process, before anyone knows the rules.

A good internal tool works well with spreadsheets. It exports cleanly and lets people do their own analysis. Fighting that habit is a fight you'll lose.

Permissions and history

Spreadsheets struggle with two things: controlling who can do what, and showing who changed what.

Sheet protection prevents accidents, but it isn't security. Google's own help page on protecting ranges says it shouldn't be used as a security measure, because people can still copy, print and export a protected sheet.

Any replacement needs clear answers on who can see each kind of record, who can create, edit and delete, which changes need approval, and how long the change history is kept. That last one varies by tool and plan. Airtable, for example, keeps record history for two weeks on its free plan and longer on paid plans.

A custom tool can record every change for as long as you like: who, when, the old value and the new one. When a number looks wrong, that log turns an argument into a two-minute lookup.

What most guides miss

The spreadsheet holds rules nobody wrote down. Colors, comments, a cell everyone knows not to touch. Miss these and the new tool will feel broken on day one, even if it works perfectly.

The quiet users matter most. Someone pulls one number every month for a board report. Break that and you'll hear about it at the worst moment. Ask "who else opens this?" more than once.

Don't move the mess. Migration is your best chance to drop columns nobody uses and merge duplicates. Every bad habit you import becomes a feature you have to keep.

Flexibility was a feature. People added a column whenever something new came up. The new tool needs a safe outlet for that, or side spreadsheets grow back within months.

Ownership beats tooling. A well-owned spreadsheet beats an orphaned app. Whatever you choose, name the person who decides what changes.

How we do it

We start by mapping the spreadsheet with the people who use it, including the quiet ones. If a cleanup or an off-the-shelf tool is enough, we'll say so. When it isn't, we build a custom internal tool around how your team actually works, run it side by side with the sheet, and keep the exports your team relies on. We also stay on after the switch, because the first months surface new rules and requests, and a tool that keeps up with the business is what stops side spreadsheets from growing back. Tell us what the sheet does and we'll tell you which rung it needs.