Excel Hide Formulas (Table of Contents)
Introduction to Hide Formula in Excel
There are two ways to Hide any formulas in Excel. The first way we can protect the current worksheet or workbook from Protect Sheet and Protect Workbook option, which is available in the Review menu option under the Changes section. There we need to provide the password to lock the changes in Sheet. This will prevent other people from changing their formulas. And other is by Format Cells option, which can be accessed by short cut key Ctrl + 1. From the Format Cells box, under the Protection tab, select the Hidden option and locked option. By doing this, it will lock the selected cell preventing it from editing.
- This feature in excel is introduced in order to hide the formulas from others.
- When we write a formula in an active cell, the formula is automatically displayed in the formula bar.
- Sometimes, it is needed to hide the formula for security or confidentiality reasons. In that case, we use the excel hide formula.
- Excel makes it simple to hide formulas from getting changed.
Example:
Different ways to Hide the Formula in Excel
There are different ways of hiding excel formulas to protect accidental or intentional changes in the worksheet, as mentioned below –
- To protect the worksheet and activate the hide formula option.
- To hide the formula bar of the workbook.
#1 – To protect the worksheet and activate the hide formula option.
Steps to hide the Excel formula
Step 1: To Unprotect the Sheet
- The first step is to make sure that the worksheet is unprotected.
- To check the same, go to the Review tab in the Excel ribbon.
- If you find the Protect Sheet button in the toolbar, then it means the worksheet is not protected. In that case, you can directly start the steps to hide the formula from here on.
- If you find the Unprotect Sheet button in the toolbar, then it means the worksheet is protected. Click on the same and make the sheet unprotected by typing the correct password.
Step 2: To hide the Formula in Excel.
- As per the in-built feature of MS Excel, by default, the formula of the active cell appears in the formula bar.
- To hide the excel formula, select the range of cells for which the formula is needed to be hidden.
- Right-click the cell or range of cells. Select Format Cells or press Ctrl+1.
- Once the Format Cells dialog box appears, click on Protection.
- The locked check box is selected by default. The hidden checkbox is deselected by default. The selected Locked check box prevents the user from changing the contents of the cells in a sheet, whereas the Hidden checkbox is an optional feature. It needs to be selected manually. It helps in hiding the formula and prevents the user from seeing the same.
Step 3: To Protect the Sheet
- This step is very important in hiding the formula. The above formula will remain ineffective until the sheet is being protected.
- Go to the Review tab in the excel ribbon. Click on Protect Sheet.
- Protect Sheet dialog box will appear. Type the password to protect the sheet. Once the sheet is protected, no one except the real user can unprotect the sheet and make changes in it and then click OK.
Note: By default, Protect worksheet and contents of the locked cells check box is selected. Also, Select locked cells and Select unlocked cells checkboxes to remain selected by default.
- Confirm Password dialog box will appear. Retype the password, which will help to prevent a typographical error in the password from locking the spreadsheet forever. Click OK.
- Once the sheet is protected, the formula of the active cell will not be shown up in the formula bar. Thus the formulas in the cells are protected. Therefore, neither it can be seen or edited.
- Whenever you want to make changes to the protected sheet, the following message will be displayed.
Note: The user without a password can click on the cells but cannot make changes in the content of the protected sheet. To make the changes, first, make the protected sheet is unprotected.
How to Unprotect the Protected sheet?
- Select Review tab in excel ribbon. Click on Unprotect Sheet option.
- Unprotect sheet dialog box will appear. Type in the password and then click Ok. The sheet will become unprotected.
#2 – To hide the formula bar of an Excel workbook
Formula bar exists below the formatting toolbar or ribbon area. It is divided into two different parts:-
The left part is the Name box where the active cell number is displayed.
On the right, the Formula of the active cell is displayed.
Steps to hide the formula bar:
- Go to the File tab in the Excel ribbon. Select Options.
- Excel Options dialog box will appear. Select Advanced.
- On the right-hand side panel, scroll down to the Display section.
- Unselect the Show formula bar checkbox and then click OK.
Note: If show formula bar check box is selected, the formula bar will be displayed; otherwise, it will not be displayed.
How can the Hidden formulas be found?
- Go to the Home tab in the Excel ribbon in the Editing section, select Find & Select and then click on the Formulas option.
- Excel will highlight and select all the cells that have formulas.
Things to Remember about the Hide Formula in Excel
- This option helps you hide the crucial and confidential formulas from others, thus protecting your documents.
- As discussed above, there are two ways of hiding formulas. In the first method, we can hide and protect the formulas sheet wise. In other words, in the same workbook, we can apply it to protect and hide the excel formula in one sheet, whereas the second sheet remains unprotected.
- In the protected worksheet, we can only see the data and put the cursor on the cells but cannot view the formulas and make changes in them.
- The second way to hide the formula bar applies across any file opened in the MS Excel application. The worksheets are not protected however formulas remain hidden because of the aforementioned feature.
Recommended Articles
This has been a guide to Hide Formula in Excel. Here we discuss how to use Hide Formula in Excel along with practical examples and a downloadable excel template. You can also go through our other suggested articles-
