Sometimes we may need to hide formulas in our Excel worksheet without necessarily protecting the sheet or protecting the cells that have the formulas.

In this tutorial, I will cover three techniques for hiding formulas in Excel without protecting the sheet/cells.

The easiest method would be to remove the formulas and only keep the values. I will also cover how to use an event handler in VBA so that as soon as you enter the formula in a cell, it is automatically converted into value, and the formula is removed.

## Method #1: Convert the Formulas to Values

The simplest way to hide formulas in Excel without protecting the worksheet is to convert the formulas to values.

Suppose we have the following dataset that has formulas in column E.

We want to share the worksheet with some people, but we do not want them to see the formulas in column E.

We can convert the formulas to values before sharing so that they only see the results of the formulas and not the formulas themselves.

Below are the steps to hide the formula and only keep the values:

- Select range E2:E6, which contains the formulas that we want to hide
- Press
**Ctrl + C**to copy the formulas.

- Press
**Ctrl + Alt + V**to open the**Paste Special**dialog box.

Alternatively, you can open the **Paste Special** dialog box by opening the **Home** tab and selecting **Paste Special** in the **Clipboard **group.

- Select
**Values**on the**Paste Special**dialog box that appears and click**OK**.

The formulas are converted to values:

One clear drawback of this method is that once you have removed the formulas and only kept the values, when you make any changes in any of the cells that were used in the formula, the formula will not update automatically.

## Method #2: Use a Sub-procedure in a Standard Code Module

We can use a sub-procedure in a standard code module to hide formulas in Excel without protecting the worksheet.

Imagine we have the following dataset showing the salaries of the employees of a particular bank. The dataset is in Sheet1 of the workbook.

We want to use a sub-procedure in a standard module to calculate the bonuses of the employees (10% of salary).

The formula used to calculate the bonuses will be in the sub-procedure and not in column E.

Below are the steps to use a subroutine in Excel VBA to do the calculation and get the value:

- Activate
**Sheet1** - On the
**Developer**tab, in the**Code**group, click**Visual Basic**to open the**Visual Basic Editor**.

*If you do not see the Developer tab in your ribbon, you can also use the keyboard shortcut Alt + F11 (hold the ALT key and then press the F11 key)*

- In the
**VB Editor**, click on the**Insert**option in the menu and then click on the**Module**option. This will insert a new module for the workbook.

- Copy and paste the following code in the module code window:

```
'Code developed by Steve Scott from https://spreadsheetplanet.com
Sub CalculateBonus()
Dim i As Long, lastRow As Long
lastRow = Sheets("Sheet1").Range(“A” & Rows.Count).End(xlUp).Row
Sheet1.Activate
For i = 2 To lastRow
Cells(i, 5) = Cells(i, 4) * 0.1
Next i
End Sub
```

- Save the macro code by clicking on the save button in the toolbar
- Place the cursor anywhere in the macro code and then press the
**F5**key to execute the code. You can also run the code by clicking the**Run Sub/Userform**green button on the toolbar.

- Click the
**View Microsoft Excel**icon to hide the Project Explorer and go back to the worksheet where you have the dataset.*You can also use the shortcut ALT + F11 to go back to the worksheet*

The results of the formula (and not the formula used in the code) are displayed in the range E2:E6.

*I have created this code for the data set I used in this example. You can modify this code based on your data set*

### Explanation of the sub-procedure

In the procedure:

- Two variables of the Long data type are declared.
- The number of the last row used in the dataset is assigned to the
**lastRow**variable. - Sheet1, which contains the dataset, is activated.
- The
**For…To…Next**construct is used to loop through range E2:E6 assigning the results of the used formula to each cell in the range.

## Method #3: Use a Selection Change Event Handler Sub Procedure in a Worksheet Code Module

We can use a worksheet event handler to hide formulas in Excel without protecting the worksheet.

The event handler triggers whenever any cell in the worksheet is changed. so as soon as you insert a formula in a cell in Excel, you get the result, and the formula is instantly removed, and the cell only gets the static value.

The worksheet event-handler procedure must be in the code module for that worksheet. If we put the code in a standard code module, it won’t work.

Suppose we have the following dataset showing the salaries of the employees of a particular bank. The dataset is in **Sheet2 **of the workbook.

We want to enter an event handler code in the code module of Sheet2 so that whenever we enter a formula in the cells in the range E2:E6, the formula is converted to a value.

We proceed as follows:

- Right-click the tab of Sheet2 and select
**View Code**on the shortcut menu that appears.

- In the worksheet module that appears, type in the following code:

```
'Code developed by Steve Scott from https://spreadsheetplanet.com
Option Explicit
Dim myDict As New Dictionary
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim myCell As Range, myRng As Range
Set myRng = Range("E2:E6")
If myDict.Count <> myRng.Count Then
For Each myCell In myRng
myDict.Add myCell.Address, myCell.FormulaR1C1
Next
End If
If (Target.Count = 1) And (Not Application.Intersect(myRng, Target) _
Is Nothing) And (Target.HasFormula) Then
With Target
.Value = .Value
End With
End If
myDict.RemoveAll
End Sub
```

- Now that the code can work, do the following:

- Open the
**Tools**menu and select**References**:

- In the
**References – VBAProject**feature that appears, scroll down in the**Available References**box and select the**Microsoft Scripting Runtime**option and click**OK**.

- Click the
**View Microsoft Excel**button on the toolbar to switch to the active worksheet containing the dataset.

Alternatively, press **Alt + F11** to switch to the active worksheet.

- Select cell E2 and type in the following formula:

=D2*0.20

- Press Enter. Notice that when you press Enter, the event handler is triggered and converts the formula to a value:

- Enter the following formulas:

- In cell E3 enter D3*0.15
- In cell E4 enter D4*0.12
- In cell E5 enter D5*0.11
- In cell E6 enter D6*0.1

Notice that we were able to enter different formulas in the cells, and the event handler converted each formula to a value.

This would also work in case you enter the formula in one cell and then drag the fail handle to enter the formula in all the remaining cells in the column

### Explanation of the event handler

- A module-wide collection variable of Dictionary data type is declared.
- We use the worksheet
**SelectionChange**procedure. - Two variables of the Range object type are declared.
- Range E2:E6 is assigned to the myRng variable using the
**Set**keyword. In case you’re entering a formula in some other range, you can change that here - The
**For…Each…In…Next**construct is used to add values to the**myDict**variable. The myCell Adress is the key and myCell.FormulaR1C1 is the content. - The procedure checks that the targetted cell in range E2:E6 has a formula and then converts the formula to a value.
- At the end of the sub-procedure, all the values entered in the myDict variable are removed.

In this tutorial, we looked at three techniques we can use to **hide formulas in Excel without protecting the worksheet**.

We hope you found the information helpful.

**Other Excel articles you may also like:**

- How to Remove Formulas in Excel (and keep the data)
- Lock Cells in Excel (Shortcut)
- Drag Down Formula in Excel (Shortcut)
- How to Translate in Excel? (Text/Formulas)
- Formula Bar in Excel – Show/Hide Formula Bar!
- How to Hide Rows based on Cell Value in Excel
- Show Formulas (Instead of Formula Result) in Excel (Shortcut)
- Excel Showing Formula Instead of Result (How to FIX!)