Getting more from your spreadsheets

If your business or charity runs on spreadsheets, you're in very good company. Lots of small organisations do, and for good reasons: everyone already has them, most people know their way around them, and you can change them yourself without waiting for anyone.

The trouble usually starts when a spreadsheet that began as something simple has quietly grown into something everything depends on. When that happens, it's tempting to think the answer is new software. Sometimes it is. Often, though, the quicker and cheaper fix is to tidy up what you already have. This guide will help you work out which, and give you a few improvements you can make yourself this week.

Signs a spreadsheet is struggling

None of these mean anyone has done anything wrong. They're simply what happens when a useful spreadsheet gets used for years by busy people.

• Only one person really understands how it works.
• There are notes like "don't touch this column".
• Someone copies and pastes the same things into it every week or month.
• There are several versions, emailed around, and nobody's sure which is the latest.
• A total doesn't quite match another total, and nobody knows why.

If some of those sound familiar, it's worth knowing how common they are. Raymond Panko, who has studied spreadsheets for many years, brought together studies that inspected 85 working spreadsheets in detail, and errors turned up in 94% of them. Most cells are fine; it's the sheer number of them that makes a slip somewhere likely. It's not a reason to worry; it's a reason to build in a few simple checks.

It happens to large organisations too. In October 2020, Public Health England left 15,841 positive COVID-19 cases out of its daily figures because results were being loaded into an older Excel format that stops at 65,536 rows. The lesson for the rest of us is simple: think about how much data will flow through a spreadsheet, and add a check that the totals add up.

Five improvements you can make yourself

The ICAEW, the professional body for chartered accountants, publishes a short code for building reliable spreadsheets. It was written for financial models, but many of its principles work for any spreadsheet, and these five are a good place to start.

1. Keep the things you type in one place

Prices, rates, targets, dates: anything you might need to change should live on its own clearly labelled tab, not be typed into formulas. Then a price rise is one change, not a hunt through every sheet.

2. Keep each list in one tidy block

One header row, one row per item, no blank rows or merged cells in the middle. Excel's "Format as Table" does this for you, and formulas and filters work far better on a tidy list.

3. Use drop-down lists for things that should match

If people type "Paid", "paid" and "PAID", the totals won't add up. A drop-down list (Data validation, in both Excel and Google Sheets) means everyone picks the same words.

4. Add a check

Wherever two numbers should agree, such as the total of a list and the figure on a summary page, put a cell that compares them and turns red if they don't. It's the cheapest insurance there is.

5. Add a "read me" tab

A few lines saying what the spreadsheet is for, what to update and when, and who to ask. The ICAEW code recommends keeping this inside the workbook rather than in an email, so it's there when the next person needs it.

Keep one copy, not ten

Emailing spreadsheets back and forth is where most version muddles start. If the file lives in a shared place your team already uses, such as OneDrive, SharePoint or Google Drive, everyone works on the same copy. Excel in Microsoft 365 lets several people work in a file at once when it's stored in OneDrive or SharePoint, and Google Sheets does the same.

When it might be time for something else

Spreadsheets are good at a lot, but not everything. It's worth looking at other options if:

• several people are changing the same records at once and you need to stop them overwriting each other, or control who can see what;
• you need a full record of every change, for example for a regulator;
• something needs to happen automatically, overnight or with nobody there;
• a system you already pay for does the job, such as your accounts or booking software. It's always worth checking the settings first;
• a well-made app or piece of software does exactly this, as there are good ones for quoting and invoicing, bookings and rotas.

If you do look at new software, plan for more than the subscription. In a government survey of 2,000 small and mid-size businesses, nearly a third said software was too expensive, and those that went ahead often found that getting set up "took longer and required more resources than anticipated". Time to move your information across and help everyone get used to it is part of the cost.

Excel or Google Sheets?

Whichever you already use. Both are capable, both let several people work together, and the best choice is nearly always the one your team already knows. Excel has more built-in tools for pulling in data from other systems; Google Sheets is a little easier to use on a phone. Neither is the wrong answer.

Common questions

Our spreadsheet has grown into something enormous. Can it be rescued?

Almost always. The gentlest way is to rebuild it cleanly alongside the old one, move the data across, run both for a month, and retire the old one once everyone trusts the new one. Nobody has to stop work while it happens.

Should we be using a "proper system" instead?

Maybe. If one of the signs in "When it might be time for something else" fits, it is worth looking. If not, a well-organised spreadsheet your team already understands is a perfectly proper way to run things.

What about Power Query and macros?

Both are part of Excel and can save a lot of time: Power Query pulls in and tidies data from other systems and, once set up, refreshes it in one click, and a macro can turn a ten-step routine into a button. They are worth learning, or asking someone to set up and show you.

How big can a spreadsheet get?

An Excel worksheet holds 1,048,576 rows and 16,384 columns, and a Google Sheets file holds up to 20 million cells. Very few working spreadsheets come close. If yours does, the data probably wants to live somewhere else, with the spreadsheet doing the reporting.

Sources

1. What we don’t know about spreadsheet errors today, Raymond Panko, Proceedings of EuSpRIG, 2015.
2. PHE statement on delayed reporting of COVID-19 cases, Public Health England, GOV.UK, October 2020.
3. Excel spreadsheet blunder blamed after England under-reports 16,000 COVID-19 cases, The Register, October 2020.
4. Financial Modelling Code, ICAEW, August 2024.
5. Collaborate on Excel workbooks at the same time with co-authoring, Microsoft Support.
6. Excel specifications and limits, Microsoft Support.
7. Files you can store in Google Drive, Google Drive Help.
8. Understanding technology adoption among UK SMEs, Ipsos for the Department for Business and Trade, July 2025.