Cell locking in Excel - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

What happens when you lock cells but do not protect the sheet?

You select some cells and lock them using Excel's cell locking feature, but you do not protect the worksheet. What will happen if you try to edit those locked cells?

A You can edit the locked cells without any restriction.

B You cannot edit the locked cells; Excel blocks any changes.

C Excel will show an error message but still allow editing.

D The locked cells become hidden and cannot be seen.

💡 Hint

Think about what the 'Protect Sheet' feature does in Excel.

Locking cells alone does not prevent editing. You must protect the worksheet to enforce the lock. Without protection, locked cells behave like unlocked cells.

📊 Formula Result

intermediate

What is the result of editing a locked cell on a protected sheet?

You have a worksheet where cells A1 to A5 are locked and the sheet is protected. You try to type a new value in cell A3. What will happen?

A The new value is accepted and replaces the old value.

B Excel allows editing but only if you enter a formula.

C Excel shows a warning and does not allow the change.

D Excel deletes the content of cell A3 automatically.

💡 Hint

Think about what protection does to locked cells.

When a sheet is protected, locked cells cannot be edited. Excel shows a warning message and blocks changes.

❓ Function Choice

advanced

Which Excel feature allows you to lock cells and prevent editing?

You want to make sure certain cells cannot be changed by others. Which combination of features should you use?

A Select cells, lock them, then protect the sheet.

B Select cells, unlock them, then protect the sheet.

C Select cells, hide them, then protect the sheet.

D Select cells, format as text, then protect the sheet.

💡 Hint

Remember the default state of cells and what locking means.

By default, all cells are locked. To protect specific cells, you unlock the ones you want editable and protect the sheet. Locking cells and protecting the sheet prevents editing.

🎯 Scenario

advanced

You want users to edit only cells B2:B10 but not others. How do you set this up?

Describe the steps to allow editing only in cells B2:B10 while locking all other cells in the worksheet.

A Unlock all cells, then protect the sheet.

B Lock B2:B10, unlock all other cells, then protect the sheet.

C Hide B2:B10, lock all other cells, then protect the sheet.

D Unlock B2:B10, lock all other cells, then protect the sheet.

💡 Hint

Think about which cells should be locked or unlocked to allow editing only in B2:B10.

By unlocking B2:B10 and locking all other cells, then protecting the sheet, only B2:B10 remain editable.

❓ data_analysis

expert

Analyzing protection impact on formulas referencing locked cells

You have a protected sheet with locked cells A1:A5 containing numbers. Cell B1 has the formula =SUM(A1:A5). If a user tries to change A3, what happens to the value in B1?

A The user can change A3, and B1 updates accordingly.

B The user cannot change A3, so B1 remains the same.

C The formula in B1 breaks and shows an error.

D B1 shows zero regardless of changes.

💡 Hint

Consider what protection does to locked cells and formulas referencing them.

Since A3 is locked and the sheet is protected, the user cannot change A3. Therefore, B1's sum remains unchanged.

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