Hide formulas in Excel (A complete guide)

Excel formulas are powerful tools for conducting calculations and data analysis. When a formula resides within an Excel cell, users can access it using two distinct methods: by double-clicking the cell to enter edit mode or by selecting the cell to reveal the formula in the formula bar.

However, if you attempt to protect your worksheet, possibly due to concerns related to confidentiality, security, or other considerations, you'll discover that your formulas can still be accessed using the aforementioned two methods.

Should you wish to conceal these formulas to prevent users from viewing them, this guide is designed to assist you. In this comprehensive walkthrough, we will lead you through the steps to hide formulas in Excel so that they remain invisible, allowing you to retain the functionality of your formulas while keeping them discreet.

Easily hide formulas in Excel Hide formulas and keep specific cells editable Using Excel’s built-in features Using Kutools for Excel Using VBA What if you want to unhide formulas How to identify formula hidden cells and unlocked cells

Video: Hide formulas in Excel

Easily hide formulas in Excel

Here are the steps to prevent users from viewing formulas when they select a formula cell. It's important to note that protecting the worksheet is necessary to hide the formulas, and as a result, no changes are allowed after the worksheet is protected.

Step 1: Select cells containing formulas you want to hide

• To hide specific formulas, select the cells containing these formulas. Tip: If the formulas are in non-adjacent cells or ranges, hold "Ctrl" and select each.
• To hide all formulas in the current worksheet, navigate to the "Home" tab and, within the "Editing" group, click on "Find & Select" > "Formulas" to select all cells with formulas.
• 

Step 2: Enable the Hidden property for the cells with formulas

1. Right click on any of the selected cells and choose "Format Cells" from the context menu to open the "Format Cells" dialog.
   Tip: Alternatively, you can use the shortcut keys "Ctrl" + "1".

   

2. In the "Format Cells" dialog, on the "Protection" tab, check the "Hidden" option and click "OK".

   

3. Note: As indicated in the hint within this dialog box, hiding formulas has no effect until you protect the worksheet. To finalize the process of hiding the formulas, make sure to follow the next step to protect the worksheet.

Step 3: Protect the worksheet

1. Go to the "Review" tab, and select "Protect Sheet".

   

2. In the "Protect Sheet" dialog, enter a password and click "OK".

   

   Tip: In the "Allow all users of this worksheet to:" box, you can choose to permit or restrict the listed actions by checking or unchecking the respective boxes next to each action.
3. The "Confirm Password" dialog box appears asking you to reenter the password. Please retype the password and click "OK".
4. 

Result

Now, when you select a cell containing a formula, you will observe that no formula is displayed in the formula bar.

If you attempt to double-click on the cell, a dialog box will appear instead.

Hide formulas and keep specific cells editable using Excel’s built-in features

In this section, I will illustrate an approach to hiding formulas in your worksheet while preserving the ability to edit specific cells. This will be achieved by configuring cell protection properties and protecting your worksheet as follows:

Step 1: Select cells containing formulas you want to hide

• To hide specific formulas, select the cells containing these formulas. Tip: If the formulas are in non-adjacent cells or ranges, hold "Ctrl" and select each.
• To hide all formulas in the current worksheet, navigate to the "Home" tab and, within the "Editing" group, click on "Find & Select" > "Formulas" to select all cells with formulas.
• 

Step 2: Enable the Hidden property for the cells with formulas

1. Right click on any of the selected cells and choose "Format Cells" from the context menu to open the "Format Cells" dialog.
   Tip: Alternatively, you can use the shortcut keys "Ctrl" + "1".

   

2. In the "Format Cells" dialog, on the "Protection" tab, check the "Hidden" option and click "OK".
3. 

4. Note: As indicated in the hint within this dialog box, hiding formulas has no effect until you protect the worksheet. To finalize the process of hiding the formulas, make sure to follow the next step to protect the worksheet.

Step 3: Disable the Lock property for the cells you want to keep editable

