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

Method 1 – Hide Formulas From Other Users Using Protection On Entire Sheet

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

• Check your Formula Bar and ensure that your formulas in the Formula Bar are showing properly.

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

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

• 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. Go through step 2 again.

• Go to the Protection tab, check both the Locked and Hidden boxes and press

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

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

• After selecting Protecting Sheet, there should be a dialog box like the one shown below should appear.
• Check what you want to allow other users to do with your worksheet. In this case, just the first two boxes will be sufficient.
• Enter your desired password and click on OK.
  
• After entering your password, another dialog box will appear to confirm your password. After confirming, click on OK.

• On confirming passwords, you will notice that formula bar in your worksheet will no longer show any formulas.

Method 2 – VBA to Hide Formulas from Other Users in Excel

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: After you launch the VBA editor, a new window will open.

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

Step 3: In the editor, enter 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: Close both the Module and the VBA editor.

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

Step 6: In the new dialog box, select the macro that you created and click Run.

On 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 is protected.

Download Practice Workbook

Download the practice workbook and use the password 123456hideformula when asked.

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