Hide Excel Formulas (with Protecting Sheet & Without It)

This article covers the application of Format Cells dialog box and Review tab to hide Excel formulas. Also, we show the use of VBA code to do the same thing. But these two methods require protecting sheet. For your convenience, we have demonstrated hiding Excel formulas without protecting sheet.

Hiding Excel formulas is a useful technique employed to safeguard sensitive information and maintain the integrity of spreadsheet data. While Excel formulas are powerful tools for performing calculations and automating tasks, there are situations where it becomes necessary to conceal them from view. Whether you are protecting proprietary formulas or preserving confidentiality, this guide delves into different strategies and techniques to effectively hide Excel formulas, ensuring that your data remains secure while maintaining the desired level of transparency.

Download Practice Workbook

Hide Excel Formulas.xlsm

Hide Excel Formulas: 3 Suitable Ways

1. Hide Excel Formula Using Format Cells Option with Protecting Sheet

1.1 Hide Manually Selected Cells Containing Excel Formulas

First, we need to select the column where we want to hide the formula.
Then, right-click on selected cells and select Format Cells….

Next, go to the Protection tab and check the box before the Hidden After that, press OK.

Now, select Review >> Protect Sheet.

Following that, choose your password as you wish and press OK. You can protect anything you want in the Protect Sheet.

Now, you will see that the Excel formulas are hidden.

1.2 Hide Automatically Selected Cells Containing Excel Formulas

First, select the whole sheet.

Now, we have to search for cells with formulas.
So, select Home >> Editing >> Find & Select.

Then select Go To Special….

Next, check the circle before the Formulas option and (also keep the boxes of the Numbers, Text, Logicals, Errors options selected).
Finally, press OK.

Now, you will see that the cells having formula are selected.

Now press Ctrl + 1 to get Format Cells window, and check Hidden and Locked
Then press OK.

Select Protect Sheet from the Review tab.

Then press OK in the Protect Sheet.

Now, you can see that formula is hidden.

2. Hide Excel Formulas Using VBA with Protecting Sheet

At first, you need to Select ‘Alt+F11’ in order to run the VBA editor. If there is no Developer tab, you can adjust it by customizing the ribbon.
After opening the VBA editor, click on ‘Insert’.

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

In the opened Module window, type 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.
Then click on the ‘View’ tab and go to the ‘Macros’

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

After using VBA codes, you can protect your worksheet with the cell formula hidden.

3. Hide Excel Formulas Without Protecting Sheet

First, we have to right-click on the worksheet and select View Code to add code for that worksheet.

Then, you have to write the code for hiding the formulas.

You can copy the code from here.

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

Dim iDictionary As New Dictionary

This line declares a new dictionary object called iDictionary. The Dictionary class allows you to store key-value pairs.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

This line defines a private subroutine called Worksheet_SelectionChange, which is an event handler that gets triggered whenever a different cell or range is selected on 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 than 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

This piece of code is to check 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 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.

Now, we will select Tools and select References….

Then, we have to check Microsoft Scripting Runtime from References-VBAProject

Now, when we select any cells with formulas, it will hide the formula.

How to Unhide Formulas in Excel

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

Then you can see the formulas again.

How to Show Formulas in Excel

Right-click on cells, and you will see the formula.

Things to Remember

Do not forget to select Protect >> Protect Sheet from Review Otherwise, it will not hide the formulas.
Always review and recheck your formula. Otherwise, it can cause errors in your whole worksheet. Because suppose you protected the worksheet with the wrong formula and the formula is hidden. Nobody will understand and find out what is wrong. So in those cases, worksheet will be discarded. So, recheck the formula.

Conclusion

To summarize, hiding Excel formulas helps protect sensitive information and improve the appearance of your spreadsheet. You can hide formulas by changing cell formatting, using conditional formatting, or applying for worksheet or workbook protection. Remember to test and verify the accuracy of hidden formulas and consider potential risks, such as accidental changes or unauthorized access. By understanding these methods, you can ensure data security while maintaining transparency in your Excel spreadsheets.

Frequently Asked Questions

1. Can I still use hidden formulas in my spreadsheet?

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.

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 themselves does not significantly impact the performance of your spreadsheet. However, if your spreadsheet contains complex calculations or a large number of hidden formulas, it may still have an impact on overall spreadsheet performance. It’s always a good practice to optimize your formulas and keep the spreadsheet size manageable for optimal performance.

Hide Excel Formulas: Knowledge Hub

<< Go Back to Excel Formulas | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!