How to Write Protect Specific Cells or Formulas in Your Worksheet

It is natural that all of us want to protect cells with important values. In this article, we will explore the feature of cells protection in Excel.

The cells protection in Excel will take effect when the worksheet is protected. You can lock a cell or hide a cell to protect it. But the different options will take different effects, which depends on whether the value in a cell is an ordinary content or a formula. Therefore, in the following sentences, we will find how the cells protection works.

Cells Protection to Ordinary Cells

Before you protect the worksheet, you may check whether the cell is locked.

1. Right click the target cell. Here we click the cell B2.
2. And then in the menu, choose the option “Format Cells”.
3. In the “Format Cells”, choose the tab “Protection”.
4. Make sure that the option “Locked” has been checked.
5. And then click “OK”.
6. Now protect the worksheet.

Therefore, if anyone wants to change the cell, they will see this dialog:

Suppose the option in step 4 is unchecked. If you double click the cell or press “F2”, you can still edit this cell. Therefore, this is exactly how the cells protection works.

You may also see that there is another option “Hidden” in the “Format Cells” window. This option will take effect when the cell contains a formula.

Cells Protection to Cells with Formulas

If the cell contains a formula, the effect of cells protection will be a little different. In the image below, we use the SUM function to calculate the total sales volume.

Here if you check the option “Hidden” in the “Format Cells”, you can still see the numbers in the cell. This is because the original formula is hidden in the cell. And Excel only shows the result of the formula. If you want to use the feature of “Hidden”, you need to make further settings.

1. Click the tab “Formula” in the ribbon.
2. And then click the button “Show Formulas”.

Therefore, the formula will appear in the worksheet.

3. Now check the option “Hidden” in the “Format Cells” window.
4. And then protect the worksheet. Therefore, you cannot see the formula in the cell.

We didn’t check the “Hidden” for cell C14. Thus, this formula in C14 still appears in the protected worksheet. Now you know how the option “Locked” takes effect in cells with formulas.

Protect Excel by Third-Party Tools

Except for the protection that Excel itself has provided, you can also use other tool to protect it. One way is preparing an Excel fix tool at hand. Thus, whenever you meet with Excel corruption, you can calmly treat with those issues.

Author Introduction:

Anna Ma is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including word recovery and outlook repair software products. For more information visit www.datanumen.com