How to Show Formula in Excel?
In Excel, we have an option to show those formulas to study the relationship of the formula. There is also a keyboard shortcut to show formulas in Excel, which we will explore further. Follow the steps to find that option in Excel.
Click on the “Formulas” tab, then “Formula Auditing” and “Show Formulas.”
Once we click on those options, all the formulas in the Excel sheet will be visible instead of the values of the formula.
If you again click on that option, Excel will start to show the values instead of the formula itself.
Below is an example of that showing formulas.
Below are the steps of showing formulas:
- First, select the formula cell range.
- Now, go to the “Formulas” tab and click on the “Show Formulas” option.
- Once you click on that option, Excel shows the formula instead of the formula results.
Table of contents
Shortcut to Show Formula in Excel
The shortcut to show the formula in Excel is “Ctrl + `.” The key (‘) is on the keyboard below the “Esc” button.
How to Enable Show Formula in Workbook Option?
Showing formulas is not a workbook-level option. But, we can offer all the formulas at once by changing our default settings of Excel.
Follow the below steps to enable this option.
- Step 1: Go to the “File” option in Excel.
- Step 2: Now, click on “Options.”
- Step 3: Go to the “Advanced” option.
- Step 4: Scroll down and find “Show formulas in cells instead of their calculated results.”
It seems a long process but is very useful if you want to show multiple sheets’ formulas. However, making one sheet at a time is a very time-consuming process. So, we can rely on these settings options.
Print Formulas using Show Formula Option
We can print these formulas instead of the values using this formula option.
- Step 1: Select the formula cells (if you need particular cells to be displayed, then select those cells only) and click on this formula option. (We can use the shortcut Ctrl + ` ).
It would show up all the selected cell formulas.
- Step 2: Select the printable region data and press the “ALT + P + R + S” shortcut keys. It would set up the print area.
- Step 3: Now, press the “Ctrl + P” keys. It will print all the formulas. Now, you can return and press “Ctrl + `” to remove the visible formulas option and make the values visible.
Hide Formulas in Excel
We cannot only show the formula but also hide those formulas to be visible to other users by protecting the sheet.
- Step 1: Select the formula cells.
Step 2: Right-click and select “Format CellsFormat CellsFormatting cells is an important technique to master because it makes any data presentable, crisp, and in the user's preferred format. The formatting of the cell depends upon the nature of the data present..”
- Step 3: Select the “Protection” tab and check the “Hidden” box.
- Step 4: Now, click on the “Review” tab and select “Protect Sheet.”
- Step 5: Type the password and protect the sheet.
Things to Remember
- Since “Ctrl + `” is a toggle option, we can show formulas and values after every alternative click.
- We must always study the formula and return to formula values but never lose formulas.
- We often type “Ctrl + `” when using shortcuts. So, do not panic; try typing “Ctrl +`.” It will give back your values.
- If the format of the cell is “TEXT,” then it may show the formula in Excel rather than the values of the formula. Apply “General” formatting to show the values of the formula.
This article has been a guide to Show Formula in Excel. Here, we discuss how to show the formula in Excel and its shortcut, along with practical examples. You may learn more about Excel from the following articles: –
- Write Formula in ExcelWrite Formula In ExcelWrite Formula in excel refers to different formulas written in MS excel to imply a function or calculation to the selected cell or range of cells, rows and columns. There are hundreds of inbuilt formula like AVERAGE, SUM and a proper way of writing it to apply it.
- TEXT Formula in ExcelTEXT Formula In ExcelTEXT function in excel is a string function used to change a given input to the text provided in a specified number format. It is used when we large data sets from multiple users and the formats are different.
- Excel Print AreaExcel Print AreaIn Excel, the print area is the portion of the workbook or worksheet that we wish to be printed rather than the entire workbook or worksheet. From the page out tab, we can set up a print area. In addition, a single worksheet can contain numerous print areas.
- Create a Formula in ExcelCreate A Formula In ExcelExcel formulas are nothing but simple equations to perform calculations and get desired and accurate outputs. In Excel, you can create your formulas as per your choice and need.
- False in ExcelFalse In ExcelFalse is a logical function in Excel that returns false as an output when used in a blank cell. It can also be used with other conditional functions to return a false, such as the IF function.