How to Hide Formulas from Other Users in Excel (2 Ways)

In many working sectors in our lives, we may want to hide our data from other people. For example, during many presentation and data sharing occasions, we just want to present the findings, but how we got the findings or the formulas behind those findings may be confidential, and we don’t want to reveal those data. Unfortunately, whenever we hover over the Formula Bar or select one of the cells containing formulas, it shows us the formula in the formula bar, which is pretty inconvenient. In this tutorial, you will learn how to hide formulas from other users in Excel.

Below I am going to show two easy methods by which you can easily hide your formula in the Formula Bar and also from other users.

How to Hide Formulas from Other Users in Excel: 2 Ways

The data that we are going to use is below here, In this dataset, we kept Region Rep(representative), Item units, and totals in columns serially from left to right.

1. Hide Formulas From Other Users Using Protection On Entire Sheet

Step 1: Make sure your worksheet is in a lock state.

In this process, you need to make sure that your worksheet is in a locked state. But sometimes your worksheet cells may not be in lock state. To avoid finding which one is locked and which one is not locked, it’s better to select the whole worksheet cells unlock them, and then lock them all again.

• First, check your Formula Bar, and ensure that your formulas in the Formula Bar showing properly.

• Then select the entire worksheet by pressing the Select All button(left corner most button in the worksheet).

• Go to Format in the Home tab, then click on Format Cell.

• Next, on the format page, you need to go to the Protection tab, if the locked option is unchecked, toggle to check.

• If both of the options are being checked, then uncheck it, after that go through step 2 again.

• Next, go to the Protection tab, check both the Locked and Hidden boxes, and press OK.

Step 2: Hide Formula In Excel Worksheet Using Protect Sheet Option

After step 3, the whole worksheet is now locked and hidden. But it will not be fully practical as the  Protect Sheet option isn’t in the active state right now.

• From the Home tab, go to the Format again.
• Select the dropdown menu and select the Protect Sheet option.

• After selecting Protecting Sheet, there should be a dialog box like the one below should appear.
• Then check what you want to allow other users to do with your worksheet. In this case, just the first two boxes will be sufficient.
• Up next enter a suitable password and click on OK.
  
• After typing your password, another dialog box will appear to confirm your password. After reconfirming, click on OK

• Upon confirming passwords, notice formula bar in your worksheet is no longer showing any formulas.

2. VBA to Hide Formulas from Other Users in Excel

A couple of simple VBA macros can make this whole process seamless. Utilizing macros is quite hassle-free and time-saving.

Step 1: Launch the Visual Basic Editor from the Developer tab. If you don’t get the tab, you will have to enable it first.

• Visual Basic can also be activated by pressing Alt + F11 on your keyboard.

Step 2: Right after you launch the VBA editor, a new window will open.

• In this new window, click Insert, then click Module.

Step 3: Next a white editor will open. In that editor, you need to write the following code.

Sub hidefromulafromotheruser()
With ActiveSheet
.Unprotect
.Cells.Locked = False
.Cells.SpecialCells(xlCellTypeFormulas).Locked = True
.Cells.SpecialCells(xlCellTypeFormulas).FormulaHidden = True
.Protect AllowDeletingRows:=True
End With
End Sub

Step 04: Upon writing the code, close both the Module and the VBA editor.

Step 05: From the View tab, click the Macros command, then select the  View Macros option.

Step 6: Next a new dialog box will open, from that dialog box, select the macro that you just created and click Run.

Upon clicking Run, you no longer will see any formula in the Formula Bar.

We can also check the Format tab for further confirmation. This grey unprotect sheet option verifies that our worksheet formula hidden macros are working fine.

Download Practice Workbook

Download the practice workbook from the below button, and use the password 123456hideformula when asked.

Conclusion

To sum it up, the question we answered is “How we can hide our formula from other users“in two principal ways. One is by using a built-in Protect Cell command and another one is to run a small macro in the VBA editor. The VBA process is less time-consuming and simplistic but requires prior VBA-related knowledge. Other method doesn’t have such a requirement. For this problem, we attached where you can practice and get used to these methods. Feel free to ask any questions or feedback through the comment section.

Related Articles

• How to Hide Formula in Excel without Protecting Sheet
• How to Hide Formulas and Display Values in Excel
• How to Hide Formula but Allow Input in Excel
• How to Hide Formulas in Excel until Data Is Entered

<< Go Back to Hide Excel Formulas | Excel Formulas | Learn Excel