Hide Excel Formulas (with Protecting Sheet & Without It)

This article will cover how to hide Excel formulas, either by using the Format Cells and Review features, or with a VBA macro. Since these methods require protecting the sheet, we’ll also demonstrate how to hide Excel formulas without protecting the sheet using VBA code.

Download Practice Workbook

Hide Excel Formulas.xlsm

Hide Excel Formulas: 3 Suitable Ways

Method 1 – Using the Format Cells Option to Hide Excel Formulas (Protected Sheet)

1.1 – Hiding Manually Selected Cells Containing Excel Formulas

Steps:

Select the column where we want to hide the formulas.
Right-click on the selected cells and select Format Cells… from the context menu.

The Format Cells dialog box opens.

Go to the Protection tab and check Hidden.
Click OK.

Go to the Review tab and click on Protect Sheet.

Enter a password of your choice and click OK.
Tick any elements that you would like to unprotect (i.e. allow users to access despite sheet protection).

The Excel formulas are hidden.

1.2 – Hiding Automatically Selected Cells Containing Excel Formulas

Steps:

Select the whole sheet.

Now, we’ll find and select the cells with formulas.

Go to Home >> Editing >> Find & Select.

Select Go To Special….

Select the Formulas option.
Keep all the sub-options ticked.
Click OK.

All the cells containing a formula are selected.

Press Ctrl + 1 to open the Format Cells window.
Check Hidden and Locked.
Click OK.

Select Protect Sheet from the Review tab.

Click OK in the Protect Sheet window.

The formulas are hidden.

Method 2 – Using VBA to Hide Excel Formulas (Protected Sheet)

Steps:

Select ‘Alt+F11’ to open the VBA editor.
In the VBA editor, click on ‘Insert’.

From the ‘Insert’ options, select ‘Module’.

In the Module window that opens, enter the following VBA code:

Sub LockAndHideAllCellsWithFormulas()

With ActiveSheet
.Unprotect
.Cells.Locked = False
.Cells.SpecialCells(xlCellTypeFormulas).Locked = True
.Cells.SpecialCells(xlCellTypeFormulas).FormulaHidden = True
.Protect AllowDeletingRows:=True
End With

End Sub

Minimize the Module window and editor.
Go to the ‘View’ tab and select ‘Macros’.

Select the given ‘Macro Name’ and click ‘Run’.

The worksheet is protected, and the cell formulas are hidden.

Method 3 – Using VBA Code to Hide Excel Formulas Without Protecting the Sheet

Steps:

Right-click on the worksheet and select View Code to add code in that worksheet.

Enter the code below in the Module window that opens:

Dim iDictionary As New Dictionary
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim iCell As Range
Dim iRange As Range
Set iRange = Range("B5:G11")
If iDictionary.Count <> iRange.Count Then
    For Each iCell In iRange
        iDictionary.Add iCell.Address, iCell.FormulaR1C1
    Next
End If
If (Target.Count = 1) And (Not Application.Intersect(iRange, Target) Is Nothing) And (Target.HasFormula) Then
    With Target
        .Value = .Value
    End With
Else
    For Each iCell In iRange
        iCell.Formula = iDictionary.Item(iCell.Address)
    Next
End If
End Sub

Code Breakdown

Code Explanation:

Dim iDictionary As New Dictionary

Declares a new Dictionary object called iDictionary. The Dictionary class stores key-value pairs.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Defines a private subroutine called Worksheet_SelectionChange, an event handler that is triggered whenever a different cell or range is selected in the worksheet. The Target parameter represents the selected range.

Dim iCell As Range

Dim iRange As Range

Set iRange = Range(“B5:G11”)

These lines declare two range variables: iCell and iRange. iRange is set to the range “B5:G11”.

If iDictionary.Count <> iRange.Count Then

For Each iCell In iRange

iDictionary.Add iCell.Address, iCell.FormulaR1C1

Next

End If

This conditional statement checks if the count of items in the dictionary (iDictionary.Count) is not equal to the count of cells in iRange. If the counts are different, it means the dictionary is empty or has different contents from the range. In that case, the code enters a loop and iterates over each cell iCell within iRange. It adds an entry to the dictionary using the cell’s address as the key and the cell’s formula in R1C1 notation as the value. This populates the dictionary with the initial formulas of the cells in iRange.

If (Target.Count = 1) And (Not Application.Intersect(iRange, Target) Is Nothing) And (Target.HasFormula) Then

With Target

.Value = .Value

End With

Checks whether the range holds the formula or not. If the intersection of the range doesn’t fulfil the above condition, then it replaces the value in the cell with the value that it already has.

Else

For Each iCell In iRange

iCell.Formula = iDictionary.Item(iCell.Address)

Next

End If

End Sub

Otherwise, pass the cell address as the argument of each item.

Click on the Tools tab and select References….

Tick Microsoft Scripting Runtime in the References-VBAProject box.
Click OK.

Now, when we select any cells containing a formula, the formula will be hidden.

How to Unhide Formulas in Excel

To unprotect a sheet, go to Review >> Protect >> Unprotect Sheet.

Formulas will be visible again.

How to See Formulas in Excel

Simply right-click on a cell containing a formula, and the formula will be displayed.

Things to Remember

Don’t forget to select Protect >> Protect Sheet from the Review tab, otherwise the formulas will not be hidden.
Always review and recheck your formulas before hiding them. Incorrect hidden formulas will cause incorrect results that are difficult to identify and impossible for anyone without a password to even attempt resolving.

Frequently Asked Questions

1. Will hidden formulas in my spreadsheet still work?

Yes, hiding formulas does not disable their functionality. Even though the formulas are hidden from view, they continue to calculate and update the results in your spreadsheet as normal.

2. Can hidden formulas be seen or modified by others with advanced Excel knowledge?

While casual users may not easily see or modify hidden formulas, those with advanced Excel knowledge and experience may have techniques to uncover or modify hidden formulas. It’s important to be cautious and use additional security measures such as password protection or restricted user permissions to further safeguard your hidden formulas.

3. Will hiding Excel formulas affect the performance of my spreadsheet?

Hiding formulas in and of itself does not significantly impact the performance of your spreadsheet. However, if your spreadsheet contains complex calculations or a large number of hidden formulas, this may have an impact on overall spreadsheet performance. It’s always good practice to optimize your formulas and keep the spreadsheet size manageable for optimal performance.