How do I stop people breaking our shared spreadsheet?

Why does the spreadsheet keep breaking?

Because it stopped being a spreadsheet some time ago. It started as a list. Then someone added a formula, then a lookup to another tab, then a summary the directors read every Monday. Somewhere along the way it became a system the business runs on, and nobody ever decided that it should.

The signs are familiar. One person built it and is the only one who can fix it. Everyone else has been told which columns they are allowed to type in, and that rule lives in their heads rather than in the sheet. Totals go wrong after someone sorts a column or pastes over a formula. When the builder is on holiday, the sheet waits for them to come back.

None of that is a criticism of the person who built it. A sheet people rely on enough to break is a sheet that works. It just works for one person.

What can Google Sheets do to stop it?

More than most shared sheets use. A few features that come with every Google Sheet deal with most of the damage, and you won't need a developer for any of them.

The first is protection. Google's help page on protecting sheets gives you two choices for a range or a whole sheet. You can show a warning when someone edits it, which does not block anyone but asks them to confirm. Or you can restrict who can edit it: only you, only people in your organisation if you use Google Workspace, or a list you choose. You can also protect a whole sheet and tick 'Except certain cells' to leave the input cells open.

That last option is usually the right shape. Lock the formulas, the lookups and the summary. Leave open the cells people are actually meant to fill in.

The second is the dropdown list. Free typing is where a lot of bad data starts, because "Nottingham", "Notts" and "nottingham" with a stray space are three different values to a formula. Google's guide to dropdown lists shows how to give a cell a fixed set of options, typed in or taken from a range. If you enter data in a cell that does not match an item on the list, it is rejected. You can switch that to a warning instead, but for any column a formula depends on, leave it rejecting.

The third is named ranges. Google's page on naming ranges shows a formula like =SUM(A1:B2, D4:E6) rewritten as =SUM(budget_total, quarter2). It looks cosmetic, but the next person can read the second version and guess what it does when the builder is not there to ask. One warning from the same page: delete a named range and any formula that references it stops working.

Problem	Feature	What it does	What it does not do
Someone types over a formula	Protected range	Warns, or blocks the edit entirely	Stop anyone copying the data out
The same thing typed five ways	Dropdown list	Rejects anything not on the list	Check the list itself is right
Nobody can follow the formulas	Named ranges	Puts a word where a cell reference was	Explain why the formula exists
Something broke last week	Version history	Shows earlier versions and restores them	Keep every version unless you name it

What does protection not do?

It is not security. Google says so in as many words: protection should not be used as a security measure, and people can still print, copy, paste, import and export copies of a protected spreadsheet. Protection decides who can change cells. Sharing decides who can see them. If the sheet holds personal details about customers or staff, the sharing settings are the ones that matter.

Hidden tabs catch people out too. The same page is clear that hiding a sheet is not the same as protecting it, and that anyone who can edit the spreadsheet can unhide it. Tucking the workings tab out of sight keeps it tidy. It does not keep it safe.

How do you recover when it breaks anyway?

Version history. Google's page on finding changes walks through it: click Last edit at the top right, choose an earlier version, then restore it or make a copy. You need edit access to the file to see earlier versions, so a colleague with view access cannot do this for you.

You can also right click a single cell and choose Show edit history. Useful, with a catch. Google lists changes that might not appear there, including rows and columns added or deleted, format changes and changes made by formulas. A total that went wrong because someone deleted a row will not explain itself in the cell's history.

Name a version before any big change. Google says a named version keeps your versions from being merged, and you can add up to 15 named versions per spreadsheet, so save them for moments that matter: before a restructure, or at the end of each month.

Where do people get this wrong?

The usual response to a broken sheet is an email asking everyone to be careful. Then the input cells get coloured yellow and a tab called READ ME appears. It helps for about a week. The sheet still lets anyone type anywhere, so eventually someone does, usually on a Friday afternoon.

The opposite mistake is to lock everything. If people cannot do their job in the sheet, they make a copy and work in that. Now there are two versions of the truth and nobody controls one of them.

Both mistakes cost the same thing: hours spent working out which number is right, and a summary that nobody fully trusts.

Who else needs to understand the sheet?

This is the risk nobody wrote down, because it never felt like a risk. The person who built the sheet was always there. Protection and dropdowns stop accidents. They do nothing about the fact that one person holds the only working knowledge of how it fits together.

Three things fix that, and none of them is technical.

1. A short note on the first tab: what the sheet is for, where each input comes from, which tabs feed which, and who owns it.
2. Named ranges in the formulas that matter, so the logic reads in words rather than coordinates.
3. A second person who has changed something in the sheet, with the owner watching, before they ever have to do it alone.

When should you stop using a spreadsheet?

Plenty of businesses should keep theirs. A spreadsheet is cheap, everyone can open it and it does what it says. Move on when it is doing a job it was never shaped for.

Keep the spreadsheet	Move to something else
A handful of people edit it	Lots of people edit it all day, at the same time
Your own team types the inputs	Customers or suppliers need to put data in
One summary, read weekly	Several reports built by copying tabs
Rules fit in a dropdown and a protected range	Rules depend on who the user is and what stage a record has reached
It opens quickly	It is slow, and getting slower

Size is rarely the trigger. Google's page on Drive file limits allows up to 10 million cells or 18,278 columns for a spreadsheet created in or converted to Google Sheets. If yours is anywhere near that, the cell count is the least of its problems.

The something else is often smaller than people fear. A simple web application with a form for input and a database underneath, with the same reports on top. Sometimes the sheet stays as the reporting layer and only the input moves. Sometimes a short Apps Script that imports data and checks it on a schedule is enough to stop the hand typing that caused the trouble.

When is this not the answer?

When the real problem is who can see the data rather than who can change it. That is about sharing and access, and if personal data is involved it may need proper data protection advice, not a protected range.

When the sheet is your accounting records. Accounting software, and a conversation with your accountant, will usually serve you better. If Making Tax Digital is the worry, we covered what it asks of a spreadsheet separately.

When the sheet is fine and the problem is one person who keeps overriding it. That needs a conversation, not a build.

And when nobody actually uses it. Then it does not need protecting, it needs deleting.

Where do you start?

Give it an hour this week. Open the version history and see who changes the sheet and how often. Protect the summary and formula tabs with a warning first, then switch to restricting edits once nobody complains. Put dropdown lists on the columns your formulas depend on. Write the note on the first tab.

If you have done all that and the sheet still breaks, or you suspect it quietly became a system a while ago, tell us what's stuck and we'll map the quickest fix. We will tell you straight whether it needs an afternoon of protection settings, a short script or a proper app. Time to talk yet?