To prevent a user from changing a cell’s content, like a cell holding a critical formula, we must execute a two-step process.
- Flag the cell as either a locked or unlocked
- Engage protection on the sheet.
When we perform the second step, protecting the sheet, any cell flagged as a locked cell will be protected from unwanted changes.
If we skip the first step, then all cells are flagged as locked by default. The entire sheet will be placed in a read-only mode when engaging sheet protection.
Attempting to change a protected cell’s contents will reveal the following message.
If we don’t want the entire sheet protected, we’ll need to “punch some holes” in our protective wall.
Step 1: Setting the Cell’s Protection Status
To set the status of a cell as either locked or unlocked we need to access the cell’s formatting controls.
Highlight a cell or multiple cells, then right-click a selected cell and click Format Cells… . (You can also press CTRL-1 to open the needed dialog box.)
In the Format Cells dialog box, select the Protection tab. This will display the “Locked” property for the selected cell.
Placing a check will ensure the cell is locked when sheet protection is engaged.
Clearing the checkbox will ensure that the cell can be freely changed while protecting the remainder of the sheet’s cells.
NOTE: If you see a filled checkbox like below, it means that you have selected two or more cells that have a mixture of locked and unlocked states.
Many beginning users assume (and we know what happens when you assume) that just by checking the “Locked” box they have protected the cell from changes. This is incorrect.
You must perform the next step for protection to be enforced. All you have done here is tell Excel which cells to protect when performing the next step.
Step 2: Engaging Sheet Protection
Once you have decided which cells will be protected and which cells will remain open to changes, engage sheet protection by selecting Review (tab) -> Protect (group) -> Protect Sheet.
A Protect Sheet dialog box appears asking for a password to unlock the sheet.
Protecting a sheet with a password is optional, but it’s a good way to keep a semi-literate Excel user from removing the sheet protection and making mischief with your file.
After you click OK, the sheet is now protected from unwanted changes in the entire spreadsheet while allowing changes to defined areas (i.e., exceptions).