Sheet protection in Excel - Real Business Scenario

Scenario Mode

👤 Your Role: You are an office administrator managing a monthly budget spreadsheet.

📋 Request: Your manager wants to prevent accidental changes to the budget totals while allowing team members to update individual expense items.

📊 Data: You have a spreadsheet with expense categories, monthly amounts, and total calculations.

🎯 Deliverable: Create a protected sheet that locks the total cells but allows editing of expense amounts.

Sample Data

Category	January	February	March	Total
Rent	1000	1000	1000	=SUM(B2:D2)
Utilities	150	130	140	=SUM(B3:D3)
Office Supplies	200	180	220	=SUM(B4:D4)
Travel	300	400	350	=SUM(B5:D5)
Marketing	500	600	550	=SUM(B6:D6)
Total Expenses	=SUM(B2:B6)	=SUM(C2:C6)	=SUM(D2:D6)	=SUM(E2:E6)

1

Step 1: Unlock the cells where team members can enter data (January to March columns for each category).

Select cells B2:D6, right-click, choose 'Format Cells', go to 'Protection' tab, uncheck 'Locked', then click OK.

Expected Result

Cells B2:D6 are unlocked and can be edited after sheet protection.

2

Step 2: Ensure total cells remain locked to prevent editing.

Select cells E2:E6 and B7:E7, right-click, choose 'Format Cells', go to 'Protection' tab, make sure 'Locked' is checked.

Expected Result

Total cells in column E and the total row remain locked.

3

Step 3: Protect the worksheet to enforce the locking.

Go to the 'Review' tab, click 'Protect Sheet', enter a password (optional), ensure 'Select locked cells' and 'Select unlocked cells' are checked, then click OK.

Expected Result

Sheet is protected; users can edit unlocked cells but cannot change locked total cells.

4

Step 4: Test by trying to edit a total cell and an unlocked expense cell.

Try to change cell E2 (total) and cell B2 (expense).

Expected Result

Editing cell E2 is blocked; editing cell B2 is allowed.

Final Result

Monthly Budget Sheet

Category       Jan   Feb   Mar   Total
----------------------------------------
Rent          1000  1000  1000  3000
Utilities      150   130   140   420
Office Supplies 200   180   220   600
Travel         300   400   350  1050
Marketing      500   600   550  1650
----------------------------------------
Total Expenses 2150  2310  2260  6720

* Total cells are locked and cannot be edited.
* Expense cells are unlocked for editing.

✓Total cells are protected to prevent accidental changes.

✓Team members can update individual expense amounts safely.

✓Sheet protection helps maintain data integrity in shared files.

Bonus Challenge

Add a password to the sheet protection and create a hidden summary sheet that only managers can access.

Show Hint

Use 'Protect Sheet' with a password and hide the summary sheet by right-clicking its tab and selecting 'Hide'.

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