How to Hide Formula in Excel without Protecting Sheet (2 Methods)

This tutorial will explain how to hide the formula in excel without protecting the sheet. While working in Microsoft Excel we might need to share our excel files with other users. We can protect our worksheet with a password so that the user will not be able to edit anything.

But, despite protecting the worksheet, users can click on a cell and see what formula we have used in that cell. We can hide formulas by protecting the sheet also but in this tutorial, we will hide formulas from users without protecting the sheet.


Download Practice Workbook

We can download the practice workbook from here.


2 Methods to Hide Formula in Excel without Protecting Sheet

Throughout this article, we will explain 2 methods of how to hide the formula in excel without protecting the sheet. To illustrate those methods we will use the dataset that is provided in the following screenshot. The dataset consists of sales of six salespeople in January and February. Then, we can see the total sales in another column.

2 Methods to Hide Formula in Excel without Protecting Sheet


1. Use VBA Code to Hide Formula in Excel without Protecting Sheet

If we want to hide formulas in excel from our users without protecting the sheet, the use of VBA code is the most effective way. We will use the following dataset to illustrate this method.

Use VBA Code to Hide Formula in Excel without Protecting Sheet

Just go through the following steps to understand this method clearly.

  • If we click on cell E5 we can see the following formula in the formula bar:
=C5+D5

Use VBA Code to Hide Formula in Excel without Protecting Sheet

  • Now, users can see the formulas of the following image in the formula bar just by clicking on that cell.

We want to limit this access. We will apply a VBA code so that the users will only be able to see the data but will not be able to see what formula we are using.

Use VBA Code to Hide Formula in Excel without Protecting Sheet

Let’s see the steps to perform this action.

STEPS:

  • To begin with, right-click on the sheet name. Select the option ‘View Code’.

Use VBA Code to Hide Formula in Excel without Protecting Sheet

  • The above command will open a blank VBA module.
  • In addition, insert the following code in the blank VBA module:
Dim zDic As New Dictionary
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim zCell As Range
Dim zRg As Range
Set zRg = Range("E5:E10")
If zDic.Count <> zRg.Count Then
For Each zCell In zRg
zDic.Add zCell.Address, zCell.FormulaR1C1
Next
End If
If (Target.Count = 1) And (Not Application.Intersect(zRg, Target) Is Nothing) And (Target.HasFormula) Then
With Target
.Value = .Value
End With
Else
For Each zCell In zRg
zCell.Formula = zDic.Item(zCell.Address)
Next
End If
End Sub

Use VBA Code to Hide Formula in Excel without Protecting Sheet

  • Furthermore, go to the Tools and click on the option References.

  • A new dialogue box named ‘References – VBAProject’ will appear.
  • Next, check the ‘Microsoft Scripting Runtime’ box from the ‘Available References’ section.
  • Then, click on OK.

  • After that, press Alt + Q to close the VBA module.
  • Lastly, click on cell E5. We can see that the formula bar is showing only the value instead of the formula

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


2. Hide Formula Bar to Conceal Formula in Excel without Protecting Sheet

Another easy trick to hide formulas in excel without protecting the sheet is to hide the formula bar from the excel ribbon. In the following figure, we can see that, if we select cell E5 the formula of that cell becomes visible in the formula bar.

Hide Formula Bar to Conceal Formula in Excel without Protecting Sheet

Let’s see the steps to hide the formula bar.

STEPS:

  • First, go to the File tab.

Hide Formula Bar to Conceal Formula in Excel without Protecting Sheet

  • Next, select Options.

  • Then, the above command will open a new dialogue box named ‘Excel Options’.
  • After that, select the Advanced option from that box.
  • Furthermore, uncheck the ‘Show formula bar’ box from the Display section and click on OK.

Hide Formula Bar to Conceal Formula in Excel without Protecting Sheet

  • Finally, the formula bar disappears from the ribbon. Now, if we select any cell that contains a formula, the formula will not appear in the formula bar.

Read More: Hide Formulas and Display Values in Excel (2 Ways)


Find Cells with Formulas in Excel

Suppose we have hidden the formulas of our worksheet. Now we just want to know which cells contain formulas. But we do not want to reveal the formulas. In the following dataset, we will figure out which cells contain formulas.

How to Find Cells with Formulas?

Let’s see the steps of this method.

STEPS:

  • Firstly, go to the Home tab.
  • Secondly, select the option ‘Find & Select’ from the excel ribbon.
  • Thirdly, select the option Formulas from the drop-down menu.

How to Find Cells with Formulas?

  • Lastly, we can see the cells (E5:E10) that contain formulas are selected in our dataset.


Conclusion

In conclusion, in this article, we have shown how to hide formulas in excel without protecting the sheet. Download the practice worksheet that comes with this article to put your skills to the test. If you have any questions, please leave a comment in the box below. We’ll do our best to respond as soon as possible. Keep an eye out for more intriguing Microsoft Excel solutions in the future.


Related Articles

Mukesh Dipto

Mukesh Dipto

Hi there! I am Mukesh Dipto. I graduated with a bachelor's degree in engineering. Currently, I am working as a technical content writer in ExcelDemy. You will find all my articles on Microsoft Excel on this site. Outside of the workplace, my hobbies and interests include sports and meeting new people. I also enjoy sports. My favorite sports are football (to watch and play) and Badminton (play).

2 Comments
  1. I am a beginner to learn excel VBA. I’ve just started and I am so lucky to have found this great site. Thank you and please keep going.

    About the method of hiding formula without protecting sheets. I followed and it worked.
    However, when I click on the cell with the formula hidden, I still see the formula for a blink before it is converted to a value. Furthermore, if I click and hold the mouse on that cell, the formula is displayed as normal until I release the mouse pointer.

    Can you please update the code to fix this?
    Thank you so so much.

    • Hello Ruby,
      Thanks for your query. If you do not want to see the formula for a blink then you have to do it by protecting the worksheet. You can try the following code:
      Sub HideFormulasDisplayValues()
      With ActiveSheet
      .Unprotect
      .Cells.Locked = False
      .Cells.SpecialCells(xlCellTypeFormulas).Locked = True
      .Cells.SpecialCells(xlCellTypeFormulas).FormulaHidden = True
      .Protect AllowDeletingRows:=True
      End With
      End Sub

Leave a reply

ExcelDemy
Logo