Protecting Cells Containing Formulas in Protected Sheet in Microsoft Excel 2010

In this article, we will learn protecting cells that contain formulas in a protected sheet in Microsoft Excel 2010.

The Protecting cells function in a sheet is used where you do not want to view or delete the formulas. A common way to perform this task is by protecting the worksheet that will not let the end-user do any activity in the sheet that you have created.

But there are specific requirement where you want to protect your formulas along with the flexibility for the users to change the information as per their requirement. At times, you want both flexibility & protection of important formulas.

By default, all cells in the worksheet are locked. There is a simple way to apply protection on worksheet so that only formula cells remain locked and protected.

Let us take an example:

We have Car Sales data
We have performed some simple calculation below
We want to protect the cells that contain formulas

Select all cells by pressing CTRL + A + A
Press CTRL + 1 to launch Format cells

Click on Protection tab
Click on Locked box

Click on OK
Press F5 or CTRL + G for “Go To Special” command
Click on Special
Select Formulas

Click on OK
This will highlight the cells that contain formulas
Click on Home ribbon
Click on Format & then click on Lock

Go to Data ribbon
Click on Data Validation

In Settings tab, click on Allow

Select Custom, and in Formula, enter =””

This will prevent the other users to enter their own formula
If they want to change the formula say change the Total Sales formula to find out the Total Sales for Florida region in January Month