Protection in Excel

Protection in Excel is a bizarre beast indeed. Here's a quick introduction into their intricacies and oddities.

First of all, the essentials:

• Protection is applied at one of three levels: workbook, sheet or cell
• Each level works differently.

Let's take it top-down.

Workbook-level

Workbook-level security can be found on the Review ribbon. (In this post, we won't get into the Information Rights Management Service, which is a different bag altogether.) Hit the Protect Workbook button and you're invited to Restrict Editing, with an option titled Protect Structure and Windows.

Don't worry about the Restrict Permissions options, as these get you into that world of Information Rights Management.

Then you're presented with two tick-boxes: one for Structure and one for Windows.

The Structure option will prevent people from adding or deleting worksheets, or unhiding hidden ones. The Windows option will lock the size and position that is displayed for each worksheet. So you won't be able to scroll around looking for other stuff.

If you don't type a password, then users will be able to unlock the workbook in the same way as you originally locked it. If you do, then they'll need that password to unlock it. Pretty simple really.

Worksheet-level

As its name suggests, this applies to the worksheet only, and can be found immediately to the left of the Protect Worksheet button. It allows you to prevent users from doing certain things within the sheet: format things, insert things, delete things, sort, filter etc.

Once again, you have the option of adding a password to the protection.

In here, you'll see a couple of references to "Locked Cells". The tick-box at the top of the dialog box defaults to being ticked, which is fortunate, because this is the option you want 99% of the time. You want to prevent people from doing stuff to cells that are locked.

And the section below details how indeed you format cells to be locked.

Cell-level

Somewhat counter-intuitively, all cells within an Excel spreadsheet default to being "Locked". But because the worksheet isn't protected, this status has no bearing.

If you create a brand new blank workbook and do nothing but protect Sheet1, the contents of all cells will be locked because the cells themselves are locked.

However often the business requirement is for some cells to be editable (usually those that are designed to house values) and for some to be locked (usually those that contain formulae).

To do this, you first need to unlock those cells that you want to be editable, and then protect the worksheet.

Whether or not a cell is locked is a feature found within the Format Cells dialog. So to unlock some cells, select them all (you can select non-contiguous ranges by selecting with the CTRL button held down) and then bring up the Format Cells dialog. To do this, either:

• Click the expansion button to the right of the Number section on the Home ribbon; or
• Hit CTRL+1.

Either will bring up the Format Cells dialog.

Hit the Protection tab and you're presented with two options:

• Locked: This will lock the cells. Its default is to be checked, so to make cells editable, you uncheck it.
• Hidden: This purely indicates whether the formula in a cell will be hidden. It can be useful if you don't want people to see the ugliness (or elegance) of a formula, and want them just to focus on the value it yields.

As the comment says, changing the settings here has absolutely no bearing until you protect the worksheet.

So to restrict editing to certain cells within a worksheet, you should undertake the following steps.

• Select those cells/columns/rows.
• Select Format Cells and uncheck the Locked tick-box on the Protection tab.
• Lock the worksheet.

But more often than not, there are certain areas of a worksheet that you *don't* want people to edit. In this case:

• Select the entire sheet (clicking to the left of the header of column A, immediately above the title of row 1
• Select Format Cells and uncheck the Locked tick-box on the Protection tab.
• Select the cells that you don't want people messing with
• Select Format Cells and *check* the Locked tick-box on the Protection tab.
• Lock the worksheet.

• Tweet