Sheet protection in Excel - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does sheet protection do in Excel?

Sheet protection prevents users from changing locked cells or the structure of the sheet, helping to keep data safe.

beginner

How do you lock cells before protecting a sheet?

Select the cells, right-click, choose 'Format Cells', go to the 'Protection' tab, and check 'Locked'. Then protect the sheet to enforce locking.

intermediate

What happens if you protect a sheet without locking any cells?

If no cells are locked, protecting the sheet won't stop users from editing any cells because all cells are editable by default.

intermediate

Can you allow users to select locked cells on a protected sheet?

Yes, when protecting the sheet, you can choose to allow selecting locked cells or only unlocked cells.

beginner

What is the purpose of setting a password when protecting a sheet?

A password prevents others from unprotecting the sheet without permission, adding an extra layer of security.

What is the first step to protect specific cells in Excel?

A Lock the cells you want to protect

B Protect the sheet immediately

C Hide the cells

D Delete the cells

You must lock the cells first before protecting the sheet to make protection effective.

What happens if you protect a sheet without a password?

A No one can unprotect it

B Anyone can unprotect it easily

C The sheet deletes automatically

D Cells become hidden

Without a password, anyone can unprotect the sheet by clicking 'Unprotect Sheet'.

Which tab in 'Format Cells' lets you lock or unlock cells?

A Font

B Number

C Alignment

D Protection

The 'Protection' tab contains the 'Locked' checkbox for cells.

Can users edit unlocked cells on a protected sheet?

A Yes

B No

C Only if they have a password

D Only if the sheet is unprotected

Unlocked cells remain editable even when the sheet is protected.

What option can you choose when protecting a sheet to allow users to select only unlocked cells?

A Allow editing locked cells

B Hide locked cells

C Allow selecting unlocked cells

D Allow deleting rows

You can restrict selection to unlocked cells only for easier navigation.

Explain the steps to protect a sheet in Excel and why locking cells is important.

Think about what you do before and after protecting the sheet.

Describe how passwords enhance sheet protection and what happens if you don't use one.

Consider who can change protection settings.

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