Protecting Cells Containing Formulas in an Unprotected Sheet

 

In this article, to protect cells containing formula in an unprotected sheet, we will use Data validation option.

image 1

 

Data validation is an awesome feature of Excel that you can use to describe limitations or restrictions on what type of data can be used in a cell. You can prevent users from entering data that is not valid. If you favor, you can allow users to enter invalid data & display a warning message when they enter data in the cell. The shortcut key is ALT+D+L.

 

Let’s take an example and understand how we can protect the formula containing cells.

We have data in which some cells are containing the formula for the calculation purpose. Now, we want to protect all those cells in which formulas are applied.

Follow below given steps:-

  • First of all, we will select the cells that contain formula.
  • Go to Home tab > Editing > Find & Select > Formulas

image 2

 

image 3

 

 

Data Validation:-

  • Go to Data tab > Data Validation.
  • Or use ALT + D + L shortcut keys for Data Validation

 

image 4

 

  • Data validation dialog box will open.
  • Settings > Allow > Custom & Formula > =””

image 5

 

  • Click on OK.

When we will enter any text in the cell, it will never change the formula and a pop up will appear.

 

image 6

This is the way we can protect the formula containing cells through data validation in Microsoft Excel.

 

image 4

 

 



3 thoughts on “Protecting Cells Containing Formulas in an Unprotected Sheet

  1. Fabulous! I have been trying to figure out how to protect formulas but allow them to me copied when adding new rows to a costing template.

    How can this Data Validation method be modified so that the formula cannot be deleted?

    It doesn’t allow the formula to be changed or typed over, but it does not prevent the formula from being deleted from the cell.

  2. Hi Reighley,

    Thanks for great tips; But with Data Validation anybody may delete the formula. So is there any other more safe option and how can we hide our formula which is clearly visible in Data Validation.

    Pls. Help………….

  3. To prevent deletion of formula, change the validation formula to “=FALSE” and uncheck the “Ignore Blank” option. BAM! No more deleting formulas.

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>