Excel Protecting Workbooks and Sheets
Excel's protection features prevent accidental changes to your formulas, layouts, and sensitive data. You can lock the entire workbook with a password, restrict editing to specific cells on a sheet, or hide formulas from view. These controls are essential when sharing files with colleagues or clients.
Three Levels of Protection
| Protection Level | What It Protects |
|---|---|
| Worksheet protection | Prevents changes to cells, rows, columns, and structure on one sheet |
| Workbook structure protection | Prevents adding, deleting, moving, or renaming sheets |
| File-level password | Requires a password to open or modify the entire file |
Protecting a Worksheet
Step 1 — Decide Which Cells Stay Editable
By default, all cells in Excel are marked as "Locked." When you protect the sheet, locked cells become read-only. To allow users to edit specific cells (like input fields), unlock those cells first.
Select the cells you want users to edit freely. Press Ctrl + 1 to open Format Cells. Click the Protection tab. Uncheck Locked. Click OK. These cells remain editable after protection is applied.
Step 2 — Protect the Sheet
Go to Review → Protect Sheet. A dialog appears with a list of actions users can still perform even after protection. By default, users can select cells but cannot edit them. Check additional permissions if needed — for example, allow sorting or filtering.
Optionally enter a password. Click OK. If you added a password, confirm it. The sheet is now protected.
Protection Diagram
WORKSHEET AFTER PROTECTION: +-------------+-------------+-------------+ | LOCKED | LOCKED | LOCKED | ← formula cells, headings | (read-only)| (read-only)| (read-only)| +-------------+-------------+-------------+ | UNLOCKED | LOCKED | LOCKED | ← input cell — users can type here | (editable) | (read-only)| (read-only)| +-------------+-------------+-------------+ Users see data but cannot edit locked cells. Clicking a locked cell shows: "The cell or chart you are trying to change is protected."
What You Can Allow Users to Do on a Protected Sheet
| Permission | What It Allows |
|---|---|
| Select locked cells | Click on protected cells (default: on) |
| Select unlocked cells | Click on editable cells (default: on) |
| Format cells | Change fonts and colours |
| Insert rows / columns | Add new rows or columns |
| Sort | Use Data → Sort on unprotected ranges |
| Use AutoFilter | Filter data using dropdowns |
| Edit objects | Move or resize charts and shapes |
Unprotecting a Sheet
Go to Review → Unprotect Sheet. If a password was set, enter it. The sheet returns to fully editable mode.
Protecting the Workbook Structure
Workbook structure protection stops users from inserting, deleting, moving, or renaming worksheet tabs. It does not prevent editing cells — use sheet protection for that.
Go to Review → Protect Workbook. Check Structure. Optionally enter a password. Click OK. The worksheet tab options (right-click menu) become greyed out for protected actions.
Hiding Formulas
Even on a protected sheet, users can see your formulas by clicking a cell and reading the formula bar. To hide formulas from view:
Select the cells containing formulas you want to hide. Press Ctrl + 1. Click the Protection tab. Check Hidden. Click OK. Now protect the sheet (Review → Protect Sheet). The formula bar shows blank for those cells — users see the result but not the formula.
Hidden Formula Diagram
WITHOUT hidden formula (unprotected): Cell D5 selected → Formula bar shows: =B5*C5*0.18 WITH hidden formula (protected + hidden): Cell D5 selected → Formula bar shows: (blank) Cell D5 displays: 1620 ← value is still visible, formula is not
Protecting the File with a Password
To require a password just to open the file, go to File → Info → Protect Workbook → Encrypt with Password. Enter a password and confirm it. Click OK. Save the file. Anyone who tries to open the file must enter the password.
To require a password before someone can make changes, go to File → Save As → Tools → General Options. Enter a Password to modify. Without this password, the file opens in read-only mode.
Allowing Ranges with Different Passwords
You can give different users access to different parts of a protected sheet using range passwords.
Go to Review → Allow Edit Ranges. Click New. Enter a title, select the range, and set a password for that range. Click OK. Protect the sheet. Users who know the range password can edit that specific range without unprotecting the whole sheet.
Protection Best Practices
| Situation | Recommended Protection |
|---|---|
| Share a form where users fill in inputs only | Lock all cells, unlock input cells, protect sheet |
| Prevent accidental formula deletion | Lock formula cells, protect sheet (no password needed) |
| Keep sheet structure fixed | Protect workbook structure |
| Share confidential data | File-level open password |
| Collaborate but block edits to summaries | Allow edit ranges with separate passwords per department |
Excel protection is a safeguard, not an encryption system. A determined user with third-party tools can bypass sheet protection. For truly sensitive data, use proper file encryption, access control at the folder or server level, or dedicated data management software. Within a normal working team, Excel protection is reliable for preventing accidental edits and guiding users to the right input areas.