Sheet protection in Excel - Real Business Scenario

Scenario Mode

👤 Your Role: You are an office administrator managing a monthly budget spreadsheet.

📋 Request: Your manager wants to prevent accidental changes to the budget totals while allowing team members to update individual expense items.

📊 Data: You have a spreadsheet with expense categories, monthly amounts, and total calculations.

🎯 Deliverable: Create a protected sheet that locks the total cells but allows editing of expense amounts.

Sample Data

Category	January	February	March	Total
Rent	1000	1000	1000	=SUM(B2:D2)
Utilities	150	130	140	=SUM(B3:D3)
Office Supplies	200	180	220	=SUM(B4:D4)
Travel	300	400	350	=SUM(B5:D5)
Marketing	500	600	550	=SUM(B6:D6)
Total Expenses	=SUM(B2:B6)	=SUM(C2:C6)	=SUM(D2:D6)	=SUM(E2:E6)

1

Step 1: Unlock the cells where team members can enter data (January to March columns for each category).

Select cells B2:D6, right-click, choose 'Format Cells', go to 'Protection' tab, uncheck 'Locked', then click OK.

Expected Result

Cells B2:D6 are unlocked and can be edited after sheet protection.

2

Step 2: Ensure total cells remain locked to prevent editing.

Select cells E2:E6 and B7:E7, right-click, choose 'Format Cells', go to 'Protection' tab, make sure 'Locked' is checked.

Expected Result

Total cells in column E and the total row remain locked.

3

Step 3: Protect the worksheet to enforce the locking.

Go to the 'Review' tab, click 'Protect Sheet', enter a password (optional), ensure 'Select locked cells' and 'Select unlocked cells' are checked, then click OK.

Expected Result

Sheet is protected; users can edit unlocked cells but cannot change locked total cells.

4

Step 4: Test by trying to edit a total cell and an unlocked expense cell.

Try to change cell E2 (total) and cell B2 (expense).

Expected Result

Editing cell E2 is blocked; editing cell B2 is allowed.

Final Result

Monthly Budget Sheet

Category       Jan   Feb   Mar   Total
----------------------------------------
Rent          1000  1000  1000  3000
Utilities      150   130   140   420
Office Supplies 200   180   220   600
Travel         300   400   350  1050
Marketing      500   600   550  1650
----------------------------------------
Total Expenses 2150  2310  2260  6720

* Total cells are locked and cannot be edited.
* Expense cells are unlocked for editing.

✓Total cells are protected to prevent accidental changes.

✓Team members can update individual expense amounts safely.

✓Sheet protection helps maintain data integrity in shared files.

Bonus Challenge

Add a password to the sheet protection and create a hidden summary sheet that only managers can access.

Show Hint

Use 'Protect Sheet' with a password and hide the summary sheet by right-clicking its tab and selecting 'Hide'.