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.

Practice

1. What does locking a cell in Excel do before you protect the sheet?

easy

A. Prevents the cell from being edited after sheet protection is enabled

B. Deletes the cell content automatically

C. Highlights the cell with a color

D. Copies the cell content to another sheet

Solution

1. Step 1: Understand cell locking purpose

   Locking a cell marks it so it cannot be changed once the sheet is protected.

2. Step 2: Effect of protecting the sheet

   Protecting the sheet activates the locking, preventing edits on locked cells.

3. Final Answer:

   Prevents the cell from being edited after sheet protection is enabled -> Option A

4. Quick Check:

   Locking + Protect Sheet = No edits [OK]

Hint: Lock cells then protect sheet to stop edits [OK]

Common Mistakes:

• Thinking locking deletes or copies cells
• Believing locking highlights cells
• Assuming locking works without protecting sheet

2. Which of these is the correct way to unlock a cell before protecting the sheet?

easy

A. Use Data Validation to unlock the cell

B. Double-click cell and press Delete key

C. Right-click cell -> Format Cells -> Protection tab -> Uncheck 'Locked'

D. Insert a comment in the cell

Solution

1. Step 1: Locate cell protection settings

   Right-click the cell and open Format Cells dialog, then go to Protection tab.

2. Step 2: Unlock the cell

   Uncheck the 'Locked' checkbox to make the cell editable after sheet protection.

3. Final Answer:

   Right-click cell -> Format Cells -> Protection tab -> Uncheck 'Locked' -> Option C

4. Quick Check:

   Unlock cells via Format Cells -> Protection [OK]

Hint: Unlock cells via Format Cells -> Protection tab [OK]

Common Mistakes:

• Trying to unlock by deleting content
• Using Data Validation to unlock cells
• Adding comments to unlock cells

3. You locked cells A1:A5 and unlocked B1:B5. After protecting the sheet, which cells can you edit?

medium

A. Only cells A1:A5

B. Only cells B1:B5

C. Both A1:A5 and B1:B5

D. No cells can be edited

Solution

1. Step 1: Understand locked vs unlocked cells

   Locked cells cannot be edited after protection; unlocked cells remain editable.

2. Step 2: Apply to given ranges

   Cells A1:A5 are locked, so protected sheet blocks edits there; B1:B5 unlocked, so editable.

3. Final Answer:

   Only cells B1:B5 -> Option B

4. Quick Check:

   Unlocked cells stay editable after protection [OK]

Hint: Only unlocked cells editable after protection [OK]

Common Mistakes:

• Assuming locked cells are editable
• Thinking protection disables all cells
• Confusing locked and unlocked ranges

4. You locked cells but forgot to protect the sheet. What happens when you try to edit those cells?

medium

A. The sheet becomes read-only

B. Excel shows an error and blocks editing

C. The cells get deleted automatically

D. You can edit the locked cells without restriction

Solution

1. Step 1: Understand locking vs protecting

   Locking marks cells but does not enforce protection until sheet is protected.

2. Step 2: Effect of no protection

   Without protecting the sheet, locked cells behave like normal cells and can be edited freely.

3. Final Answer:

   You can edit the locked cells without restriction -> Option D

4. Quick Check:

   Locking alone does not block edits [OK]

Hint: Protect sheet to activate locking effect [OK]

Common Mistakes:

• Thinking locking alone blocks edits
• Expecting error without protection
• Confusing locking with sheet protection

5. You want to protect a budget sheet so users can only change the 'Notes' column (D) but not the amounts (A:C). How do you set this up?

hard

A. Unlock column D cells, lock columns A:C, then protect the sheet

B. Lock all cells, then protect the sheet without unlocking any cells

C. Unlock columns A:C, lock column D, then protect the sheet

D. Do not lock any cells and protect the sheet

Solution

1. Step 1: Unlock the 'Notes' column (D)

   Make column D cells unlocked so they remain editable after protection.

2. Step 2: Lock columns A:C and protect the sheet

   Lock columns A:C to prevent edits, then enable sheet protection to enforce locking.

3. Final Answer:

   Unlock column D cells, lock columns A:C, then protect the sheet -> Option A

4. Quick Check:

   Unlock editable cells, lock others, then protect [OK]

Hint: Unlock editable cells only, then protect sheet [OK]

Common Mistakes:

• Locking editable column by mistake
• Not protecting sheet after locking
• Unlocking wrong columns