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

Method 1 – Formatting Cells to Hide Formulas and Display Values in Excel

In the sample dataset, we 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).

Steps

Select the cell/cells to modify, in this case, it is the total sales cell C8.

Go to Home tab, select Number group and click on the dialog box launcher.A new Dialog box for formatting will pop up.

In the “Format Cells” box, select the Protection Check the “Hidden” option. Click OK.

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

You will not see any formula in the formula box, but the value will be displayed in the cell.

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

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.

Select “Insert” and click on “Module”. This will create a new module.

In the VBA editor, enter 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