How to Hide Formulas and Display Values in Excel (2 Ways)

Sometimes we need to protect particular cells containing formulas for various reasons and only need the values to be displayed for the user. It is a good idea to hide the formulas from showing in the formula bar for this. I will provide two methods to hide the formulas in selected cell(s) and display values in Excel.

1. Formatting Cells to Hide Formulas and Display Values in Excel

For this tutorial, we are using the following dataset:

I have used the SUM function to calculate the total sales from the upper 3 rows. We can see the formula used in the formula bar (f_x). Follow the steps we are showing below:

Steps

• First, We need to select the cell/cells we want to modify, in this case, it is the total sales cell C8.

• Then, go to the dialog box launcher under the Number group in the Home tab (Or press Ctrl+1 for a shortcut). A new Dialog box for formatting will pop up.

• In the “Format Cells” box, select the Protection tab. Under it, check the “Hidden” option. Click on OK.

• Then go to the Review tab. Under the Protect group select “Protect Sheet”.

• Make sure the “Protect worksheet and contents of locked cells” option is checked. Click on OK.

There we have it. Now you can’t see any formula in the formula box, but the value will be displayed in the cell.

Read More: How to Hide Formula in Excel without Protecting Sheet

2. Using VBA Macro to Hide Formulas in Excel and Display Values

You can use Visual Basic for Application (VBA) to achieve the same thing. To hide all the formulas in the spreadsheet follow the below steps.

Steps

• If you don’t have the Developer tab, see how to show the Developer tab.
• Under the “Developer” tab, select “Visual Basic”. The VBA interface will pop up.

• In VBA Editor, select “Insert” and click on “Module”. This will create a new module.

• In the VBA editor write down the following codes.

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

• Now, select “Macros” from either the “View” tab or the “Developer” tab.

• Select “HideFormulasDisplayValues”(our macro name) from the macros list and click on Run.

This will remove all the formulas in the selected sheet and display only values in the cells.

Note: you have to save the file as Excel Macro-Enabled Workbook (.xlsm extension) to use VBA.

Download Practice Workbook

Download this practice workbook and practice for yourself while going through the article.

Conclusion

These are the two methods to hide formulas and display values. Hopefully, you have found this helpful. If you face any problems or if you have any queries or recommendations, feel free to comment below. Thanks for reading, have a nice day.

Related Articles

• Hide Formula but Allow Input in Excel
• How to Hide Formulas in Excel Until Data Is Entered
• How to Hide Formulas from Other Users in Excel

<< Go Back to Hide Excel Formulas | Excel Formulas | Learn Excel