How to Hide Formulas in Excel (And Protect Your Worksheets)
Let’s say you’ve been building a worksheet to share with your coworkers. When you share it, they’ll be able to see, edit, and change everything that’s in the workbook, including your formulas. It can sometimes be a little too easy to make those changes accidentally. That’s why it’s a good idea to lock and even hide formulas in Excel – that way, you can still share your workbook, but you’ll know that your work is protected.
How to protect your worksheet
First, if you don’t want the people with whom you’re sharing the worksheet to change anything, you can protect the entire worksheet by clicking the Review ribbon tab and selecting Protect Sheet. You can select which actions users are allowed to take while the sheet is protected by checking the desired options.
In practice, I typically accept the default options of Select locked cells and Select unlocked cell. At this point, Excel will basically prevent users from changing anything on the protected worksheet.
There are two enhancements we can make. The first one we’ll tackle is actually hiding the formulas from view. Then we’ll allow users to change some cells (like data input cells) and not other cells (formulas). Let’s start by hiding formulas which, luckily, only takes a few extra steps.
How to hide all formulas in your Excel worksheet
Here are the steps to prevent users from (a) making any changes to the worksheet and (b) viewing formulas when they select a formula cell. That is, when a user clicks a formula cell, they will see an empty formula bar instead of the formula. The formula will still work.
The sheet must be unprotected to perform these steps.
1. Locate the Protect Sheet or Unprotect Sheet button on the Review ribbon tab. If the sheet is already unprotected, you’re all set – if it is protected, be sure to select Unprotect Sheet.
2. Choose the cells whose formulas you want to hide and select Format Cells or Ctrl+1.
3. Choose the Protection tab in the Format Cells dialog box.
4. The Locked checkbox is checked by default, so just check the Hidden box as well. The Hidden option is what hides the formula from the user. The Locked option prevents a user from changing the cell value.
Now … HERE’S THE IMPORTANT PART … the Hidden/Locked attributes aren’t enforced until you turn on worksheet protection. That is, until you use the Protect Sheet command, the Locked and Hidden settings are ignored. This explains why you’ve been able to change cell values even though the cells are Locked by default. 🤔
So … the final step is to protect the sheet by clicking Protect Sheet on the Review ribbon tab. At this point, the user won’t be able to view the formulas (or make any changes to cell values).
So, the final little tweak is to enable the user to change the values in some cells, but not others (for example, your formulas).
What if you want to hide formulas in Excel, but still allow other changes?
You might share workbooks with people and want them to be able to make changes to certain cells – this is pretty common with worksheets involving data entry, statistics, and more. But, if you protect the whole worksheet as shown above, others will essentially be prevented from making changes to any cells.
The way to enable them to make changes to some cells and not others is by unchecking the Locked attribute before protecting the worksheet.
For example, let’s say we want to enable the user to make changes to all but formula cells. To accomplish this, we uncheck the Locked property for all cells except the ones with formulas. Here are the detailed steps for how to do it!
Start by disabling the locked option
1. Select all cells – the fastest way to do this is by using the shortcut Ctrl + A.
2. Open the Format Cells dialog (Ctrl+1).
3. Select the Protection option, and make sure that the Locked box is unchecked.
4. Click OK.
Now that the Locked option has been disabled, users will be able to edit those cells when the sheet is protected.
Now, let’s lock and hide formulas.
Lock all cells with formulas & toggle the Hidden option
1. Start by selecting either one single cell (doesn’t matter which one) or all cells (Ctrl+A).
2. On the Home tab, click the Find & Select command and select Go To Special. Check the Formulas box to select only the cells with formulas.
3. With just the formula cells selected, open the Format Cells dialog (Ctrl + 1).
4. Select the Protection tab, and be sure that the options for Locked and Hidden are checked.
5. Click OK.
6. Protect your worksheet by navigating to the Review ribbon tab and selecting the Protect Sheet option.
Now that the sheet is protected, the locked/hidden attributes are enforced. Since we applied them to the formula cells, only the cells with formulas will be locked. And if a user selects one of the formula cells, the formula will be hidden from the formula bar. The user will be able to edit any of the other cells (since we unchecked the Locked checkbox).
Note that if you add a formula in the future, you’ll need to unprotect the sheet, hide/lock the new formula cell, and then protect the sheet.
Mission accomplished 🙂
Do you have any other tips for keeping your workbooks protected while sharing them? Let us know in the comments!
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?
Access all Undergrad and Masters lessons with a Campus Pass or CPE Pass. Includes on-demand training plus live office hours.