Tutorials›Excel›Protect Sheet & Workbook
🟢 Free Demo
Excel TutorialTopic 30 of 31

Protect Sheet & Workbook

Lock cells, protect formulas from accidental edits, and password-protect sheets and workbooks.

✅ What You Will Learn

›How to lock specific cells and leave others editable
›How to protect a sheet to prevent formula tampering
›How to password-protect a workbook from opening
›How to protect sheet structure so sheets cannot be deleted or renamed
›How to allow specific users to edit specific ranges

When you share an Excel file with colleagues, there is always a risk that someone accidentally overwrites a formula, moves a chart, or deletes a row they did not mean to. Sheet protection prevents this by locking cells so they cannot be edited without a password.

By default, all cells are "locked" — but locking only takes effect when you protect the sheet. The workflow is: (1) unlock the cells you want to allow editing (input cells), (2) leave everything else locked (formulas, headers, charts), (3) protect the sheet. Users can then only edit the unlocked input cells.

Workbook protection prevents structural changes — adding, deleting, or renaming sheets. File-level password encryption prevents the file from being opened without the password.

Examples

Example 1Lock formulas, unlock inputs — the standard workflow
Step 1: Unlock input cells (the ones users should be able to change)
  Select input cells (e.g. B2:B10)
  Ctrl+1 → Protection tab → uncheck "Locked" → OK

Step 2: Protect the sheet
  Review tab → Protect Sheet
  Set a password (optional but recommended)
  Leave checked: "Select locked cells", "Select unlocked cells"
  Click OK

Result: Users can only type in B2:B10.
All other cells (formulas, headers) are protected.

To unprotect: Review → Unprotect Sheet → enter password
💡

Remember: ALL cells are locked by default. Protect Sheet enforces the locked/unlocked state. Without protecting the sheet, the lock property does nothing.

Example 2Allow specific users to edit specific ranges with passwords
Review tab → Allow Edit Ranges → New
  Title: "Input Area"
  Refers to cells: $B$2:$B$20
  Range password: (set a different password from sheet password)

Add another range with a different password for a different user group.
Then protect the sheet.

Result: Different users can edit different ranges using their own passwords,
without needing the sheet unprotect password.
💡

This feature is useful for forms where department A fills in their data and department B fills in theirs — each range has its own password, preventing cross-contamination.

📌 Key Points to Remember

  • ✓Unlock → Protect workflow: unlock input cells first, then protect the sheet. Never the reverse.
  • ✓Sheet protection password is case-sensitive — write it down. Lost passwords are not recoverable by Microsoft.
  • ✓Protection prevents accidental edits but is NOT a security feature — determined users can remove basic sheet protection with tools
  • ✓Use File → Info → Protect Workbook → Encrypt with Password for real security (file cannot be opened without password)
  • ✓Hiding a sheet (right-click tab → Hide) combined with "Protect Workbook Structure" prevents users from unhiding it

🏢 Real-World Application

Finance teams distribute Excel budget templates where managers can only edit their department input cells — the consolidation formulas, formatting, and summary calculations are all protected. HR distributes appraisal forms where each manager sees only their team section (other sections are hidden and the workbook structure is protected). This ensures the master model stays intact while allowing controlled data entry.

⚠️ Common Mistakes to Avoid

WRONGProtecting the sheet before unlocking input cells — everything is locked
FIXUnlock input cells BEFORE protecting. Select input cells → Ctrl+1 → Protection → uncheck Locked → OK. Then Review → Protect Sheet.
WRONGForgetting the sheet protection password
FIXAlways document passwords in a secure location (not on the file itself). For internal tools without security requirements, use no password — protection still prevents accidental edits.
WRONGRelying on sheet protection for sensitive data
FIXSheet protection prevents accidental edits but is not encryption. For genuinely sensitive data, use File → Encrypt with Password, which prevents the file from being opened without the password.
✏️Test Yourself

You want users to edit cells B2:B10 but not touch formulas in C2:C10. What is the correct order of steps?

❓ Frequently Asked Questions

Can I protect a sheet without a password?

Yes — leave the password field blank when protecting. The sheet is still protected against accidental edits but anyone can unprotect it with Review → Unprotect Sheet (no password required). This is appropriate for most internal shared files.

How do I hide formulas so users cannot see them in the formula bar?

Select the formula cells → Ctrl+1 → Protection tab → check "Hidden". Then protect the sheet. Hidden cells show no formula in the formula bar when selected — the result is visible but the formula is not.

How do I let a macro run on a protected sheet?

In the VBA code, use ActiveSheet.Unprotect "password" before the code runs, then ActiveSheet.Protect "password" after. Or check "Protect worksheet and contents of locked cells" in Protect Sheet but leave the macro permission option unchecked.

✏️ Practice Exercise

Build a budget submission template: (1) Input cells for each department (B2:B10 — unlocked), (2) Auto-calculated totals and variances in column C (locked formulas), (3) Headers and formatting cells (locked). Protect the sheet with a password. Test that users can edit B2:B10 but cannot modify column C. Then set up Allow Edit Ranges so that department managers can only edit their own rows using separate range passwords.

← PreviousSorting & Filtering DataNext →Flash Fill & AutoFill
🎓 Level Up Faster

Learn Excel with Live Trainer Guidance

These tutorials give you the foundations. Our live Excel course at EVIKA Academy, Noida teaches you to build real dashboards on actual business data — with a trainer who uses Excel professionally every day.