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:

Formatting Cells to Hide Formulas and Display ValuesI 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 (fx). Follow the steps we are showing below:


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

formula to hide and display values

  • 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.

Formatting Cells options to Hide Formulas and Display Values

  • 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.

protect sheet options to hide formulas and display values

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

formula hidden in formula bar and display only values

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.


  • 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.

Opening vba window

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

inserting module in vba window

  • In the VBA editor write down the following codes.
Sub HideFormulasDisplayValues()

With ActiveSheet
.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.

Hide the Formulas and Display Values with VBA

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.


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

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

Get FREE Advanced Excel Exercises with Solutions!
Abrar-ur-Rahman Niloy
Abrar-ur-Rahman Niloy

Abrar-ur-Rahman Niloy, holding a B.Sc. in Naval Architecture and Marine Engineering, has contributed to Exceldemy for nearly 1.5 years. As a leader in Excel, VBA, and Content Development teams, he authored 114+ articles and assisted the Exceldemy forum. Presently, as a project writer, he prioritizes stepping out of his comfort zone, aiming for constant technical improvement. Niloy's interests encompass Excel & VBA, Pivot Table, Power Query, Python, Data Analysis, and Machine Learning libraries, showcasing his commitment to diverse... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF