Macro to Hide Formulas in a Worksheet

This VBA macro hides every formula on a sheet so it doesn’t show in the formula bar, while the results stay visible. Other cells stay editable, so people can still enter data.

Works inExcel 2010 and later, Windows and Mac
UndoNot possible. Save a copy first
Save asMacro-Enabled Workbook (.xlsm)

New to macros? See how to use the macros on this site: copy the code, paste it into a module with Alt + F11, and run it with Alt + F8.

Hide formulas and protect the sheet

Sub HideFormulas()
    With ActiveSheet
        .Unprotect
        .Cells.Locked = False
        .Cells.FormulaHidden = False
        On Error Resume Next
        With .Cells.SpecialCells(xlCellTypeFormulas)
            .Locked = True
            .FormulaHidden = True
        End With
        On Error GoTo 0
        .Protect Password:="xlncad"
    End With
End Sub

Change "xlncad" to your own password. Formula cells are locked and hidden; all other cells are unlocked so they can still be edited.

Show the formulas again

Sub ShowFormulas()
    With ActiveSheet
        .Unprotect Password:="xlncad"
        .Cells.FormulaHidden = False
    End With
End Sub

Without a macro

  1. Press Ctrl + A, then Ctrl + 1 → Protection, untick Locked and click OK.
  2. Go to Home → Find & Select → Formulas to select only the formula cells.
  3. Press Ctrl + 1 → Protection, tick Locked and Hidden, and click OK.
  4. Go to Review → Protect Sheet and set a password.

Things to know

  • Sheet protection keeps formulas out of casual view, but it isn’t strong security. Don’t rely on it for truly confidential logic.
  • On Error Resume Next stops the macro failing on a sheet with no formulas.
  • Keep the password safe. Without it, you’ll need the protect and unprotect macros or a backup to get back in.

Related macros