How to Show (and Hide) Formulas in Excel

To become a proficient and accomplished Excel user, a person should have sufficient and effective knowledge of using Excel formulas, to perform a variety of diverse calculations and functions that are highly difficult to perform manually. Using formulas in Excel can make your day to day tasks easier and also reduce the risks of making erroneous and decrease the degree of complexity of such a tiring task.

Excel provides a divergent array of formulas for just about anything that can be applied to a specific cell or on a range of cells, as in case of array formulas. But, if you are sharing your spreadsheets with other people, then it looks quite rude to write formulas as it is. It may not only make the spreadsheet difficult to understand for a layman, but also will look less presentable or unpleasant.

Furthermore, if you are working on a spreadsheet with a wide number of formulas in it, it may become challenging to comprehend and understand the working and functionalities of those formulas as well as the relationship that exists between them as well as the cells on which they are operating upon. While, on one hand, showing formulas in Excel, instead of their results, can help an accomplished Excel user to derive those relationships easily without any impediments as well as to verify the entered formulas for any possible errors, on the other hand, it may might become very incomprehensive and unpleasant for others.

All the formulas and their inert complexities should be hidden from the viewers, that is, data abstraction in case of formulae application is essential.

Lets discuss and decode the procedure for showing and hiding mathematical and statistical formulae in Excel.

Showing Formulas in Excel

Showing formulas in Excel, especially for accomplished and proficient Excel users is of paramount importance, as it helps in studying the relationships that exist among data without any complexity and also facilitates the procedure of error deduction in the application of the mathematical or statistical formulae or the cells upon which it operates.

Using ‘Show Formulas’ option in Excel

This method to show formulas in Excel is extremely easy to understand and comprehend and involves a typical procedure.

The following steps need to performed sequentially in order to display applied formulae in Excel:

  1. Open the desired Excel sheet and click on ‘Formulas’ tab available on Menu Bar.
  2. Click on ‘Show Formulas’ option in Formulas tab, available under Formula Auditing Section.
  3. Clicking on the option once will display all the formulas at once, whereas clicking on them once again, will hide the formulas. However, clicking on specific cells wherein the formula is applied, will display formula to user.

Using Shortcut Keys in Excel to display Formula

The above method of displaying formulas is a bit long and can be replaced by a shorter route of displaying formulas, namely, pressing ‘Ctrl + `’ keys simultaneously on keyboard , wherein (`) key is known as the grace accent key is available furthest on the keyboard, on the left hand side of key 1 on keyboard or above Tab key.

Alternatively, pressing Ctrl+` together can be considered as shortcut key for Show Formulas option as it gets highlighted alongwith subsequent display of formulas when the shortcut keys are pressed.

The Show Formulas shortcut keys operate between displaying cell values and cell formulas and hiding them. Thus, once the formulas are displayed by pressing shortcut keys, they can be hidden back once again, by pressing the shortcut again.

However, as evident, that this method is not completely applicable as the standard method for displaying and hiding formula due to the fact that when the specific cells upon which formula is applied are selected, the formula is still visible in the top formula bar (Formula bar is the bar just below Menu Bar).

Using ‘Show formulas in cells instead of their results’ Excel option

An Excel option of ‘Show formulas in cells instead of their results’ available in Excel options can be used to display Excel formula.

Perform the following steps sequentially to display mathematical and statistical formulas in Excel:

  1. In Excel model of2010, Excel 2013 and Excel 2016, click on File tab available on Menu Bar, and then click on Options.

    In Excel 2007, click on Office Button available on Menu Bar, and further click on Excel options at the bottom.

  2. When Excel Options dialog box opens up, click on Advanced tab available on the left pane of dialog box and scroll down to the ‘Display options for this worksheet’ section.
  3. Select the option ‘Show formulas in cells instead of their calculated results’ by clicking the check box against it.

Though, the mentioned method, initially, may seem to look long and tedious, however, in the long run, this method is extremely useful when you want to views formulas in a number of Excel spreadsheets, within the currently open Excel workbooks . In that case, you can just select the sheet name from the dropdown list available against ‘Display options for this Workbook’ section of Advanced tab and check the ‘Show formulas in cells instead of their calculated results option.

Notes:

  1. Whichever of the aforementioned method of displaying cells you use, Microsoft Excel will show all formulas of the current worksheet. To display formulas in other sheets and workbooks, you will need to repeat the process for each sheet individually.
  2. If you click a specific cell on which a formula is applied, but the formula is not displayed in the formula bar, then the most likely case is that formula is hidden from the user’s accessibility and the worksheet is protected. This might be a way of implementing data abstraction and hide the complexities of a program. While this might suit to a large variety of users, proficient and skilled Excel users may find it unsuitable for which Worksheet protection needs to be removed.

    The following steps needs to be performed for removing Worksheet protection:

    • Click on Review Tab on Menu Bar.
    • Click on Unprotect Sheet option available under Change Section.
    • Uncheck the hidden option in the ‘Protection’ tab of format cells.
      The Excel formulas would be visible again.

Hiding Excel Formulas

Excel formulas can be hidden to implement data abstraction, hide the complexities of Excel Spreadsheets, and make the sheets informative, presentable and comprehensive for the end user.

Replacing Excel formula with computed values

Excel formulas can be easily replaced by their computed resultant values, which can, in a way, be used to hide the complexities of the sheet from the end user and implement data abstraction. This method is also essential to understand and comprehend, as it is the only method which considers a vital aspect: Not using those formulas again.

The following steps need to be performed in order to replace Excel formulas with their corresponding computed values:

  1. Select the range of cells where you have applied the Excel formula.
  2. Right click on those cells and click on Paste Special… option.
  3. After the Paste Special window is opened, click on spreadsheet labeled as 123, under Paste Values section and click on OK.

This method replaces the Excel formulas with their corresponding values.

Note: This method is unsuitable if the Excel formulas need to be reused again.

Hiding Excel formulas by protecting the Excel sheet

If you want to hide your formulas temporarily such that they need to be reused again, as desired, the following method of Protecting Excel sheet needs to be adopted. In this method, we will first hide the Excel formulas and then lock the complete excel sheet, such that the sheet cannot be edited by end user, while at the same time, hiding the Excel formulas. Following steps need to be adopted sequentially for the same:

  1. Select the range of cells wherein formula has been applied.
  2. Right click on the range of cells and click on Format Cells option.
  3. Next, in the ‘Format Cells’ dialog box, click on the ‘Protection’ tab.
  4. In Protection Tan, check or click on Hidden checkbox and further click on OK.
  5. Further, to protect the Excel sheet, click on the ‘Review’ tab available on Menu bar and select the option ‘Protect Sheet’ under Changes option.
  6. Type a user-defined password to protect the sheet, which is only known to users, who have the authority to further access and edit the requisite worksheet.
  7. Click on OK.

The worksheet is in protected mode, with the Excel formulas hidden.

In order to view the formulas again, the following steps needs to be performed for removing Worksheet protection:

  • Click on Review Tab on Menu Bar.
  • Click on Unprotect Sheet option available under Change Section.
  • Uncheck the hidden option in the ‘Protection’ tab of format cells.
    The Excel formulas would be visible again.
Comments are closed.


Magoosh blog comment policy: To create the best experience for our readers, we will only approve comments that are relevant to the article, general enough to be helpful to other students, concise, and well-written! 😄 Due to the high volume of comments across all of our blogs, we cannot promise that all comments will receive responses from our instructors.

We highly encourage students to help each other out and respond to other students' comments if you can!

If you are a Premium Magoosh student and would like more personalized service from our instructors, you can use the Help tab on the Magoosh dashboard. Thanks!