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

Get FREE Advanced Excel Exercises with Solutions!

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.


Download Practice Workbook

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


2 Ways to Hide Formulas and Display Values Only in Excel

1. Formatting Cells to Hide Formulas and Display Values

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:

Steps

  • 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 Sheet (2 Methods)


2. Hide the Formulas and Display Values with VBA

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.

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

Read More: How to Hide Formula in Excel Using VBA (4 Methods)


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. For more articles like this, keep coming to Exceldemy.

Thanks for reading, have a nice day.


Related Articles

Abrar-ur-Rahman Niloy

Abrar-ur-Rahman Niloy

Hi! my name is Abrar-ur-Rahman Niloy. I have completed B.Sc. in Naval Architecture and Marine Engineering. I have found my calling, if you like, in Data Science and Machine Learning and in pursuing so, I have realized the importance of Data Analysis. And Excel is one excel-lent tool do so. I am always trying to learn everyday, and trying to share here what I am learning.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo