Lab 11
Protection Practice
A pre-test and post-test scores file. You'll calculate the change for each student, then protect the sheet so the formulas are hidden, names are editable, and users can format cells — but nothing else can be changed.
Download prepost.xlsxComplete steps in this exact order
Cell properties like Hidden and Locked only take effect when sheet protection is turned on. You must configure all cell properties before you click Protect Sheet. If you protect first and then try to adjust — you'll need to unprotect, make changes, then re-protect.
Calculate the Change column
Column D — Post-Test minus Pre-Test
Enter the formula in cell D2 and copy it down for all students. The change can be positive (improvement), negative (decline), or zero (no change).
= C2 - B2 ← Post-Test minus Pre-Test
Check your Change column — enter the value for each student
| Student | Pre | Post | Change |
|---|---|---|---|
| Osami Abe | 61 | 87 | |
| Kiyotaka Asai | 88 | 86 | |
| Jacob Chang | 81 | 81 | |
| April Connors | 70 | 99 |
Negative values are expected
Some students scored lower on the post-test than the pre-test. A negative change is a valid result — don't be surprised to see it. Excel handles negative numbers naturally in this formula.
Configure cell properties — before protecting
Three things to set up first: hide formulas, unlock names, keep scores locked
Each column needs a different protection setting. Set all of these up before you turn on sheet protection.
a
Hide the formulas in the Change column (D)
When sheet protection is on, a "Hidden" cell shows its value normally — but the formula bar shows nothing. Users see the result without being able to see how it was calculated.
🔮 Hidden = ✓
formula bar will be blank when protected
The Locked checkbox in the same tab should remain checked for column D — you don't want users editing the formula cells either. Only Hidden needs to be turned on.
b
Allow editing for the Names column (A)
By default all cells are Locked. To allow users to edit the Name column, you must remove the Locked property from those cells before protecting.
🔓 Locked = ✗
names can be edited when protected
—
Pre-Test and Post-Test columns (B and C) — leave as is
No changes needed for B and C. They are already Locked by default — when sheet protection is on, users won't be able to edit them.
🔒 Locked = ✓
scores cannot be changed
Summary — what each column should look like
| Column | Locked | Hidden | Effect when protected |
|---|---|---|---|
| A — Name | ✗ Unlocked | ✗ | Users can edit names |
| B — Pre-Test | ✓ Locked | ✗ | Read-only — cannot be changed |
| C — Post-Test | ✓ Locked | ✗ | Read-only — cannot be changed |
| D — Change | ✓ Locked | ✓ Hidden | Value visible, formula hidden in formula bar |
Protect the Sheet
Password: 1234 · Enable Format cells · Leave defaults for everything else
In the Protect Sheet dialog, configure it as follows:
Protect Sheet dialog settings
Password
1234
✓
Select locked cells
default ✓
✓
Select unlocked cells
default ✓
✓
Format cells
← enable this
✗
Format columns
leave unchecked
✗
Insert / Delete rows
leave unchecked
Don't forget to confirm the password
Excel asks you to type the password twice to confirm. Use 1234 both times. If you type different values, it won't accept.
Verify — test these four things after protecting
Click on a cell in the Change column (D) — the formula bar should be empty even though the value shows in the cell
Try to edit a Pre-Test or Post-Test score — Excel should block it with a protection message
Try to edit a Name — this should work normally
Try to change the font colour of any cell — this should be allowed (Format cells was enabled)