Microsoft Office Tutorials and References
In Depth Information
Workbooks are made to be shared, and
thus, Excel provides lots of ways in which you
can easily do that. For example, you can share
a workbook and track the individual changes
everyone makes to it. You can later review those
changes and accept or reject them as you want.
See Chapter 14, “Collaborating with Others,” for
help. Another way that you can protect data is
to simply lock it down, preventing anyone from
changing it. You can lock down individual cells,
a range of cells, or an entire worksheet in order
to prevent anyone from changing its data. You
can also protect a whole workbook, in order to
prevent changes to its structure, such as adding
worksheets or changing the workbook window’s
size. Finally, when needed, you can prevent
unauthorized users from even opening a
workbook at all.
By default, all cells in a worksheet are locked,
which means that if you just turn on protection,
no one will be able to change anything in the
protected area. So you actually work kind of
backwards, unlocking the cells you do not want
to protect, and then turning on protection.
Before you turn on protection, you can also hide
formulas. Follow these steps to unlock the cells
in a worksheet that you want to allow users to
change, and to hide formulas as desired:
1. Select the cell or range you want to unlock.
2. Click the Format button on the Home tab
and select Lock Cell from the pop-up menu
to turn that option off.
3. Repeat Steps 1 and 2 to unlock as many cells
as you want.
4. To hide a formula, click its result cell, and
then click the Format button on the Home
Locking and Unlocking Cells
If your goal is to allow others to view data but to
prevent them from messing it up by changing it,
you must start by designating the cells you want
to protect. You designate cells for protection by
locking them down. After locking down cells,
you turn on protection, which tells Excel to
protect the data in all the locked cells. You can
protect individual cells, ranges, or an entire
worksheet in this way. You can also tell Excel to hide
formulas. The formula result will still be shown,
but not the formula itself—in other words, if
someone clicks the cell, the formula is not
displayed in the Formula bar.
5. Select Format Cells from the pop-up menu.
The Format Cells dialog box appears.
6. Click the Protection tab.
Select the Hidden checkbox and click OK.
8. Repeat Steps 4–7 to hide additional formulas.
After hiding formulas and unlocking cells,
you are ready to protect the data you’ve kept
locked. To protect the locked cells, you must
now turn on worksheet protection.