• Excel Book Excerpt

Excel Protect Cells That Contain Formulas

This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

Protect Cells That Contain Formulas

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?

LE10000744.jpg

Figure 721 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:

1. Select all cells by pressing Ctrl+A. Use Ctrl+One to open Format Cells.

2. Click on the Protection tab in the Format Cells dialog. You will see that the Locked option is chosen.

LE10000745.jpg

Figure 722 All cells start out locked by default.

3. Uncheck the Locked box. Click OK to close the Format Cells dialog.

4. With all the cells still highlighted, select Home, Find & Select, Go To Special.

5. On the Go To Special dialog box, choose the Formulas option button.

LE10000746.jpg

Figure 723 Select only formula cells.

6. Click OK to close the Go To Special dialog. Excel will reduce the selection to only cells with formulas.

7. Select Home, Format dropdown, Lock Cells. This will lock only the selected cells, which are the formula cells.

8. 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.

LE10000747.jpg

Figure 724 Turn on Protection to use the Locked/Unlocked settings.

9. Excel will display the Protect Sheet dialog. The default settings are sufficient protection. Simply click OK.

LE10000748.jpg

Figure 725 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.

<-Previous Topic Next Topic->

For more resources for Microsoft Excel:

privacy policy