Locking Formula Cells

By default, all cells on a worksheet are locked; however, this has no effect unless worksheet protection has been applied. Here is a very easy way to apply worksheet protection so that only formula cells are locked and protected.

Select all cells on your worksheet, either by pressing Ctrl-A or by clicking the square at the intersecting point of column A and row1. Then select Home → Format → Lock Cell (under Cells options, toggles to Lock/unlock cells), or if you prefer you can right-click and select Format Cells and on the Protection tab, uncheck the Locked checkbox to remove the tick (pre-2007, select Format → Cells → Protection and uncheck the Locked checkbox to remove the tick). Click OK.

Nowselect any single cell, select Home → Find & Select → Go To Special or Ctrl-G, or F5 and click Special (pre-2007, Edit → Go To... → Special).

Select Formulas from the Go To Special dialog and, if needed, limit the formulas to the subtypes underneath. Click OK. With only the formula cells selected, under Cells options choose Format → Lock Cell (pre-2007, select Format → Cells → Protection, check the Locked checkbox to insert a tick), and click OK.

Nowyou need to protect your sheet. Select Format → Protect sheet under the Cells options (pre-2007, Tools → Protection → Protect Worksheet). Apply a password if required and click OK.

The preceding method certainly saves a lot of time and eliminates possible errors locating formulas so that you can protect them. Unfortunately, it can also prevent users from using certain features, such as sorting, formatting changes, aligning text, and many others you might not be concerned with, even when in an unlocked cell.