Sheet protection in Excel - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

What happens when you protect a sheet without a password?

You protect an Excel sheet but do not set a password. What is the effect on users trying to edit locked cells?

A Users cannot edit locked cells and cannot unprotect the sheet without a password.

B Users cannot edit locked cells but can unprotect the sheet without a password.

C Users can edit locked cells without any restriction.

D Users can edit locked cells only if they have admin rights.

💡 Hint

Think about what happens if no password is set but protection is enabled.

When a sheet is protected without a password, users cannot edit locked cells, but they can unprotect the sheet easily because no password is required.

📊 Formula Result

intermediate

What is the result of trying to edit a locked cell on a protected sheet?

You have a sheet where cell A1 is locked and the sheet is protected. What happens if you try to type a new value into cell A1?

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

B Excel crashes and closes unexpectedly.

C Excel silently ignores the change without any message.

D Excel shows an error message and does not allow the change.

💡 Hint

Think about Excel's behavior when editing locked cells on protected sheets.

Excel prevents editing locked cells on protected sheets by showing an error message to the user.

❓ Function Choice

advanced

Which Excel feature allows selective editing on a protected sheet?

You want users to edit only certain cells on a protected sheet. Which feature should you use before protecting the sheet?

A Unlock the cells you want editable, then protect the sheet.

B Use Data Validation to restrict input on locked cells.

C Hide the cells you want users to edit.

D Use Conditional Formatting to allow editing.

💡 Hint

Think about how Excel treats locked and unlocked cells when protection is applied.

By unlocking specific cells before protecting the sheet, users can edit only those unlocked cells while locked cells remain protected.

🎯 Scenario

advanced

You want to allow users to sort data but not edit it on a protected sheet. How do you set this up?

You have a table on a sheet. You want users to sort the data but prevent them from changing any cell values. What should you do when protecting the sheet?

A Protect the sheet and check the option 'Sort' to allow sorting.

B Protect the sheet without any special options; sorting is allowed by default.

C Unlock all cells and protect the sheet with no options checked.

D Use Data Validation to prevent editing and protect the sheet.

💡 Hint

Look for protection options that allow sorting while keeping cells locked.

When protecting a sheet, you can check the 'Sort' option to allow users to sort data without editing locked cells.

❓ data_analysis

expert

Analyzing protection impact: How many cells can be edited?

A sheet has 1000 cells. 900 cells are locked, 100 are unlocked. The sheet is protected with default settings. How many cells can users edit?

A 900 cells

B 1000 cells

C 100 cells

D 0 cells

💡 Hint

Remember what locking and protection mean together.

Only unlocked cells can be edited on a protected sheet. Since 100 cells are unlocked, users can edit those 100 cells.