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


  • Excel will not allow the user to change the formula



Leave a Reply to farhan nazar Cancel reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.