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.