How to hide formulas in Excel

When you share Excel documents with others, they can make changes into formulas and other formatting options in the document. If you don’t want to make changes in the document, then it is necessary to hide as well as protect the entire sheet, some portion of the sheet or formula so that other users can’t edit those sheets.

Before hide formulas in Excel, let’s discuss how to show formulas in Excel –

By default, the formula will appear in the Formula Bar, when we click on the cell in which the formula is applied.

To view the formula in the Cell, Go to the Formula tab on the Ribbon and click on the Show formulas button under the Formula Auditing section.

Now, you can see that the applied formula appears on the cell.

Hide Formulas

There are the following methods to hide formulas in Excel –

Note: We use the below methods to show or hide formulas in Microsoft Excel 2007, Excel 2010, Excel 2013, Excel 2016, Excel 2019, as well as Office 365.

Method 1: Hide Formulas in Excel and protect the worksheet

In Microsoft Excel, it is very simple to hide formulas in the document so that others can’t be able to see which formula you used on a particular section.

Steps to hide formulas in Excel

Steps by step procedure to hide formulas in Microsoft Excel given below –

Step 1: Open the Microsoft Excel document by double click on the Microsoft Excel icon on the desktop.

Note: To open New document go to File -> New -> Blank document -> Create or to open an existing document go to File -> Open -> Browse file location -> click Open button.

Step 2: Select or highlight the cell for which you want to hide the formula. (In our case, we want to hide formula from the cell B8)

Step 3: Make sure that your Microsoft Excel worksheet is unprotected. If your Excel worksheet is protected, then go to the Review tab on the Ribbon and click on the Unprotect Worksheet button under the changes section.

Note: If your Microsoft Excel sheet is already protected, then skip Step 3.

Step 4: Right click on the selected cell. A small pop up window will appear in which click on the Format Cells option.

Note: You can also press Ctrl + 1 keys from the keyboard to open the Format cell window.

Step 5: A Format Cells dialog box will appear with the selected Number tab. To Hides formula, click on the Protection tab in the Format Cells dialog box.

Step 6: Tick on the check box associated with the Hidden and click on the OK button at the bottom of the Format Cells dialog box, as shown in the screenshot given below.

Protect the Excel sheet

The hidden cell effect doesn’t work properly until you protect your Excel document.

Follow the below given easiest steps to protect your Microsoft Excel document –

Step 1: On the same Excel document, Go to the Review tab on the Ribbon. Click on the Protect Sheet tab under the Changes section.

Step 2: A Protect Sheet dialog box will appear on the screen with ticked Select locked cells and Select unlocked cells checkboxes. Type your desired password in the Password to unprotect sheet: section. Click on the OK button at the bottom of the dialog box.

Step 3: A small ConfirmPassword dialog box pops up in which Reenter your password in the Reenter password to proceed textbox and click on the OK button at the bottom of the screen.

Note: Always remember your entered password, otherwise, you are unable to unprotect the worksheet.

Now, you can see the formula is hidden in the formula bar. See the screenshot given below.

Note: The above mention steps protect the entire Microsoft Excel document.

Hide only Formulas in Excel (Keep rest of cells editable)

In the previous method, the entire document is protected but if you don’t want to protect the entire worksheet and only protect the cells that have formulas, then use the below steps to hide formulas.

Step 1: Open a Microsoft Excel document and enter your desired data on it.

Step 2: Place the cursor on the cell that contains the formula. In our case, Cell B7 contains a formula.

Step 3: Now, you need to disable the lock property for all cells, so follow the below instructions –

1. Select all cells in the Excel document by clicking on the gray triangle at the top left of the Excel document.

2. Go to the Home tab on the Ribbon and click on the dialog box launcher icon in the Number group.

3. A Format Cells dialog box appears on the screen. Click on the Protection tab. Untick the Locked checkbox and click on the OK button at the bottom of the Format Cells dialog box.

Step 4: Enable the Locked and hidden property for cells having formulas –

1. Select or highlight the entire Excel document.

2. Go to the Home tab on the Ribbon and click on the Find & Section option in the editing group. A list of options will appear. Click on the Go To Special.

3. A Go To Special dialog box will appear on the screen. Click on the radio button associated with Formulas. Click on the OK button.

4. Press the Ctrl + 1 keys from the keyboard. A Format cell dialog box will appear. Ticked both Locked as well as Hidden checkboxes. Click the OK button at the bottom of the screen.

Step 5: Protect the Excel Sheet

1. On the same Excel document, Go to the Review tab on the Ribbon. Click on the Protect Sheet tab under the Changes section.

2. A Protect Sheet dialog box will appear on the screen with ticked Select locked cells and Select unlocked cells checkboxes. Type your desired password in the Password to unprotect sheet: section. Click on the OK button at the bottom of the dialog box.

3. A small Confirm Password dialog box pops up in which Reenter your password in the Reenter password to proceed textbox and click on the OK button at the bottom of the screen.

Hide Formula Bar

The formula bar is the place where all formulas are displayed on the Excel document.

Hide formula bar using View tab

In Microsoft Excel, you can also hide the formula bar to maximize workspace and hide the formula that we use on the document to perform calculations.

Steps to Hide Formula bar

Follow the below given easiest steps to Hide the Formula bar in Excel document –

Step 1: Open a Microsoft Excel document in which you want to Hide a Formula bar.

Step 2: Go to the View tab on the Ribbon and Untick the Formula Bar check box in the Show section.

Now, you can see that the Formula bar is hidden in the Excel document. See the screenshot given below –

Method 3: Hide Formula bar using the Excel options

Microsoft Excel also provides the Excel options tab to hide the Formula bar.

Follow the below steps to hide the formula using the Excel options –

Step 1: Open a Microsoft Excel document in which you want to hide a formula bar.

Step 2: Click on the Office button (in Excel 2007) Or click on the File tab at the top left corner of the document (in Microsoft Excel 2010 and above versions)

Step 3: A list of File options will appear on the document. Click on the Options tab to activate the Excel Options.

Step 4: An Excel Options dialog box will appear in which do the following –

1. Click on the Advanced tab form the left pane.
2. Scroll down the Excel Options dialog box.
3. Untick the Show formula bar checkbox.
4. Click on the OK button at the bottom of the Excel Options dialog box to apply the changes.

The screenshot below shows that Formula Bar is hidden in the Excel document.

---