Protecting sheets and workbooks

Protection stops accidental changes. There are two kinds:

• Sheet protection stops locked cells on a sheet being typed into.
• Workbook protection stops sheets being added, deleted, renamed, moved or duplicated.

Both are saved in the workbook and honoured by Excel. Neither uses a password: protection states how the workbook is meant to be used, and anyone can turn it off from the Review tab.

Lock and unlock cells

Every cell starts locked. Locking does nothing until the sheet is protected, so the usual approach is to unlock the cells people should fill in, then protect the sheet.

1. Select the cells that should stay editable.
2. Click Unlock in Review › Protect.

To lock cells again, select them and click Lock. You can also set Locked on the Protection tab of Format cells — see Formatting cells.

Protect a sheet

1. Show the sheet.
2. Click the lock button in Review › Protect (Protect the sheet — locked cells will refuse edits).

The status bar says Sheet protected. Locked cells refuse edits. The lock button is highlighted while the sheet is protected.

When anyone tries to type into a locked cell, the edit is refused and the status bar says, for example, Budget is protected, and B4 is locked.

To unprotect the sheet, click the same button (Unprotect the sheet). The status bar says Sheet unprotected.

Keep ranges editable on a protected sheet

An editable range is a named block that anyone may type into while the sheet is protected, even if its cells are locked. A form is a good example: a protected sheet with a few cells to fill in.

1. Select the block that should stay editable.
2. Click Review › Protect › Allow ranges…. Once the sheet has some, the button shows how many, such as 2 allowed.
3. In Ranges anybody may edit, type a name in the box labelled Add followed by the selection’s address — for example Entry.
4. Click Add.
5. Click Close.

The status bar says the block stays editable when the sheet is protected. The dialog lists each range by name and address; click × beside one to remove it.

Protect the workbook structure

1. Click Workbook in Review › Protect (Protect the structure — no adding, deleting, renaming or moving sheets).

The button is highlighted, and the status bar explains that sheets can no longer be added, deleted, renamed or moved, and that this states an intention rather than locking anyone out.

While the structure is protected, these commands are refused: adding a sheet, renaming, deleting, moving and duplicating sheets. The status bar names the command, for example This workbook’s structure is protected, so you cannot delete a sheet. Unprotect it on the Review tab first.

To remove workbook protection, click Workbook again (Let sheets be added, deleted, renamed and moved again). The status bar says The workbook’s structure is unlocked.

Cells can still be edited while only the workbook is protected.

Workbooks from other apps

Sheet protection, locked and unlocked cells, editable ranges and workbook structure protection in a workbook you open are all honoured, and written back when you save. Protection that Nixt Sheets writes has no password.