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

In many working sectors in our life, 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. But 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.


Download Practice Workbook

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


2 Ways to Hide Formulas from Other Users in Excel

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 lock state. But sometimes your all 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 reassure that your formulas in the Formula Bar showing properly.

Hide Formulas From Other Users Using Protection On Entire Sheet

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

Hide Formulas From Other Users Using Protection On Entire Sheet

  • 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, then 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 being 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 Protect Sheet option.

Hide Formulas From Other Users Using Protection On Entire Sheet

  • 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.

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

VBA to Hide Formulas from Other Users in Excel

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

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

VBA to Hide Formulas from Other Users in Excel

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

VBA to Hide Formulas from Other Users in Excel

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.

VBA to Hide Formulas from Other Users in Excel

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

VBA to Hide Formulas from Other Users in Excel

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

VBA to Hide Formulas from Other Users in Excel

 

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.

Read More: How to Hide Formula in Excel Using VBA (4 Methods)


Conclusion

The 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. Any suggestion for the betterment of the Exceldemy community will be highly appreciable.


Related Articles

Rubayed Razib Suprov

Rubayed Razib Suprov

Hi, I am Rubayed Razib Suprov. Graduated from the Bangladesh University of Engineering Technology in Naval Architecture and Marine Engineering department. I joined Exceldemy on March 2022. I have a keen interest in both fluid dynamics and data analysis. I try to teach people what I learned so far about the Excel tool to date, and hope I can make some positive impacts.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo