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

Comments

  1. Sherry says:

    I followed the above steps and it worked beautifully except there is one cell that I am selecting that is not staying protected. It is the first cell I highlight. I have tried not selecting it 1st to see if that would make a difference but I am still able to modify that particular cell. Any suggestions? I have Version 2002.

  2. admin says:

    Try this:

    1. Leave the protected cells as is.
    2. Unprotect the sheet to be able to modify.
    3. Select only the cell with the problem.
    4. Repeat the last 5 steps in the post (to apply the protection of formula cells).

  3. danish says:

    sir
    HOw can i lock the particular cell
    Danish

  4. admin says:
  5. Steve Patrick says:

    Hello,

    Do you know how to do this simple approach in Excel 2007?

    Steve