Protect Workbooks: Soft Restrictions, Worksheet Protection, and Cell Ranges

When collaborating on a workbook, you usually need a layered approach to access control. At the lightest level, you signal that a file is "done" or open it in read-only mode by default. At the strongest level, you encrypt the file or carve out per-range permissions inside a protected sheet. The MO-211 exam expects you to know which tool to reach for in each scenario—from a simple "discourage editing" prompt to fine-grained, cell-level locking.

Part 1: Soft Restrictions (Mark as Final, Read-Only, Encryption)

The first layer of restriction lives in File > Info > Protect Workbook. These options range from a friendly "please don't edit" sign to full file encryption.

Mark as Final

This is the "softest" form of restriction—essentially a digital "Do Not Disturb" sign. It communicates to others that this is the completed version of the document and discourages further editing.

1. Go to File > Info.
2. Click Protect Workbook.
3. Select Mark as Final.
4. Excel saves the file and displays: "This workbook has been marked as final to discourage editing."

What happens next?

• The file becomes Read-Only.
• The Ribbon is hidden/disabled.
• A yellow bar appears at the top: "An author has marked this workbook as final to discourage editing."
• Anyone can bypass this by clicking Edit Anyway. Mark as Final is a communication feature, not a security feature.

Always Open Read-Only (Read-Only Recommended)

If you want to prevent accidental changes (such as someone hitting Ctrl + S after testing some numbers), you can set the file to open in Read-Only mode by default.

1. Go to File > Info > Protect Workbook.
2. Select Always Open Read-Only.
3. Result: Every time someone opens the file, Excel asks: "The author would like you to open this as read-only unless you need to make changes. Open as read-only?"

Encrypt with Password

If you want to ensure that only people with the "key" can even see the data, you encrypt the entire file.

• Path: File > Info > Protect Workbook > Encrypt with Password.
• Type and confirm a password. Anyone opening the file will be prompted before they can view any contents.

Restrict Access (Information Rights Management)

In a corporate environment with Information Rights Management (IRM) configured, you can restrict editing to specific people:

• Path: File > Info > Protect Workbook > Restrict Access.
• Assign per-user permissions such as Read (view only) or Change (view, edit, and save, but not print).
• IRM requires your organization to have a Rights Management service available; it is uncommon on the exam but worth recognizing on the menu.

Summary of Soft Restrictions

Part 2: The Two-Step Protection Workflow

Locking a cell does nothing on its own. The lock only becomes active when sheet protection is enabled. This is the most common point of confusion on the exam.

Step 1: Unlock the Input Cells

By default, every cell on a new worksheet has its Locked property set to True. To allow users to type into specific cells, you must first unlock those cells.

1. Select the range that users should be allowed to edit (e.g., B2:B20).
2. Press Ctrl + 1 to open the Format Cells dialog.
3. Switch to the Protection tab.
4. Uncheck the Locked checkbox.
5. Click OK.

Step 2: Turn On Sheet Protection

1. On the Review tab, click Protect Sheet (in the Protect group).
2. (Optional) Enter a Password to unprotect sheet.
3. Choose which actions users may still perform (see Part 3).
4. Click OK, then re-enter the password to confirm.

Part 3: The "Allow All Users of This Worksheet To" Checklist

When you click Protect Sheet, Excel presents a long checklist of permissions. By default, only the first two items are ticked. Understanding each option is essential for building a usable protected template.

Common Permissions

• Select locked cells — Allows clicking on read-only cells. Uncheck this if you want the cursor to skip over formula cells entirely.
• Select unlocked cells — Almost always left checked; otherwise users can't enter data.
• Format cells — Lets users change fonts, fill colors, borders, and number formats on any cell.
• Format columns / Format rows — Controls resizing and hiding of columns or rows.
• Insert columns / Insert rows / Insert hyperlinks — Granular permission to add structural elements.
• Delete columns / Delete rows — Granular delete permissions.
• Sort — Allows sorting, but only on ranges where every cell is unlocked.
• Use AutoFilter — Enables existing filter dropdowns. Note: it does not let users create new filters.
• Use PivotTable & PivotChart — Allows refreshing and rearranging existing pivots.
• Edit objects — Controls whether shapes, charts, and form controls can be modified.
• Edit scenarios — Affects Scenario Manager entries.

Part 4: Hiding Formulas

Sometimes you want users to see the result of a formula but not the formula itself (e.g., a proprietary pricing model). The Hidden attribute solves this.

How to Hide Formulas

1. Select the cells containing the formulas you want to hide.
2. Press Ctrl + 1 → Protection tab.
3. Check Hidden (leave Locked checked as well).
4. Click OK.
5. Apply Review > Protect Sheet as normal.

The Result

• The cell still displays its calculated value in the grid.
• The formula bar shows nothing when that cell is selected.
• Ctrl + ~ (Show Formulas) will not reveal the hidden formula either.

Part 5: Allow Edit Ranges (Per-Range Permissions)

The Allow Edit Ranges dialog (formerly "Allow Users to Edit Ranges") lets you carve out specific zones of a protected sheet that individual users or groups can edit—each with its own password or Windows-account permission. This is the closest thing Excel has to row-level security.

Opening the Dialog

• On the Review tab, click Allow Edit Ranges (visible only when the sheet is not currently protected).

Defining a New Range

1. Click New... in the Allow Edit Ranges dialog.
2. Fill out the New Range form:
   • Title: A friendly name for the range (e.g., RegionalSales_North).
   • Refers to cells: Type or select the target range (e.g., =$C$5:$E$50).
   • Range password: (Optional) A password specific to this range. Anyone who knows it can edit, regardless of Windows identity.
3. Click Permissions... to choose users or groups (domain-joined PCs only).
   • Click Add... to launch the standard Windows Select Users or Groups picker.
   • For each user or group, you can tick Allow under the Permit access to range without a password column. Those users edit the range with no prompt.
4. Click OK to return to the main dialog.
5. Click Protect Sheet... from inside the Allow Edit Ranges dialog to finalize—this ensures the per-range permissions activate.

Behavior at Run Time

• When a permitted user types into a range, no prompt appears.
• When a non-permitted user types into the range, Excel asks for the range password.
• Cells outside any defined range still follow the standard locked/unlocked + sheet-protection rules.

Part 6: Removing Protection

To edit the structural settings of a protected sheet—or to hand the workbook off without restrictions—you must remove protection.

Steps

1. On the Review tab, click Unprotect Sheet.
2. If a password was set, type it and click OK.
3. The button label flips back to Protect Sheet, confirming the sheet is now editable.

What If You Forgot the Password?

• Excel does not offer a built-in password recovery tool.
• For lost passwords on critical files, your only sanctioned options are restoring an earlier version (OneDrive/SharePoint version history) or rebuilding the sheet.

Expert Tips for the Exam

• Read the prompt carefully. Tasks often phrase the requirement as: "Allow users to sort the data in the table but not delete rows." This maps directly to checkboxes in the Protect Sheet dialog.
• Locked is the default. If a question says "lock cells A1:A10," you usually need only verify Locked is on (it already is) and apply Protect Sheet.
• Ranges before protection. Always define Allow Edit Ranges before you click Protect Sheet—the button is grayed out once protection is on.
• Different passwords are fine. The sheet password and individual range passwords are independent. The exam may explicitly require different ones.

Links:

• Microsoft Support - Protect a worksheet
• Microsoft Support - Lock or unlock specific areas of a protected worksheet
• Microsoft Support - Lock cells to protect them
• Microsoft Support - Display or hide formulas