Updated August 16, 2023
Show Formula in Excel
Show Formula in Excel helps the user see the formulas in any cell of the worksheet, showing all the formula types and lengths. To see the formulas in any cell, go to the Formula menu tab, and under the Formula Auditing section, select Show Formula. We can use this option to show the formula, print it, and hide its value instead of seeing it. First, to print the formula, go to the File menu and, from the Options section, check the Show formula box in the cell. And if we see the print preview, it will show the formula instead of the value.
How to Show Formula in Excel Instead of the Values?
There are different ways by which we can show the formulas in Excel. They are listed below, along with the relative examples and screenshots. This will help to understand different ways to show formulas in Excel worksheets.
Here is the sample data used for examples:
We will look at a few examples to understand this better.
Example #1 – Double-Clicking a Single Cell to Exhibit the Formula
- If you want to see a single cell formula that shows the result, just double click on that cell as below. You can also then edit it:
- If you want to see the formula in all the cells of column F, then select the cells below:
- Now, you have to go to the formula tab and click on the Show Formulas Excel option as below:
- Once you have clicked the Show Formula tab, then you will see the formulas in the cells of column F as below:
Example #2 – Show Formula in Excel
- Another example from the above data:
- Now select all the cells from column F:
- Now click the Show Formula tab, then you will see the formulas in the cells of column F as below:
- Placing the Cursor before the Formula would show the Formula in that cell.
This shows the formula rather than the result.
Example #3 – By Enabling Show Formula in Workbook Option
- Go to the File option, now go to Options.
- Now go to Advanced, scroll down a bit, and then under the heading ” Display options for this worksheet “, just check the box Show Formulas in cells instead of their calculated values. Press OK.
This might look like a lengthy process, but it is very handy if you want to show the formula for multiple sheets.
- In case you want to disable this option, just follow the same path, which is File, then Options, then Advanced < heading “Display options for this workbook“.
- Uncheck the box ” Show formulas in cells instead of calculated results.”
Example #4 – Keyboard Shortcut to see the Formula of a Workbook
The keyboard short cut keys are ( CTRL +` ). You can find Ctrl on the left side and the ` right above Tab button on the right side of your keyboard.
Press them, and then you would be able to see every Formula in the spreadsheet, but other cells won’t be changed.
Adding an Apostrophe at the Start of the Cell Containing the Formula.
Just add ‘ in front of the formula so that it can be seen in the cell containing the calculated value.
Example: Using our previous sample data to show how it works.
You can also show formulas in multiple sheets. For this, select the worksheets by pressing and holding the Ctrl key and clicking the worksheets, then press the Ctrl key and ~ on the keyboard. If you want to switch back to no formula shown in a worksheet, press and hold the Ctrl key and press ~ again.
Show Formulas Selected Cells Only Instead of the Results.
Example: In the below sample data, select all the cells.
Now, click ” Find and Select “, then select Replace dialog box.
- Within the replace tab, enter “ =” in the ” Find what ” field and “ ‘=” in the ‘Replace with’ field and then click on Replace All.
- It will show formulas in all the selected cells, while the other cells will not be changed.
Things to Remember About Show Formula in Excel
- Sometimes, we might witness a problem wherein we type formula, and when we press Enter, we get no result. We try again and again, but nothing happens.
The excel formulas show as text and don’t show the result. Like the cell shows :
=sum(A1, B1) but not the result.
One of the reasons could be that unknowingly you set cell formatting to Text instead of General.
This is shown below:
Now make formatting from Text to General.
Another reason could be, you might have pressed, or Show Formulas is enabled.
This can be fixed by pressing CTRL+` again or disable the Show Formulas button.
- Sometimes, the presence of space character or apostrophe before the equal to sign in the formula makes the cell format like text, and the formula shows up instead of the value. To handle this, simply remove these. You can use find and replace to do this.
- It might happen in a hurry that we press the wrong key with Ctrl instead of pressing Ctrl+`. Hence we don’t see the formula in that cell.
- Be careful to study/check the formula by using the above methods but do not lose/delete the formulas in your worksheet/workbook.
Recommended Articles
This has been a guide to Show Formula in Excel. Here we discuss how to use Show Formula in Excel along with practical examples and a downloadable excel template. You can also go through our other suggested articles-
