Problem: You have to key-in data in a large number of cells in a month end financial statement, as shown in Fig. 608. You don’t want to accidentally key-in a number in a cell with a formula. How can you protect just the formula cells?
Strategy: After unlocking all cells, use the Edit – Go To – Special dialog to select only the cells with formulas and lock just those cells.
By default, all cells in the worksheet start with their Locked property set to TRUE. You don’t realize this until you turn on protection for the first time. The first step is to unlock all of the cells.
- Select all cells with Ctrl+A. From the menu, select Format – Cells.
Click on the Protection tab. As shown in Fig. 609, you will see that
the Locked option is chosen.
- Uncheck the Locked box. Choose OK to dismiss the Format Cells dialog.
-
You should still have all of the cells highlighted. From the menu, select Edit – Go To. On the Go To dialog, choose the Special button in the lower left corner, as shown in Fig. 610.
-
On the Go To Special dialog box, choose the option button for Formulas, as shown in Fig. 611.
Advertisement -
Choose OK to close the Go To Special dialog. As shown in Fig. 612, you will see that only the cells with formulas are selected.
-
From the menu, select Format – Cells. On the Protection tab, choose the Locked checkbox. This will lock only the selected cells, which are the formula cells.
Advertisement -
The final important step is to enable protection for the sheet. If you miss this final step, you can still accidentally overwrite your formulas. From the menu, select Tools – Protection – Protect Sheet to display the Protect Sheet dialog, as shown in Fig. 613.
Now, if you accidentally try to enter something in a formula cell, Excel will warn you, as shown in Fig. 614.
Summary: Use the Go To Special dialog to select only the formula cells to protect just those cells. Remember to protect the sheet from overwriting.
Commands Discussed: Format – Cells – Protection; Edit – Go To Special
Images