1. Select the cells you wish to keep editable. Tip: You can select non-adjacent cells or ranges by holding down the "Ctrl" key.
2. Press "Ctrl" + "1" to open the "Format Cells" dialog box.
3. Switch to the "Protection" tab, uncheck the "Locked" option and click "OK".
4. 

Step 4: Protect the worksheet

1. Go to the "Review" tab, and select "Protect Sheet".

   

2. In the "Protect Sheet" dialog, enter a password and click "OK".

   

   Tip: In the "Allow all users of this worksheet to:" box, you can choose to permit or restrict the listed actions by checking or unchecking the respective boxes next to each action.
3. The "Confirm Password" dialog box appears asking you to reenter the password. Please retype the password and click "OK".
4. 

Result

In the unlocked cells, you can still input values, and the formulas will continue to generate calculation results while remaining invisible.

Hide formulas and keep specific cells editable using VBA

In this section, I will demonstrate how to efficiently conceal formulas from the formula bar within your worksheet and simultaneously maintain the ability to edit specific cells using a VBA macro.

Step 1: Create a new module

1. Press "Alt" + "F11" to open the "Visual Basic for Applications (VBA)" editor.
2. Click "Insert" > "Module" to create a new module.
3. 

Step 2: Copy VBA code to module window

Copy the below VBA code and paste it into the opened "Module" window.

Sub HideFormulasAndProtectWithEditableCells()
'Update by ExtendOffice
    Dim xWs As Worksheet
    Dim xWb As Workbook
    Dim xPassword As String
    xPassword = "123456" ' Replace "123456" with the actual password for protecting the sheet
    Set xWb = Application.ActiveWorkbook
    
    Set xWs = xWb.Sheets("Sheet1") ' Replace "Sheet1" with your sheet's name
    xWs.Unprotect Password:=xPassword
    For Each cell In xWs.UsedRange
        If cell.HasFormula Then
            cell.FormulaHidden = True
        End If
        cell.Locked = True
    Next cell
    
    On Error Resume Next
    Set xEditableRange = Application.InputBox("Select the range to keep editable", "Kutools for Excel", Type:=8)

    If Not xEditableRange Is Nothing Then
        xEditableRange.Locked = False
    End If
    xWs.Protect Password:=xPassword, UserInterfaceOnly:=True
End Sub

Step 3: Run the VBA code

1. In the "Module" window, press "F5" or click the button to execute the pasted code.
2. In the "Select the range to keep editable" dialog box that appears, select the cells you wish to keep editable, and click "OK".
3. 

Result

Now, with the password you provided to the VBA, the worksheet is protected. All formulas in this worksheet are hidden, and the selected cells are editable.

What if you want to unhide formulas

To unhide formulas, simply unprotect your worksheet: Go to the "Review" tab, select "Unprotect Sheet", enter the password, and click "OK".

How to identify formula hidden cells and unlocked cells

When you've made adjustments to the hidden or locked attributes of specific cells, pinpointing which cells have been modified can be a tedious task. It typically involves individually checking each cell's format using Excel's Format Cell dialog.

However, with "Kutools for Excel", you can simplify this process with a single click. It swiftly highlights the cells that have had their attributes altered, making the identification of formula hidden cells and unlocked cells effortless and time-saving.

After installing Kutools for Excel, navigate to "Kutools Plus" > "Design View" to access the "Kutools Design" tab. From there, click "Highlight Unlocked" to mark all unlocked cells (the cells with the locked attribute removed), or click "Highlight Hidden" to identify all formula hidden cells (the cells with the hidden attribute added).

Above is all the relevant content related to hiding formulas in Excel. I hope you find the tutorial helpful. If you're looking to explore more Excel tips and tricks, please click here to access our extensive collection of over thousands of tutorials.

Related articles

• 7 Ways to Show Formulas in Excel (Easy Step-by-Step Guide)
• How to show or hide formula bar in Excel?