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

 
img1
 

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

 
img2
 

  • Click on Protection tab
  • Click on Locked box

 
img3
 

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

 
img4
 

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

 
img5
 

  • Go to Data ribbon
  • Click on Data Validation

 
img6
 

  • In Settings tab, click on Allow

 
img7
 

  • Select Custom, and in Formula, enter =””

 
img8
 

  • 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

 
img9
 

  • Excel will not allow the user to change the formula

 

Users are saying about us...

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

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.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube