Abrar-ur-Rahman Niloy
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:
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 (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.
- 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 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.
- 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 “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.
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.
