Page 241 - Excel Workbook for Dummies

24_798452 ch17.qxp 3/13/06 7:39 PM Page 224
224 Part IV: Managing and Securing Data
Protecting the Worksheet

After you’ve got your spreadsheet the way you want it, you are wise to use Excel’s
Protection feature to keep it that way: Nothing is quite as bad as having an inexperi-
enced data entry operator doing major damage to the formulas and functions that
you’ve worked so hard to construct and validate. To keep the formulas and stan-
dard text in a spreadsheet safe from all unwarranted changes, you need to protect
its worksheet.
All cells in the workbook can have one of two different protection formats: locked or
unlocked and hidden or unhidden. Whenever you begin a new spreadsheet, all of the
cells in the workbook have the locked and unhidden status. However, this status in
and of itself means nothing until you turn on protection in the worksheet. At that
time, you are then prevented from making any editing changes to all locked cells and
from viewing the contents of all hidden cells on the Formula bar when they contain the
cell cursor.

What this means in practice is that prior to turning on worksheet protection, you
go through the spreadsheet removing the Locked protection format from all the cell
ranges where you or your users still need to be able to do data entry and editing even
when the worksheet is protected. You also assign the Hidden protection format to all
cell ranges in the spreadsheet where you don’t want the contents of the cell to be dis-
played when protection is turned on in the worksheet. Then, after activating protec-
tion, the user can make changes only to the unlocked cells and display on the Formula
bar the contents of the unhidden cells in the sheet.

When setting up your own spreadsheet templates, you will want to unlock all the cells
where users need to input new data and keep locked all the cells that contain headings
and formulas that never change. You may also want to hide cells with formulas if you’re
concerned that their display might tempt the users to waste time trying to fiddle with
or finesse them. Then, turn on worksheet protection prior to saving the file in the tem-
plate file format. You are then assured that all spreadsheets generated from that tem-
plate automatically inherit the same level and type of protection as you assigned in
the original spreadsheet.

When you open the Protect Sheet dialog box (Tools➪Protection➪Protect Sheet) to
turn on protection, Excel gives you an opportunity to assign a password to remove the
protection from the sheet (a wise move in most cases). This dialog box also contains
the following list of check box options that enable you to specify what particular edit-
ing actions users of the worksheet are still allowed to perform:
Select Locked Cells to enable the user to still be able to move the cell cursor into
the locked cells of the worksheet even when he can’t change their contents —
note that this check box is selected by default but you can deselect it when you
want to restrict movement to just the cells containing the unlocked protection
format that allow data entry.
Select Unlocked Cells to enable the user to be able to move the cell cursor into
the unlocked cells of the worksheet so that he can make changes to them — note
that deselecting this check box automatically deselects the Select Locked Cells
check box as well, making it impossible to move the cell cursor to any cells in
the worksheet and subsequently make any type of editing changes in the work-
sheet, which is very rarely your intention.
Format Cells to enable the formatting of cells (with the exception of changing
the locked and hidden status on the Protection tab of the Format Cells dialog
box).