Protect formula cells but allow access to data cells in Excel 2007 / 2010

By default all cells in a worksheet are locked. I.e when you protect a worksheet every cell in the worksheet will be protected.

To handle cell protection differently then you have to change the default settings for the required cells.

Here is how to protect formula cells but allow access to data cells:

  1. Select all cells in the worksheet by clicking on the top left corner of the sheet.
  2. On the Home tab, Cells group, click Format.
  3. Under Protection click Format cells. The Format Cells window will be shown.
  4. Click on the Protection tab.
  5. Uncheck the Locked and Hidden check boxes, then click OK.

These five steps will make all cells in the worksheet unprotected and can therefor be modified. Next step is to protect the formula cells, and here is how we do it:

  1. Select all cells that have formulas.
  2. On the Home tab, Cells group, click Format.
  3. Under Protection click Format cells. The Format Cells window will be shown.
  4. Click on the Protection tab.
  5. Check the Locked and Hidden check boxes, then click OK.
  6. On the Home tab, Cells group, click Format.
  7. Under Protection click Protect sheet.

Applies to: Excel 2007 , Excel 2010

  1. On the Home tab, Cells group, click Format.
  2. Under Protection click Format cells. The Format Cells window will be shown.
  3. Click on the Protection tab.

How to protect formula cells but allow access to data cells

By default all cells in a worksheet are locked (see image below). I.e when you protect a worksheet every cell in the worksheet will be protected.

protect1

To handle cell protection differently then you have change the default settings for the required cells.

Here is a scenario to protect formula cells but allow access to data cells:

  1. Select all cells in the worksheet by clicking on the top left corner of the sheet.
  2. On the Format menu Click Cells.
  3. Click on the Protection tab.
  4. Uncheck the Locked and Hidden check boxes, then click OK.

These four steps will make all cells in the worksheet unprotected and can therefor be modified. Next step is to protect the formula cells, and here is how we do it:

  1. Select all cells that have formulas.
  2. On the Format menu Click Cells.
  3. Click on the Protection tab.
  4. Check the Locked and Hidden check boxes, then click OK.
  5. On the Tools menu click Protection and select  Protect sheet.

Applies to: Excel 2003

How to protect or unprotect cells in a worksheet

In a previous post we talked about how to protect worksheet elements. When you protect a worksheet, by default all its cells will be locked.

If you want to protect or unprotect some cells selectively then do the following:

  1. Select the cells you want to protect / unprotect.
  2. In the main menu click Format.
  3. Click Cells. The Format Cells window will open.
  4. Click the Protection Tab.
  5. Select the Locked check box to protect the cell or clear the check box to unprotect it.

Tip.

In the Protect tab there is a checkbox named Hidden. This checkbox is applicable for cells with formulas.

Select this checkbox to hide formulas from appearing in the formula bar.

Note.

  1. If the Cells command is dimmed when you are trying to protect cells, then parts of the worksheet may already been locked.

Click Tools —> Protection —> Unprotect Sheet.

  1. When you are done, and in order for your cells to be locked then you must protect the worksheet again through the Tools menu.