If you are working with an Excel sheet that contains many formulas, it may be difficult to keep track of all those formulas. In addition to Formula bar, Excel has a simple tool that allows you to display formulas.
This tool also shows the relationship for each formula (when highlighted) so you can keep track of the data used in each calculation. The display of formulas allows you to find the cells containing them, view them, check for errors, and print the formula sheet.
To show formulas in Excel click Ctrl+’(apostrophe). The formulas will be displayed as shown in the picture above. Cells associated with a formula are highlighted with borders that match the color of the links to make it easier to keep track of the data.
You can also choose to command Show Formulas (Show formulas) tab Formulas (Formulas) in a group Formula Auditing (Formula Dependencies) to show formulas in Excel.
Even if the display is disabled, the formula can be viewed in Formula bar when selecting a cell. If you don’t want the formulas to be visible to users of your Excel workbook, you can hide them and protect the worksheet.
- To do this, select the cells containing the formulas you want to hide.
- On the Advanced tab Home (Home) in a command group Cells. Cells. (cells) click Size (Format) and from the drop-down menu, select Format Cells (cell format).
- A dialog box will appear Format Cells (cell format). Check box Hidden (Hide formulas) tab Protection (Protection). Then click OK.
- To permanently hide the cells, you must protect the sheet. To do this, press again Size (Format) tab Home (Home) and from the drop-down menu, select the item Protect Sheet (Protect sheet).
- A dialog box will open Protect Sheet (Sheet protection). Make sure the checkbox is checked Protect worksheet and contents of locked cells (Protect the sheet and the contents of protected cells). Enter a password to protect the worksheet, which will allow you to remove the protection and see the formulas again in the future. In chapter Allow all users of this worksheet to (Allow all users of this sheet) check the boxes next to the tasks that you want to allow users. Click OK.
- To continue, enter your password again in the corresponding field of the dialog box Confirm Password (Password confirmation).
- You may notice that now when you select a cell containing a formula, Formula bar remains empty.
- To show the formulas again, click Size (Format) and select an item Unprotect Sheet (Remove sheet protection).
- Enter the password in the dialog box Unprotect Sheet (Unprotect sheet) and press OK.
Formulas will again be visible when cells are selected.