Problem: I have to key in data in a large number of cells in a month-end financial statement. I don't want to accidentally key in a number in a cell that contains a formula. How can I protect just the formula cells?
- Allow people to enter details but protect the formulas.
Strategy: After unlocking all cells, you can use the Go To Special dialog to select only the cells with formulas and lock just those cells.
By default, all cells in a worksheet start with their Locked property set to TRUE, but you may not realize this until you turn on protection for the first time. The first step is to unlock all the cells:
- Select all cells by pressing Ctrl+A. Use Ctrl+One to open Format Cells.
- Click on the Protection tab in the Format Cells dialog. You will see that the Locked option is chosen.
- All cells start out locked by default.
- Uncheck the Locked box. Click OK to close the Format Cells dialog.
- With all the cells still highlighted, select Home, Find & Select, Go To Special.
- On the Go To Special dialog box, choose the Formulas option button.
- Select only formula cells.
- Click OK to close the Go To Special dialog. Excel will reduce the selection to only cells with formulas.
- Select Home, Format dropdown, Lock Cells. This will lock only the selected cells, which are the formula cells.
- Enable protection for the sheet. (Note that if you skip this final step, you can still accidentally overwrite your formulas.) Select Home, Format dropdown, Protect Sheet.
- Turn on Protection to use the Locked/Unlocked settings.
- Excel will display the Protect Sheet dialog. The default settings are sufficient protection. Simply click OK.
- The default settings are fine.
Now if you accidentally try to enter something in a formula cell, Excel will prevent you from entering the data.