Excel Show Formula (Table of Contents)
Show Formula in Excel
In this article, we will learn how the formulas can be shown instead for their result in a specific cell, selected cells, workbook or multiple workbooks. It is our common observation that when we add/write formulas in our Excel sheet they are hidden while viewing the sheet. Once we have written the formula and pressed Enter, it would only show the result, and the formulae would disappear. We can, however, see it in formula bar but not in the cell. We only can see the result.
In Excel, we do have options to show the formulas in the cells. This becomes helpful when we are working on data where we have to use a lot of formulae and we need to understand the relationship between them. By this way, we can also locate the error in the formulas.
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 surely help to understand different ways to show formula 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 the formula of a single cell which 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 as below:
- Now, you have to go to formula tab and click on 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 a lengthy process but is very handy in case 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 ` right above Tab button on the right side of your keyboard.
4.9 (2,645 ratings)
View Course
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.
In case you want to switch back to no formula shown in a worksheet, press and hold Ctrl key and press ~ again. You can also show formulas in multiple sheets. For this, first select the worksheets by pressing and holding Ctrl key and clicking the worksheets, then press Ctrl key and ~ on the keyboard.
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 would 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 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 due to the presence of space character or apostrophe before the equal to sign in the formula makes the cell format as 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 downloadable excel template. You can also go through our other suggested articles-
