When a Spreadsheet Is Actually the Right Tool

This is a blog about escaping spreadsheet purgatory, so you might expect us to say every sheet should be burned. We won’t. Some of the most useful work in your company happens in spreadsheets, and replacing it with a rigid tool would make it slower and worse.

The trick is knowing which sheets are doing the job they were built for, and which ones have quietly become something else.

The line: analysis versus process

Here’s the distinction that does most of the work.

Analysis is when you use data to answer a question. What happens to margins if shipping costs rise 8%? Which product lines grew last quarter? How many people do we need to hire to handle peak season? You pull some numbers, play with them, reach an answer and move on.

Process is when the data represents ongoing work that people act on. An order moves from received to packed to shipped. A request gets approved or rejected. A customer record gets updated whenever someone speaks to them. The data isn’t answering a question; it’s the system of record for how the work gets done.

Spreadsheets are superb at analysis. They are mediocre at process. Most of the pain this blog talks about comes from using a great analysis tool to run a process.

Five jobs a spreadsheet does brilliantly

One-off modelling

Your finance lead wants to know whether opening a second warehouse pays off within three years. They’ll build a model, tweak assumptions, try a dozen scenarios and present the result. Next year they might rebuild it from scratch with new numbers.

No internal tool does this better. Models need freedom: any formula, any layout, any what-if. Locking that into a structured system would just slow the thinking down.

Ad-hoc analysis

Someone exports last month’s support tickets and wants to see which categories spiked. They sort, filter, build a quick pivot table (a summary that groups and totals rows by category) and have an answer in twenty minutes. The file might never be opened again, and that’s fine.

Personal scratchpads

Your account manager keeps a private list of follow-ups, half-formed ideas and notes from calls. Only they use it. If it vanished, they’d be mildly annoyed and rebuild it in an afternoon.

That’s a perfectly good use of a spreadsheet. The risk only starts when the rest of the team begins relying on someone’s personal scratchpad.

Early-stage prototyping

Before a process is stable, you often don’t know what fields you need or what the steps are. A spreadsheet is an excellent sketchpad. A small team piloting a new partner referral scheme can run it in a sheet for a couple of months, learn what actually matters, and then design a proper tool with far better information.

The mistake isn’t starting in a sheet. It’s never leaving.

Small, stable, low-stakes lists

A list of office equipment serial numbers, updated twice a year by one person. The team’s holiday rota. A reference table of country codes. These are small, rarely edited, and nothing serious happens if there’s a typo. Building an app for them would be overkill.

Warning signs that analysis has become process

Sheets drift. A model built for one decision gets reused every month. A scratchpad gets shared “just so the team can see”. Watch for these shifts:

• Other people start editing it. One owner is analysis. Many editors usually means process.
• It has a status column. If rows move through stages and someone acts on each stage, that’s a workflow.
• It’s never finished. Analysis reaches an answer. Process just keeps accumulating rows.
• Other things depend on it. Reports, invoices or decisions pull from it on a schedule.
• People get reminded to update it. Nobody needs a reminder to finish their own analysis.

If two or more of these apply, your sheet has probably crossed the line. The 7 Signs Your Spreadsheet Has Become a Database (And Is Failing at It) has a fuller self-assessment.

A quick decision table

Mostly left column? Leave it alone and stop feeling guilty. Mostly right column? It’s worth a closer look.

The healthy pattern: process in a tool, analysis in a sheet

The best setup isn’t “no spreadsheets”. It’s each tool doing what it’s good at.

Picture a fictional services firm, Halden & Co. Their project tracking used to live in a giant shared workbook. They moved it into a proper internal tool with defined stages, owners and permissions. Now the live data is reliable and nobody overwrites anyone else.

But their operations director still exports a snapshot every quarter to model staffing scenarios in a spreadsheet. That’s fine. The export is a one-way copy for analysis; nobody types changes into it and pastes them back. The tool stays the single source of truth, meaning the one place everyone agrees holds the correct, current data.

Where this goes wrong is when the export becomes the new working copy. Reporting Without Exports: Getting Dashboards from Your New System covers how to stop the routine exports creeping back in, while keeping the occasional deep-dive.

Where to start this week

1. Take your spreadsheet inventory (or start one using Who Owns This Sheet? Mapping the Shadow Processes in Your Company) and tag each file as “analysis” or “process”.
2. Run the decision table on any sheet you’re unsure about. Be honest about the number of editors.
3. Give yourself permission to keep the analysis sheets. Remove them from your migration list so you can focus on the ones that matter.
4. Check one prototype sheet. If a pilot you started in a spreadsheet has been running for more than six months, ask whether it’s time to graduate it.