Protecting* Cells, Worksheets, & Workbooks

14 slides

Click the right arrow to go to the next slide.

* I use the terms lock and protect synonymously. If a cell or worksheet is protected, it is locked.

Three Types of Protection

The first two allow access to areas of a protected worksheet, i.e., after the worksheet has been locked.

1. Locked worksheet/unlocked cells. Allow everyone access to cells that remain unlocked after the worksheet is locked.
2. Locked worksheet/locked cells with a password. Allow certain people with a password to access certain locked cells in a protected worksheet.
3. Workbook structure. This doesn't affect editing but prevents users from moving, copying, renaming, deleting, and adding worksheets themselves.

FIRST: How to Protect (Lock) a Worksheet

The best way to lock a worksheet is in the Review tab of the main-menu ribbon under Protect. It will tell you if the worksheet is currently protected or unprotected.

The sheet is unprotected. Clicking will protect it.              This sheet is protected. Clicking will unprotect it.

FIRST: How to Protect (Lock) a Worksheet

The Protect Sheet dialog box

1. A password to unlock the worksheet is optional. Without it, anyone can unlock it again. Oftentimes, that's OK.

2. You can also use the checklist to unlock certain features that allow users to perform these tasks while the worksheet is locked. I usually check Format Cells.

3. Click OK, and your worksheet is locked (protected).

Locked Worksheet with

Unlocked Cells (everyone has access)

Officetuts.net lists 11 ways to invoke the Format Cells dialog box.

The two quickest ways are to (1) use the Ctrl + 1 keyboard shortcut and (2) right-click in a cell to bring up the pop-up menu and select Format Cells...

Locked Worksheet with

Unlocked Cells

The last tab in the Format Cells dialog box is Protection.

By default, all cells are locked and their formulas unhidden.

Locked Worksheet with

Unlocked Cells

(1) Select the cell(s) you want to unlock and call up the Format Cells dialog box. (2) Go to Protection and (3) uncheck Locked.

Locked Worksheet with

Unlocked Cells

Of course, unlocking cells doesn't mean anything until the worksheet itself is locked (protected). So don't forget to do that.

Locked Worksheet with

LOCKED Cells (need password to access)

It might be the case that you want certain parts of your worksheet locked to keep everyone out except for certain users.

So here's a case where you most certainly want to put a password on the locked worksheet. 

Now, all you have to do is put passwords on the areas of the worksheet you want only certain users to be able to access.

Locked Worksheet with

Locked Cells

1. Go to the Review tab and click on Allow Edit Ranges.

2. You can now add as many ranges as you like and password protect each one. Just click New to begin adding them.

Locked Worksheet with

Locked Cells

3. Give the password-protected range a name.

4. Select the range.

5. Enter its password and click OK.

6. Lock the worksheet.

7. Share the password with those you want to be able to edit that range when it's locked. Repeat for each range.

Protect the Workbook Structure

Protecting the workbook's structure doesn't affect editing. It stops users from being able to add, delete, move, copy or rename the workbook's worksheets themselves.

1. Go to the Review tab and click Protect Workbook.

Protect the Workbook Structure

1. Go to the Review tab and click Protect Workbook.

2. Enter a password if you like (to prevent others from unprotecting the structure). Click OK.

Fin