Cell locking in Excel - Real Business Scenario

Scenario Mode

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

📋 Request: Your manager wants you to protect the budget sheet so that only certain cells can be edited, preventing accidental changes to formulas.

📊 Data: You have a budget table with categories, planned amounts, actual amounts, and variance calculated by formulas.

🎯 Deliverable: Create a budget sheet where only the 'Actual Amount' cells are editable, and all other cells are locked and protected.

Sample Data

Category	Planned Amount	Actual Amount	Variance
Office Supplies	500	450	=C2-B2
Travel	1200	1300	=C3-B3
Training	800	700	=C4-B4
Marketing	1500	1600	=C5-B5

1

Step 1: Select the entire budget table range including headers.

Select cells A1:D5.

Expected Result

All cells in the table are selected.

2

Step 2: Unlock the 'Actual Amount' cells so they can be edited.

Select cells C2:C5, right-click, choose Format Cells > Protection tab, uncheck 'Locked'.

Expected Result

'Actual Amount' cells are unlocked and editable.

3

Step 3: Protect the worksheet to enforce locking.

Go to Review tab > Protect Sheet, set a password if desired, ensure 'Select unlocked cells' is checked and 'Select locked cells' is unchecked, then click OK.

Expected Result

Only 'Actual Amount' cells can be edited; other cells are locked and protected.

4

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

Try editing cell B2 (locked) and cell C2 (unlocked).

Expected Result

Editing B2 is blocked with a warning; editing C2 is allowed.

Final Result

Budget Sheet (Protected)

Category       Planned Amount   Actual Amount   Variance
---------------------------------------------------------
Office Supplies    500              450           -50
Travel            1200             1300          100
Training           800              700          -100
Marketing         1500             1600          100

Only 'Actual Amount' cells (column C) are editable.

✓Unlocked cells allow user input while formulas and important data remain protected.

✓Protecting the sheet prevents accidental changes to formulas and planned amounts.

✓Users can only update actual spending amounts safely.

Bonus Challenge

Add conditional formatting to highlight variance cells in red if the variance is negative.

Show Hint

Use a formula-based conditional formatting rule like =D2<0 applied to the variance cells.