Why protecting data prevents mistakes in Excel - Business Case Study

Scenario Mode

👤 Your Role: You are a financial analyst at a small company.

📋 Request: Your manager wants you to prepare a monthly expense report that team members can update only in specific cells without accidentally changing formulas or headers.

📊 Data: You have a table with expense categories, budgeted amounts, actual expenses, and variance calculations.

🎯 Deliverable: Create a protected Excel sheet where only the 'Actual Expenses' column is editable, and formulas and headers are locked to prevent accidental changes.

Sample Data

Category	Budgeted Amount	Actual Expenses	Variance
Office Supplies	500	450	=B2-C2
Travel	1200	1300	=B3-C3
Utilities	300	280	=B4-C4
Marketing	800	750	=B5-C5
Training	400	420	=B6-C6

1

Step 1: Select the entire worksheet and unlock all cells to allow editing.

Right-click any cell, choose 'Format Cells', go to 'Protection' tab, uncheck 'Locked', then click OK.

Expected Result

All cells are unlocked and can be edited.

2

Step 2: Select the category and budgeted amount data cells (A2:B6) and lock them to prevent changes.

Select cells A2:B6, right-click, choose 'Format Cells', go to 'Protection' tab, check 'Locked', then click OK.

Expected Result

Category and budgeted amount data cells are locked.

3

Step 3: Lock the header row and formula cells to prevent changes.

Select cells A1:D1 and D2:D6, right-click, choose 'Format Cells', go to 'Protection' tab, check 'Locked', then click OK.

Expected Result

Headers and variance formula cells are locked.

4

Step 4: Protect the worksheet to enforce the locked cells and allow editing only in unlocked cells.

Go to 'Review' tab, click 'Protect Sheet', set a password if desired, ensure 'Select unlocked cells' is checked, then click OK.

Expected Result

Sheet is protected; only 'Actual Expenses' cells can be edited; other cells cannot be changed.

5

Step 5: Test by trying to edit a locked cell and an unlocked cell to confirm protection works.

Try editing cell B2 (locked) and C2 (unlocked).

Expected Result

Editing B2 is blocked; editing C2 is allowed.

Final Result

Expense Report (Protected Sheet)

+----------------+----------------+----------------+----------+
| Category       | Budgeted Amount| Actual Expenses| Variance |
+----------------+----------------+----------------+----------+
| Office Supplies| 500            | 450            | 50       |
| Travel         | 1200           | 1300           | -100     |
| Utilities      | 300            | 280            | 20       |
| Marketing      | 800            | 750            | 50       |
| Training       | 400            | 420            | -20      |
+----------------+----------------+----------------+----------+

* Only 'Actual Expenses' cells are editable.
* Headers and formulas are locked to prevent mistakes.

✓Protecting the sheet prevents accidental changes to formulas and headers.

✓Users can only update actual expenses, reducing errors.

✓Variance calculations remain accurate and safe from edits.

Bonus Challenge

Add conditional formatting to highlight negative variance values in red automatically.

Show Hint

Use 'Conditional Formatting' > 'New Rule' > 'Format only cells that contain' with rule 'Cell Value < 0' and set font color to red.

Practice

1. Why is it helpful to protect a spreadsheet by locking cells?

easy

A. It hides the spreadsheet from other users.

B. It prevents accidental changes to important data.

C. It automatically fixes errors in formulas.

D. It makes the spreadsheet load faster.

Solution

1. Step 1: Understand the purpose of locking cells

   Locking cells stops users from changing important data by mistake.

2. Step 2: Recognize what locking does not do

   Locking does not speed up loading, fix errors, or hide sheets.

3. Final Answer:

   It prevents accidental changes to important data. -> Option B

4. Quick Check:

   Protecting data = Prevent mistakes [OK]

Hint: Lock cells to stop accidental edits [OK]

Common Mistakes:

• Thinking protection speeds up the file
• Believing protection fixes formula errors
• Assuming protection hides data

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

easy

A. Select cells, press Ctrl+C to copy

B. Select cells, press Delete key

C. Select cells, go to Data tab, click Filter

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

Solution

1. Step 1: Identify how to unlock cells

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

2. Step 2: Eliminate unrelated actions

   Deleting, filtering, or copying cells does not unlock them.

3. Final Answer:

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

4. Quick Check:

   Unlock cells via Format Cells > Protection [OK]

Hint: Unlock cells via Format Cells > Protection tab [OK]

Common Mistakes:

• Trying to unlock by deleting cell content
• Confusing filter with unlocking
• Using copy instead of unlocking

3. You have locked all cells except B2 and B3, then protected the sheet. What happens if a user tries to edit cell A1?

medium

A. The user gets an error and cannot edit A1.

B. The user can edit A1 but changes are not saved.

C. The user can edit A1 without restrictions.

D. The user can edit A1 only if they enter a password.

Solution

1. Step 1: Understand locked cells behavior

   Locked cells cannot be edited once the sheet is protected.

2. Step 2: Apply to cell A1

   Since A1 is locked, editing it is blocked and shows an error.

3. Final Answer:

   The user gets an error and cannot edit A1. -> Option A

4. Quick Check:

   Locked + Protected = No edit allowed [OK]

Hint: Locked cells block edits after protection [OK]

Common Mistakes:

• Assuming locked cells can be edited
• Thinking changes are saved but hidden
• Believing password is always required

4. You protected a sheet but forgot to unlock input cells. What is the best fix to allow data entry?

medium

A. Delete the sheet and create a new one.

B. Change the file format to CSV.

C. Unprotect the sheet, unlock input cells, then protect again.

D. Use the Undo button to revert protection.

Solution

1. Step 1: Identify the problem

   Input cells are locked, so users cannot enter data after protection.

2. Step 2: Correct the protection setup

   Unprotect the sheet, unlock the input cells, then protect the sheet again.

3. Final Answer:

   Unprotect the sheet, unlock input cells, then protect again. -> Option C

4. Quick Check:

   Unlock inputs before protecting [OK]

Hint: Unlock inputs before protecting sheet [OK]

Common Mistakes:

• Deleting sheet instead of fixing protection
• Changing file format does not fix protection
• Undo does not revert protection permanently

5. You want to share a budget sheet with your team. You want them to enter expenses only in column C but prevent changes elsewhere. How do you set this up?

hard

A. Unlock column C cells, lock all others, then protect the sheet.

B. Lock column C cells, unlock all others, then protect the sheet.

C. Protect the sheet without unlocking any cells.

D. Do not protect the sheet at all.

Solution

1. Step 1: Identify input area

   Users should enter data only in column C, so those cells must be unlocked.

2. Step 2: Lock all other cells and protect

   Lock all other cells to prevent changes, then protect the sheet to enforce it.

3. Final Answer:

   Unlock column C cells, lock all others, then protect the sheet. -> Option A

4. Quick Check:

   Unlock inputs, lock rest, protect sheet [OK]

Hint: Unlock inputs, lock others, then protect [OK]

Common Mistakes:

• Locking input cells prevents data entry
• Not protecting sheet allows all edits
• Unlocking all cells defeats protection purpose