Stealth Formulas
Have you ever wondered how you could work some magic in your Excel sheet such that you could include formulas, yet your cell appears empty? Have you pondered on making your Excel worksheets more secure by protecting your formulas from being changed or seen? If so, this blog post is for you! We will dive deep together into the world of hiding and locking Excel formulas and worksheet protection.
Video
Tutorial
The beauty of Excel is its ability to keep complex calculations hidden under the hood. At times, we may want our formulas concealed, either for aesthetic purposes or to stop users from altering them. Yet, when a cell gets selected, we usually see the formula displayed, right? Well, in this article, we approach the challenge by focusing on the hidden and locked cell format attributes and how to protect the sheet to enforce them. Let’s get started!
Exercise 1: Hiding The Formulas
Our first exercise shows the first step in hiding a formula in a cell.
For example, typically, when you select a cell that contains a formula the formula is displayed in the formula bar:
It is also displayed in the cell when you begin to edit it.
So, what if we want to retain the formula, but tell Excel to NOT display it in the formula bar when selected? Well, begin with these steps:
- Select the cell containing the formula you want to hide.
- Open the Format Cells dialog box (Home > Format > Format Cells).
- Within the “Protection” tab, you will see two checkboxes: locked and hidden. Check the “hidden” checkbox.
- Click OK to apply the changes.
We immediately go to confirm our formula is hidden, and to our surprise, the formula continues to be displayed in the formula bar!!!! So like … what is going on here!?!
No, Excel isn’t malfunctioning. By design, the “hidden” attribute isn’t enforced until we protect the worksheet. So, let’s tackle that in the next exercise.
Exercise 2: Protecting The Worksheet
Our second exercise solves the mystery of applying the “hidden” feature.
The hidden attribute isn’t enforced until we turn on worksheet protection. To do so:
- Review > Protect Sheet
- You can accept the defaults in the resulting Protect Sheet dialog
- Or, you can optionally set a password that would be required to unprotect the sheet
- Click OK
Now when we select the formula cell, the formula is NOT displayed in the formula bar:
So, we managed to hide the formula. But, what if we want to prevent users from changing it? This brings us to our third exercise.
Exercise 3: Locking The Formulas
In our final exercise, we learn about locking our cell to prevent a user from changing it.
To lock a cell, we will want to ensure the Locked checkbox is checked in the Format Cells > Protection tab.
Just like the Hidden checkbox, the Locked checkbox isn’t enforced unless worksheet protection is enabled. We can enable it with Review > Protect Sheet.
Now, when someone tries to change the cell, they will receive an error message:
You can use Locked or Hidden or both depending on what you are working on.
To remove worksheet protection, Review > Unprotect Sheet.
Conclusion
By utilizing Excel’s hidden and locked attributes along with worksheet protection, we can easily hide our formulas in cells and protect their integrity. You can also include a password to enforce stricter protection if desired. Please note that when you set a password in the Protect Sheet dialog, that password will be needed to unprotect the sheet (it won’t be prompted if you try to change the cell value).
I hope this will help you increase the security of your worksheets a bit!
File Download
Download an example Excel file if you’d like to experiment.
FAQs
Here are some frequently asked questions that will enhance your understanding of these features:
Q: Is there a way to apply the hidden attribute without protecting the worksheet?
A: You can apply it, but it won’t be enforced until you protect the sheet.
Q: Can users see the formula if the worksheet is protected but the cell isn’t hidden?
A: Yes, the formula will show in the formula bar if the cell isn’t hidden.
Q: Is it possible to set a password for unprotecting the worksheet?
A: Yes, you can set a password when protecting the worksheet in the Protect Sheet dialog.
Q: Can users overwrite the formula if the worksheet is protected?
A: If the worksheet is protected AND the cell is Locked, Excel presents an error when a user attempts to change the cell.
Q: Can users remove the worksheet protection?
A: Yes, users can use the Review > Unprotect Sheet command. If a password is set, it will be requested when trying to unprotect the sheet.
Q: If a cell is locked, does it mean it’s also hidden?
A: Not necessarily, you can choose to only lock a cell but not hide it.
Q: Can you protect specific cells or areas of a worksheet?
A: Yes, you can specify which cells or areas to protect.
Q: What can users do despite the worksheet being protected?
A: The protect sheet dialog box lets you select what users can do, such as formatting, editing, or deleting cells.
Q: What is the default for cells?
A: By default, cells are Locked and the worksheet is not protected. This is why you can edit any cell at any time even though it is Locked. However, once you protect the worksheet, the Locked attribute is enforced.
Q: Can you apply hidden and locked attributes to various cells simultaneously?
A: Yes, you can select multiple cells and apply the attributes all at once.
Excel is not what it used to be.
You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.
Want to learn Excel?
Our training programs start at $29 and will help you learn Excel quickly.