Show Formulas in Excel – 5 Easy Methods
Usually, when you create a formula in Excel, it shows the result in the cell. But sometimes, you might want to see the formula itself instead of the result. This can help you spot errors and understand how the spreadsheet works.
Seeing the formulas also makes it easier to know which parts are calculations and which are just regular data.
Method 1:- Show Formulas in Excel using shortcut
There’s an easy keyboard shortcut to quickly show or hide formulas in Excel.
Just press Ctrl + ` on your keyboard to switch to the formula view in your sheet. (you will find this key in the top-left part of the keyboard, under the Escape key).
Note: This method only works for the active sheet. When you use the Show Formulas option or shortcut, it will show the formulas just in the sheet you’re currently on. Other sheets won’t be affected. To see formulas in another sheet, switch to that sheet and use the shortcut or ribbon button there.
Method 2:- Show Formulas in Excel Using Formula Tab
You can see all the formulas in a sheet at once by going to the Formulas tab
- Go to the Formulas tab.
- Click Show Formulas.
When you choose this option, all formulas in the sheet will show up instead of their results.
Method 3:- Show Formulas in Excel using FORMULATEXT Function
The previous methods can only show either one formula or all formulas at once.
If you want to display formulas in just a selected range while showing calculated values for the rest, you can use the FORMULATEXT function. The FORMULATEXT function in Excel allows you to display the formula in a cell as text, instead of its result. Here’s how it works:
- Make the helper Column in I
- In Cell I3 insert the formula “=FORMULATEXT(H3)” and press ENTER
- The above formula will convert the formula in cell H3 into text and it will be delayed in the cell as text.
- Click and drag the fill handle down to apply the formula to the cells below in column I3
This will display the formulas in all cells.
Method 4 :- Show Formulas in Excel Using Check Advanced Excel Options
- Go to the File tab.
- Click Options at the bottom.
- In the Excel Options dialog, select Advanced from the left menu.
- Check the option Show formulas in cells instead of their calculated results.
- Click OK
This will make all formulas visible in the cells, replacing their results. This is the easiest way to show formulas in Excel
Method 5 :- Show Formulas in Excel Using Find and Replace
Another way to make Excel treat your formula as text is by adding an apostrophe ' at the beginning.
Simply place the apostrophe before the equal sign ‘= and the formula will appear as text in the sheet.
If you want to use this trick on many cells, use Find and Replace. Find all equal signs = and replace them with '= to show formulas as text. Here’s are the simple steps :-
- Select the cells with the formulas you want to show.
- Press Ctrl + H to open Find and Replace.
- In Find what, type
=. - In Replace with, type
'=(apostrophe + equal sign). - From the Look in dropdown, choose Formulas.
- Click Replace All.
- In Popup press OK.Â
This will add an apostrophe to the beginning of all formulas, making them display as text. The apostrophe will only appear in the formula bar when the cell is selected, but it won’t show in the grid.
Conclusion
Show formulas in Excel is important for troubleshooting or understanding someone else’s work.
You can see one formula at a time using the formula bar or edit mode, but if you want to view multiple formulas, you can use the Show Formula feature. It’s available in the Home tab, with a keyboard shortcut, or in Excel Options.
Other ways to show formulas include text formatting, the FORMULATEXT function, or using Find and Replace to turn formulas into text. These methods let you display formulas in a selected range while keeping other cells as calculated values.
Hope you like this article, follow us to learn more advanced Excel FunctionsÂ
