how to

Protect Sheets but Let Colleagues Edit the Right Cells

· 2 min read spreadsheetsprotectioncollaborationexcel

Sheet protection is backwards to most people: by default every cell is locked, and protection enforces that. The workflow is therefore unlock what users may touch first, then switch protection on. Done properly, colleagues can enter data anywhere in the input area yet cannot break a single formula.

What you need

  • A workbook shared with other users
  • A clear mental line between input cells and calculated cells
  • Excel or Google Sheets

Step by step

  1. Map the input area. Select every cell users should edit, hold Ctrl for scattered selections, then open Format Cells (Ctrl+1) > Protection tab and clear Locked. This step does nothing visible until protection is enabled.
  2. Leave everything else locked at its default. Headers, formulas, labels, and formatting stay untouchable.
  3. Optionally allow specific actions despite protection: in Excel's Review > Protect Sheet dialog, tick "Select unlocked cells" and, if users need to sort or filter tables, also those checkboxes. Unticked actions are blocked workbook-wide.
  4. Set a password only if enforcement matters. Without one, any user can unprotect via Review > Unprotect Sheet, which is fine for preventing accidents but not malice.
  5. In Google Sheets, use Data > Protected sheets and ranges instead. Add a warning on the whole sheet, then describe the exact input range as fully editable; warnings are softer than Excel locks and suit casual collaborators.
  6. Test as a second account or after opening an incognito copy: confirm typing into an input cell works, typing into a formula cell is rejected with the protection message, and sorting behaves.
  7. Hide helper columns before protecting if they contain intermediate calculations; protection plus hidden columns keeps the interface clean without removing the logic.
  8. Document the password location outside the file itself, e.g. your team vault. Passwords embedded in a note inside the same workbook defeat the purpose.

Common mistakes

  • Enabling protection first and wondering why nothing can be edited, because no cells were unlocked beforehand
  • Leaving "Select locked cells" off, so users cannot even click a formula cell to inspect it
  • Forgetting to allow sort/filter in the dialog, then fielding complaints that protected tables are unusable

Related guides

Protection complements [[dropdown-data-validation-lists]]: validation constrains values while protection constrains structure. If your locked sheet feeds lookups, verify them against [[index-match-vs-vlookup]] before locking, since restructuring later requires unprotecting everything.

← All guides