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.
Also read: How to Remove Password From Excel? 5 Easy Ways!
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!)
