Now, even if I go and protect the sheet using the option in the ribbon (Review > Protect Sheet), the cells would not be completely locked and you can still edit the cells. The above steps have disabled the locked property for all the cells in the worksheet. In the Format cells dialog box, click on the ‘Protection’ tab.In the Number group, click on the dialog box launcher.Select all the cells in the worksheet (you can do this by clicking on the gray triangle at the top left part of the sheet).So, we first need to disable the Locked property for all the cells (so that these can’t be protected) Step 1 – Disable the Lock Property for all the Cells We will use this concept where we will disable the locked property for all the cells except the ones that have formulas in it. This also means that if you disable the lock property for a few cells, these could still be edited after you protect the worksheet. Only when both of these happen does a cell truly becomes locked (i.e., can’t be edited). Suppose you have a data set as shown below where you only want to protect the formulas in column D (which has formulas).įor a cell to be protected, it needs to have the ‘Locked’ property enabled, as well as the protection enabled from the ribbon. The remaining of the worksheet would remain open for the user to edit. Unlike the previous method, where we protected all the cells in the worksheet, in this method we would only select the cells that have the formulas and protect these cells. This could be the case when you want the users to input data (such as in a data entry form) but not be able to edit the formula or see it.
In the above method, I showed you how to protect the entire worksheet (including the cells that have don’t have a formula in it).īut what if you don’t want to protect the entire worksheet? What if you only want to protect the cells that have formulas and hide these formulas from the user. Also read: How to Lock Formulas in Excel How to Only Hide Formulas in Excel (And Keep Rest of the Cells Editable)
It’s not straight-forward, but it’s not too hard.īut if you’re working with less tech-savvy users, adding a password should be enough. While this method works fine, you need to know that the sheets/cells that are protected in Excel can easily be unlocked by the user.Īny tech-savvy user can easily break into your protected workbooks (a simple Google search will give them multiple ways to break the protected worksheet). The above steps would protect the entire worksheet in such a way that if you click on a cell that has a value you would see the value in the formula bar, but if you click on a cell that has a formula, no formula would be shown in the formula bar.Īnd since the worksheet is protected, you would not be able to double-click on the cell and get into the edit mode (so the formula is hidden that way as well). In the Protection dialog box, enter the password that would be needed if you want to unlock the worksheet (in case you don’t want to apply a password, you can leave this blank).In the Protect group, click on the Protect Sheet option.In the ‘Format Cells’ dialog box that opens up, click the ‘Protection’ tab.In the ‘Number’ group, click on the dialog box launcher (it’s the small tilted arrow icon at the bottom right of the group).Select the cells in column D that have the formula that you want to hide.Suppose you have a data set as shown below where you have the formula in column D.īelow are the steps to hide all the formulas in column D: When you hide the formulas (as we’ll soon see how), the users won’t be able to edit the cell as well as not be able to see the formula in the formula bar.
By selecting the cell and seeing the formula in the formula bar.By double-clicking on the cells and getting into the edit mode.When you have a formula in a cell, a user can see the formula in two ways: How to Hide Formulas Without Protecting the Worksheet.Step 2 – Enable the Locked and Hidden Property only for Cells with Formulas.Step 1 – Disable the Lock Property for all the Cells.How to Only Hide Formulas in Excel (And Keep Rest of the Cells Editable).