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.

Practice

1. What is the main purpose of sheet protection in Excel?

easy

A. To change the font style of cells

B. To delete all data in the sheet

C. To automatically save the file

D. To prevent unwanted changes to the spreadsheet

Solution

1. Step 1: Understand sheet protection purpose

   Sheet protection is used to stop users from making unwanted changes to the sheet.

2. Step 2: Compare options

   Only To prevent unwanted changes to the spreadsheet correctly describes this purpose; others describe unrelated actions.

3. Final Answer:

   To prevent unwanted changes to the spreadsheet -> Option D

4. Quick Check:

   Sheet protection = prevent unwanted changes [OK]

Hint: Sheet protection stops edits except where allowed [OK]

Common Mistakes:

• Thinking it deletes data
• Confusing with autosave
• Assuming it changes formatting automatically

2. Which of the following is the correct way to unlock cells before protecting a sheet?

easy

A. Select cells, go to Data tab, click Sort

B. Select cells, right-click, Format Cells, Protection tab, uncheck 'Locked'

C. Select cells, press Delete key

D. Select cells, change font color to white

Solution

1. Step 1: Identify how to unlock cells

   Unlocking cells is done via Format Cells > Protection tab by unchecking 'Locked'.

2. Step 2: Verify options

   Only Select cells, right-click, Format Cells, Protection tab, uncheck 'Locked' describes this correct method; others do unrelated actions.

3. Final Answer:

   Select cells, right-click, Format Cells, Protection tab, uncheck 'Locked' -> Option B

4. Quick Check:

   Unlock cells via Format Cells > Protection [OK]

Hint: Unlock cells via Format Cells before protecting sheet [OK]

Common Mistakes:

• Deleting cells instead of unlocking
• Using Data tab options wrongly
• Changing font color to hide data

3. You protect a sheet but forget to unlock cells where users enter data. What happens when users try to type in those cells?

medium

A. The sheet automatically unlocks those cells

B. Users can type normally without restrictions

C. Users get an error and cannot edit those cells

D. Users can only change font color in those cells

Solution

1. Step 1: Understand locked cells effect

   If cells are locked and sheet is protected, users cannot edit those cells.

2. Step 2: Analyze user experience

   Users will get an error message or be blocked from typing in locked cells.

3. Final Answer:

   Users get an error and cannot edit those cells -> Option C

4. Quick Check:

   Locked cells + protected sheet = no editing [OK]

Hint: Locked cells block editing when sheet is protected [OK]

Common Mistakes:

• Assuming sheet unlocks cells automatically
• Thinking users can edit anyway
• Believing only formatting is blocked

4. You protected a sheet but users report they cannot sort data. What is the likely cause and fix?

medium

A. Sheet protection disables sorting; allow sorting in protection options

B. Cells are unlocked; lock them to enable sorting

C. File is corrupted; repair the file

D. Sorting is disabled in Excel options; enable it there

Solution

1. Step 1: Identify sorting restriction in protected sheets

   By default, sheet protection disables sorting unless allowed in options.

2. Step 2: Fix by enabling sorting permission

   When protecting the sheet, check 'Sort' permission to allow users to sort data.

3. Final Answer:

   Sheet protection disables sorting; allow sorting in protection options -> Option A

4. Quick Check:

   Allow sorting in protection settings to fix [OK]

Hint: Enable sorting permission when protecting sheet [OK]

Common Mistakes:

• Locking cells to fix sorting
• Assuming file corruption causes sorting issue
• Looking in Excel options instead of protection settings

5. You want users to enter data only in cells B2:B10 but protect the rest of the sheet. Which steps correctly achieve this?

hard

A. Unlock B2:B10 cells, then protect sheet with default settings

B. Lock B2:B10 cells, then protect sheet allowing editing everywhere

C. Protect sheet first, then unlock B2:B10 cells

D. Delete all other cells except B2:B10 before protecting

Solution

1. Step 1: Unlock only B2:B10 cells

   Select cells B2:B10, open Format Cells > Protection tab, uncheck 'Locked'.

2. Step 2: Protect the sheet with default settings

   Protecting the sheet now locks all other cells except unlocked B2:B10, allowing data entry only there.

3. Final Answer:

   Unlock B2:B10 cells, then protect sheet with default settings -> Option A

4. Quick Check:

   Unlock input cells before protecting sheet [OK]

Hint: Unlock input cells first, then protect sheet [OK]

Common Mistakes:

• Locking input cells instead of unlocking
• Protecting sheet before unlocking cells
• Deleting cells instead of unlocking