5.3Collaboration, protection & sharing
The essentials in 30 seconds
- Cloud co-authoring: saved to OneDrive or SharePoint, the workbook is co-authored in real time via a link (view or edit) — no more diverging copies by email.
- Targeted protection: you unlock the entry cells first, then protect the sheet — the formulas stay locked, entry stays possible.
- Version safety net: version history lets you go back; the open password encrypts, the modify password enforces read-only.
Running mini-case — Wassim shares without breaking
Wassim, an accountant in Algiers, shares his expense-entry model with three colleagues: the first month, everyone returns their copy by email and the formulas come back broken. He changes method: workbook saved to OneDrive and shared for editing, entry cells unlocked then sheet protected, a single link instead of attachments. When a colleague overrides a formula anyway, version history restores the previous state in two clicks. Continuation of case 5.2 — Wassim protects what he has built.
Continuation of case 5.2 — Wassim moves from the solo workbook to the shared and protected workbook.
1. Co-authoring in the cloud
Saved to OneDrive or SharePoint, a workbook is co-authored by several people in real time: everyone sees the others' cursors, changes merge. You share via a link (view or edit). Wassim has banned the « file_v3_final_REAL.xlsx » sent by email: a single link, chosen rights, zero diverging copies.
The sharing rule: edit link for contributors, view link for readers — and you check who has which link before sending. Sensitive data (salaries, bank details) is shared neither widely nor via an open link: access restricted to the people concerned only.
2. Protect without locking everything
You protect to avoid breakage: lock the formulas while allowing entry in the entry cells. Method: first unlock the cells to fill (Format ▸ Protection), then Protect Sheet. Wassim applies the order to his expense model: his colleagues enter their amounts but cannot alter the calculations. You can also protect the workbook structure (prevent deleting sheets).
Key point: by default all cells are locked, but the lock only acts once the sheet is protected — protecting without having unlocked the entries blocks everything. And sheet protection prevents errors, it's not encryption: for confidentiality, open password and restricted cloud access.
3. History & recovery
In the cloud, version history lets you revert to an earlier state — a safety net against a bad move. When one of Wassim's colleagues overrides an area despite the protection, two clicks restore the previous version. You check the history regularly on sensitive shared files.
Also useful: the open password (encrypts the file) vs the modify password (allows read-only without the password). You choose the level by sensitivity — and you keep the passwords somewhere safe, because an encrypted workbook lost without its password is unrecoverable.
🔎 Concrete example — A model others fill without breaking it
Wassim's expense-entry table, shared with his team:
1. Unlock ONLY the cells to fill (Format > Protection > uncheck Locked)
2. Review > Protect Sheet (the formulas stay locked)
3. Share ONE link (edit for the team, view for the others)
-> the team enters its data, but can't alter the calculationsReading: by default everything is locked; you unlock the entry areas BEFORE protecting.
Lesson: targeted protection allows collaboration without breakage.
Section vocabulary
- Co-authoring
- Simultaneous editing by several people via OneDrive or SharePoint, with merging of changes.
Simple example: Wassim and his colleagues enter the expenses at the same time on the same link.
What it is not: copies sent by email that diverge.
Reflex: am I sharing a link or copies?
- Protect Sheet
- Prevent modification of locked cells, once protection is enabled.
Simple example: Wassim protects his sheet: the formulas are untouchable, the entries stay free.
What it is not: an encryption that guarantees confidentiality.
Reflex: what must stay modifiable?
- Locked / unlocked cell
- The attribute that only acts once the sheet is protected.
Simple example: Wassim unlocks the amounts to enter before protecting.
What it is not: a permission that acts on its own without protection.
Reflex: did I unlock BEFORE protecting?
- Version history
- Reverting to an earlier state of the workbook in the cloud.
Simple example: Wassim restores the version from before the bad move in two clicks.
What it is not: a manual backup by file copy.
Reflex: can I go back if it breaks?
- Open / modify password
- Encrypt the file, or enforce read-only without the password.
Simple example: Wassim encrypts the salary workbook and sets the rest to read-only.
What it is not: simple sheet protection.
Reflex: what level does the sensitivity require?
In practice — Protect an entry model
- Save a workbook to OneDrive and share it for editing.
- Unlock the entry cells, then Protect the sheet.
- Check that you can enter data but not modify the formulas.
- Open the version history and restore a previous version.
Ready-to-say script — announce the sharing rules to the team
« No more copies by email: our entry model is on OneDrive, shared by link — edit for us, view for the others. I unlocked the cells to fill then protected the sheet: you enter your amounts, the formulas are untouchable. If a bad move slips through anyway, version history restores us in two clicks. And sensitive data stays on restricted access. »
Say it to the team before the first sharing, to set the rules once and for all.
Copy-paste template — protected sharing checklist
PROTECTED SHARING CHECKLIST — file: ___
CLOUD: saved to OneDrive/SharePoint? ___ · single link (no attachments)? ___
RIGHTS: edit for ___ · view for ___ · sensitive on restricted access? ___
PROTECTION: entries unlocked BEFORE? ___ · sheet protected? ___ · structure protected? ___
NET: version history checked? ___ · passwords kept somewhere safe? ___Solo / small budget
Wassim alone: OneDrive + sheet protection is enough — a view link for management, history as a net.
SME / small team
An edit link for the team, a view link for the external accountant — each with their right.
SME in Algiers
Wassim sets up five minutes of rules in Algiers: who enters what, which cells are free, where to report a problem.
Team getting started
Pair up: one unlocks the entries, the other tests the protection before sending the link to everyone.
✓ Best practices
- Co-author in the cloud rather than by email.
- Unlock the entries BEFORE protecting the sheet.
- Rely on the version history.
✗ Common pitfalls
- Protecting without having unlocked the entry cells → everything is blocked.
- Sending copies by email (diverging versions).
- Confusing the open password (encryption) and the modify one.
Key takeaways
- OneDrive/SharePoint = real-time co-authoring + history.
- Protect: lock the formulas, allow entry in the inputs.
- Unlock the entry cells BEFORE protecting the sheet.
- Password: open (encrypts) ≠ modify (read-only).
48h challenge — apply tomorrow
Put your real model on OneDrive: unlock the entries, protect the sheet, share via a link with chosen rights — then restore a previous version via the history to prove the net.
- I share by cloud link, not by email copies.
- My entries are unlocked before protection.
- I know how to restore a previous version.
Frequently asked questions
My protected sheet blocks all entry, even the input cells?
By default, all cells are « locked »; the lock only acts once the sheet is protected. First unlock the cells to fill (Format ▸ Protection), THEN protect the sheet.
Is sheet protection real security?
It prevents errors and accidental changes, but remains bypassable. For confidentiality, use an open password (which encrypts the file) and manage access via cloud sharing.
Before protecting a sheet to allow entry in certain cells, what must you do?