Workbook protection in Excel - Real Business Scenario

Scenario Mode

👤 Your Role: You are an office administrator managing a shared Excel workbook.

📋 Request: Your manager wants you to protect the workbook so that no one can accidentally delete or move sheets, but users can still enter data in the allowed cells.

📊 Data: You have an Excel workbook with three sheets: 'Sales', 'Inventory', and 'Summary'. Each sheet has data tables where users enter daily updates.

🎯 Deliverable: A protected Excel workbook where sheets cannot be deleted or moved, but users can edit unlocked cells.

Sample Data

Sheet	Cell	Content
Sales	A1	Product
Sales	B1	Units Sold
Sales	A2	Apples
Sales	B2	100
Inventory	A1	Product
Inventory	B1	Stock
Inventory	A2	Apples
Inventory	B2	500
Summary	A1	Total Sales
Summary	B1	=SUM(Sales!B2:B100)

1

Step 1: Unlock the cells where users can enter data on each sheet.

Select cells with data entry (e.g., Sales!B2:B100), right-click, Format Cells > Protection tab, uncheck 'Locked'. Repeat for Inventory and other sheets.

Expected Result

Users can edit these unlocked cells after sheet protection is applied.

2

Step 2: Protect each worksheet to prevent editing locked cells.

Go to Review tab > Protect Sheet. Check 'Protect worksheet and contents of locked cells'. Also ensure 'Delete rows' and 'Delete columns' are unchecked to prevent deletion. Enter a password if desired.

Expected Result

Users cannot edit locked cells but can edit unlocked cells.

3

Step 3: Protect the workbook structure to prevent adding, deleting, renaming, or moving sheets.

Go to Review tab > Protect Workbook. Check 'Structure'. Enter a password if desired.

Expected Result

Users cannot add, delete, rename, or move sheets.

4

Step 4: Save the workbook with protection enabled.

File > Save As > Save the workbook.

Expected Result

Workbook is saved with sheet and structure protection active.

Final Result

Workbook Protection Status:

+----------------+-----------------------------------------+
| Sheet Name     | Protection Status                       |
+----------------+-----------------------------------------+
| Sales          | Sheet protected, unlocked cells editable |
| Inventory      | Sheet protected, unlocked cells editable |
| Summary        | Sheet protected, unlocked cells editable |
+----------------+-----------------------------------------+

Workbook structure: Protected (no sheet add/delete/move)

User experience: Can enter data only in unlocked cells, cannot delete or move sheets.

✓Unlocked cells allow user data entry even when sheet is protected.

✓Sheet protection prevents accidental changes to formulas and headers.

✓Workbook protection stops users from deleting or moving sheets.

✓Using passwords adds security but remember to keep them safe.

Bonus Challenge

Set up the workbook so that users can only edit certain ranges on each sheet without unlocking all data entry cells manually.

Show Hint

Use the 'Allow Users to Edit Ranges' feature under the Review tab before protecting the sheet.