MrExcel.com - Your One Stop for Excel Tips & Solutions

Protect Cells That Contain Formulas

October 28, 2022 - by

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?

With a complex financial statement that has numbers and formulas, you want to protect only the formula cells.
Figure 780. 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.

In the Protection tab of the Format Cells dialog, unselect the Locked checkbox.
Figure 781. All cells start out locked by default.

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

  • 4. Select Home, Find & Select, Formulas.

  • 5. Excel will select all formulas on the current worksheet.

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

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

In the Protection section, choose Protect Sheet....
Figure 782. Turn on Protection to use the Locked/Unlocked settings.

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

The Protect Sheet dialog. Choose the box at the top for Protect Worksheet and Contents of Locked Cells. Skip the password box. You then have a series of checkboxes in a section called Allow All Users Of This Worksheet To: Select Locked Cells Select Unlocked Cells Format Cells Format Columns Format Rows Insert Columns Insert Rows Insert Hyperlinks Delete Columns Delete Rows. There are more items that you can scroll to.
Figure 783. 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.

This article is an excerpt from Power Excel With MrExcel

Title photo by Jon Moore on Unsplash