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.