Protecting fields in shared event spreadsheets
Someone typed over a total in the shared budget sheet, and it took two days to notice.
The short answer
Protect a shared event spreadsheet by separating three kinds of cell: inputs that people should change, formulas that must not change, and approved figures that change only through a decision.
Lock the second and third kinds, name who holds the keys, and keep one unprotected copy in the owner's hands.
Sort every column into three kinds
- Input: a value someone legitimately updates, such as confirmed headcount or quote received.
- Calculated: a formula, such as total cost or remaining balance.
- Approved: a figure signed off by a decision, such as the agreed budget ceiling.
What to lock, and for whom
Lock calculated and approved cells for everyone except the owner and a named deputy. Leave input cells open to the people who supply that input.
If your tool allows ranges per person, give each contributor only their own range. If it does not, give each contributor their own input sheet and have the owner combine them.
Make locked cells obvious
Use a consistent colour for input cells and a different one for locked cells, with a key at the top. Add a short note beside any approved figure saying who approved it and the date.
Protection that people cannot see tends to be switched off by someone who is in a hurry.
Protection is not the same as control
A protected cell can still be changed by anyone holding unlock rights. Keep those rights narrow and record any unlock in the version history.
Protection also does not stop two people working on separate copies. The single source of truth rule still applies.
Check before you share
- Test as a contributor: try to edit a formula and an approved cell.
- Check hidden sheets, hidden columns and external links.
- Confirm that totals still calculate when an input is left blank or entered as text.
- Save a baseline version before sharing, so you can compare later.
Worked example · Fictional example
A budget sheet used by five people
Fictional organisation and figures, for illustration only.
A fictional logistics forum has a budget sheet edited by the treasurer, the programme lead and three committee members for a one-day event of about 200 delegates. A committee member adjusts a catering line and accidentally overwrites the subtotal formula.
After the fix, the sheet has green input cells for catering counts and quote amounts, grey locked cells for subtotals and variance, and a blue locked cell for the approved ceiling with a note showing the committee minute reference. Only the treasurer and a deputy can open the grey and blue cells.
Use this yourself
Field protection checklist
Work through this list for each shared spreadsheet before it is circulated.
- Every column is labelled input, calculated or approved.
- Calculated and approved cells are locked.
- Named owner and deputy can unlock; everyone else cannot.
- Each contributor can edit only their own input range or sheet.
- Colour key and approval notes are visible at the top.
- Hidden sheets, hidden columns and links reviewed.
- Tested as a contributor, including blank and text entries.
- Baseline version saved with version number and date.
- Any later unlock is recorded in the change log.
Open the tool: Conference materials and translation-version checker
Handle it in-house, or bring in help?
Your team can usually handle this when
- One person owns the sheet and the contributors are few and known.
- The spreadsheet tool supports range protection and the owner has tested it.
Outside planning help earns its fee when
- Several organisations edit the same budget or supplier tracker and the totals feed decisions.
- The sheet has already been overwritten once and the committee has lost trust in the figures.
Want the key sheets set up and kept clean?
A conference project lead can set up the shared sheets with your owners, agree who may change what, and check the figures before each committee meeting. Approval of any figure stays with your committee.
Questions organisers ask
Should we password-protect the whole file?
A file password limits who can open it but does not stop contributors overwriting cells. Protect ranges and manage who holds the keys.
What if people work offline on copies?
Treat offline copies as copies. Ask contributors to send inputs, not whole files, and let the owner merge them into the master.
Can protection hide private details?
Not reliably. If a sheet holds personal details, ask your data protection contact before sharing, and keep such data out of general working sheets.
Related resources
Content record: Draft. Written from the cited sources and checked by automated rules; not yet independently reviewed.