While working on a spreadsheet in Excel with tons of data and formulas, you may feel the necessity to display the cell formulas at once for several reasons. Excel can also show a single formula present in a specific cell. From time to time, you may want to see reference cells, edit them, understand how they are working, or even how different cells are related to each other. Whatever the purposes you possess, go through the whole article carefully to learn how to display cell formulas in Excel.
The above image displays the cell formulas in the Excel worksheet using the Show Formulas command in the Formulas tab.
How to Display Cell Formulas in Excel: 6 Methods
The above sample dataset contains sales amounts of different products in January, February, and March. Then there is the total sales column where we use the SUM formula to combine these 3 months. In this article, we’ll show 6 methods to display those formulas.
1. Use Show Formulas Command to Display All Cell Formulas in Excel
To see formulas instead of results, go to the Formulas tab > click Formula Auditing drop-down > select Show Formulas. Hence, you’ll see the formulas in the Total Sales column.
2. Show Formulas in All Cells Through Keyboard Shortcut
In the current worksheet, press the Ctrl + ` keys together to display the cell formulas in Excel. The formulas in the Total Sales column will appear as it’s shown in the above image.
3. Insert FORMULATEXT Function to Display Cell Formulas in Excel
The FORMULATEXT function returns the formula present in a cell. Here in cell G5, we insert the formula:
Then, we apply AutoFill to get other formulas as well.
4. Check Advanced Excel Options to Show Cell Formulas
Another way to display cell formulas is through a settings change in the Excel Options. In this way, you will only see the formulas instead of the results.
First, go to the File tab > click Options.
In the Excel Options dialog box, go to the Advanced tab and check for Show formulas in cells instead of their calculated results. Press OK.
Thus you’ll see the formulas in the Total Sales column.
5. Temporarily Display Excel Formula in a Cell
You can temporarily view the formula present in a cell instead of displaying all the formulas in the worksheet. This section will go over 3 ways to perform the task.
5.1 Press F2 Function Key to Show Cell Formula
To get a quick view of the formula, select the F5 cell and press the F2 key. You can do that with other cells too. However, the cell will display the sum result again after you click elsewhere.
5.2 View Cell Formula in Formula Bar
You can also see the formula in the Excel Formula Bar. Click on the desired cell and the formula bar shows the formula. Here the F5 cell has the formula:
5.3 Double-Click on Cell to Display Formula
Another way to display formulas temporarily is by double-clicking on the cell. The above image shows the formula in the F5 cell by double-clicking on it.
6. Modify Formula to Display Cell Formulas in Excel
Now we will show you some modifications of the formulas to display alongside the results.
Copy the F5:F14 range through the Ctrl + C keys. Then paste them into G5:G14 with Ctrl + V keys. Now, select the G5:G14 range and press Ctrl and H keys together.
The Find and Replace dialog box pops out. Insert
Find what >> =
Replace with >> = (there’s a space before =)
Press Replace All.
Another way is to insert:
Find what >> =
Replace with >> ‘=
Press Replace All.
Therefore, you’ll see the formulas beside the Total Sales column.
How to Hide Cell Formulas in Excel
To hide cell formulas present in F5:F14 in Excel, select the range. Go to the Home tab > click Cells drop-down > click Format drop-down > choose Format Cells.
The Format Cells dialog box pops out. Check for Hidden and press OK.
Now go to the Review tab > select Protect drop-down > click Protect Sheet.
The Protect Sheet dialog box appears. Check for Protect worksheet and contents of locked cells. Press OK. It’s your wish whether or not to give the password to unprotect the sheet.
Click on any cell that has the formula in it. The formula bar will show blanks. In this way, we can hide the cell formulas in Excel.
Why Is Excel Showing Formula Instead of Result
Sometimes you may see that Excel shows formulas instead of results. Some of the reasons are:
1. If the Show Formulas mode is enabled, you’ll see the formulas. To disable it, go to the Formulas tab > click the Formula Auditing drop-down > select Show Formulas. Or you can press the Ctrl and ` keys together. It’ll give back the calculated values.
2. If you accidentally put an apostrophe (‘) before the = sign in the formula, it’ll only display the formulas.
In the above image, the cells in the Total Sales column show the formulas. But in the formula bar, you can actually see an apostrophe that hinders the cell from displaying the result.
3. Similarly if you put a space before the sign, you’ll see the formulas only.
The above image shows the space in the formula bar which keeps it from displaying the calculated values.
4. If you enter a formula in the cell that is set to the Text format, the cell won’t show the calculated value. Because Excel assumes the formula to be a text string. So, change the format to General to get the formula results.
The above image shows the Total Sales column in formulas as it’s set in the Text format.
Things to Remember
- All the above methods work for the current Excel worksheet only. That means it’ll display the cell formulas in the active worksheet. You have to repeat the method for other worksheets too if you want to display cell formulas there.
- Formula Bar is the dedicated place to show cell formulas in Excel. If the formula bar shows blank after clicking a cell that contains the formula, it’s likely that the sheet is protected. Unprotect the worksheet first to display the formulas.
Download Practice Workbook
You are recommended to download the practice workbook and practice along with it.
In this article, we’ve demonstrated how to display cell formulas in Excel with 6 distinct methods. Go with the Show Formulas mode to display all the cell formulas present in the sheet.
If you want to see the formulas alongside the results, you can use the FORMULATEXT function. You are recommended to practice all of them along with the attached practice workbook that has been prepared exclusively for your practice purpose.
Don’t forget to leave any questions in the comment section, you all try to respond to your problems ASAP.
- How to Show All Formulas in Excel
- Why Excel Shows Formulas Instead of Results
- How to Show Formula in Cells Instead of Value in Excel
- How to Show Value Instead of Formula in Excel
- How to Show Formula as Text in Another Cell in Excel
- How to Show Formulas When Printing in Excel
- [Fixed!] Formula Result Showing 0 in Excel
- How to Stop Showing Formulas in Excel