Skip to main content

Hide formulas in Excel (A complete guide)

Author: Kelly Last Modified: 2025-04-09

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.

A screenshot showing an Excel worksheet where the formula is hidden in the formula bar

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.
  • A screenshot showing how to click Find & Select > Formulas in Excel

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".
    A screenshot showing the Format Cells option in the context menu in Excel
  2. In the "Format Cells" dialog, on the "Protection" tab, check the "Hidden" option and click "OK".
    A screenshot of the Format Cells dialog in Excel, highlighting the Hidden option on the Protection tab
  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".
    A screenshot showing the Protect Sheet button on the Review tab in Excel
  2. In the "Protect Sheet" dialog, enter a password and click "OK".
    A screenshot of the Protect Sheet dialog in Excel where you can set a password
    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. A screenshot of the Confirm Password dialog in Excel for protecting the worksheet

Result

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

A screenshot showing an Excel worksheet where the formula is hidden in the formula bar

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

A screenshot of a pop-up dialog asking for a password when trying to edit a locked cell

(AD) All-in-one Excel content protection with Kutools for Excel

Struggling to find where to activate the Hidden property or protect your Excel worksheet? Make life easier with "Kutools for Excel"! It seamlessly combines Excel's native features into a single, user-friendly ribbon for effortless content protection. No more hunting for options, just pure simplicity!

Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy permanently free AI features! Get It Now


Hide formulas and keep specific cells editable

You may find yourself needing to share workbooks with others, granting them the ability to make changes to specific cells while safeguarding your formulas from view or edit. This is a frequent requirement for worksheets involving data entry, statistical analysis, and various other purposes. To achieve this, you have the option to employ one of three methods below:


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.
  • A screenshot showing how to click Find & Select > Formulas in Excel

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".
    A screenshot showing the Format Cells option in the context menu in Excel
  2. In the "Format Cells" dialog, on the "Protection" tab, check the "Hidden" option and click "OK".
  3. A screenshot of the Format Cells dialog in Excel, highlighting the Hidden option on the Protection tab
  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. A screenshot of the Protection tab in the Format Cells dialog in Excel, with the Locked option unchecked

Step 4: Protect the worksheet

  1. Go to the "Review" tab, and select "Protect Sheet".
    A screenshot showing the Protect Sheet button on the Review tab in Excel
  2. In the "Protect Sheet" dialog, enter a password and click "OK".
    A screenshot of the Protect Sheet dialog in Excel where you can set a password
    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. A screenshot of the Confirm Password dialog in Excel for protecting the worksheet

Result

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

A GIF showing formulas hidden while specific cells are editable in Excel


Hide formulas and keep specific cells editable using Kutools for Excel

The method described above instructs how to hide formulas and keep specific cells editable. However, it requires you to remember the location to enable the hidden property and disable the locked property. Additionally, you need to know where to protect your sheet, which can be cumbersome if you have to search for these options.

Kutools for Excel's "Design" tab streamlines these processes by integrating these options, making it easier to achieve your goals. Once you have downloaded and installed Kutools, please do as follows:

Kutools for Excel offers over 300 advanced features to streamline complex tasks, boosting creativity and efficiency. Itegarate with AI capabilities, Kutools automates tasks with precision, making data management effortless. Detailed information of Kutools for Excel...         Free trial...

Step 1: Select "Kutools Plus" > "Design View" to display the "Kutools Design" tab

A screenshot of the Design View button on the Kutools tab in Excel

Step 2: Select cells containing formulas and click "Hide formulas"

  1. Select the cells containing formulas you want to hide.
    Tips:
    • 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.
      A screenshot showing how to click Find & Select > Formulas in Excel
  2. Select "Hide formulas" on the "Kutools Design" tab.
  3. A screenshot of the Hide formulas button on the Kutools Design tab
  4. Tip: A dialog box will appear, informing you that the formulas in the selected cells will be hidden after the worksheet is protected. Click "OK".
    A screenshot of the Kutools dialog box notifying that formulas will be hidden after protecting the worksheet

Step 3: Select the cells you want to keep editable and select "Unlock Cells"

Tip: To keep multiple non-adjacent cells or ranges editable, hold "Ctrl" and select each, and then select "Unlock Cells".

A screenshot of the Unlock Cells button on the Kutools Design tab in Excel

Step 4: Protect the worksheet

  1. On the "Kutools Design" tab, select "Protect Sheet" to open the "Protect Sheet" dialog. Then, enter a password and click "OK".
    A screenshot showing the Protect Sheet dialog in the Kutools Design tab with a password being set
    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.
  2. The "Confirm Password" dialog box appears asking you to reenter the password. Please retype the password and click "OK".
  3. A screenshot of the Confirm Password dialog in Excel for protecting the worksheet

Result

By following these steps, you can effectively hide formulas in Excel while allowing specific cells to remain editable.

A GIF showing formulas hidden while specific cells are editable in Excel

Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy permanently free AI features! Get It Now


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. A screenshot of the VBA editor showing Insert > Module option to add a new module

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

Notes:

  • You should replace "123456" on the 6th line with the actual password for protecting the sheet.
  • You should replace "Sheet1" on the 9th line with the actual name of the worksheet that you will protect.

Step 3: Run the VBA code

  1. In the "Module" window, press "F5" or click the Run button 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. A screenshot of the Select the range to keep editable dialog box in Excel

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.

A GIF showing formulas hidden while specific cells remain editable in Excel


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

The Unprotect Sheet button on the ribbon in Excel

Notes:

  • If you plan to protect the sheet in the future while allowing users to view the formulas, please select these formula cells and press "Ctrl" + "1" to open the "Format Cells" dialog. Then go to the "Protection" tab and uncheck the "Hidden" box.
  • If you've unchecked the "Locked" box to make cells editable after protecting the worksheet, and later want to protect the sheet again without allowing users to edit those cells, select those cells, press "Ctrl" + "1" to open the "Format Cells" dialog, navigate to the "Protection" tab, and check the box next to the "Locked" option.
  • Tip: For assistance in identifying unlocked or hidden cells, refer to the next section.
  • A screenshot of the Protection tab in the Format Cells dialog in Excel

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.

Kutools for Excel offers over 300 advanced features to streamline complex tasks, boosting creativity and efficiency. Itegarate with AI capabilities, Kutools automates tasks with precision, making data management effortless. Detailed information of Kutools for Excel...         Free trial...

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

A GIF showing the Highlight Unlocked and Highlight Hidden options in Kutools for Excel

Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy permanently free AI features! Get It Now

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.