How to show formulas as text in Excel
A free Office suite fully compatible with Microsoft Office
As soon as you write a formula in Excel and hit enter, it'd return the calculated end result, and the formulation might disappear. That’s how it’s supposed to work. But what if you want to reveal formulas inside the cells and no longer the calculated values. To do this, you may use Ribbon or Keyboard shortcut, Function, Putting Apostrophe or Space in front of the Equal sign of a Function.
How to show Formulas as text in Excel Instead of the Values?
Following are the steps to show formulas as text in Excel instead of the value.
1. Click on the ‘Formulas’ Tab in the ribbon and click on the Show Formulas option.
2. As soon as you click on Show Formulas, it’s going to make the formulation inside the worksheet seen. It’s a toggle button, so you can click on it again to make the formulas be replaced by their calculated results.
Show Formulas as text in Excel Instead of the Value in Selected Cells
If you want to view the formulas in some selected cells only. For example, as an Excel trainer, you often create templates where you show the formula in one cell and its result in another cell (as shown below).
Following are the steps to show formulas as text in Excel in selected cells only.
1. Select the cell where you want to show the formula instead of the value.
2. Go to Home –> Find & Select –> Replace (keyboard shortcut – Control + H).
3. In the Find and Replace dialog box, within the replace tab, enter = in the ‘Find what’ field and ‘= in the ‘Replace with’ field.
4. Click on Replace All.
This will show formulas in all the selected cells while the remaining cells would remain unchanged.
Note: Entering an area before the system makes it a text string and the gap character is visible earlier than the same signal. On the other hand, using an apostrophe before the equal to the sign make the formula a text string, however, the apostrophe isn’t visible in the cell.
Handle Excel Showing Formulas Instead of Calculated Values
Sometimes, you can find that the cells in Excel are displaying the formula rather than their result.
There are multiple reasons why this may take place:
1. The ‘Show Formulas’ mode is enabled or you may have accidentally hit the Control + ` shortcut. To disable it, use the shortcut again or click on the ‘Show Formula’ choice inside the Formulas tab.
2. It could be due to the presence of a space character or apostrophe before the equal to sign in the formula. The presence of these earlier than the equal to signal makes the cell format as text and the formula indicates up rather than the value. To handle this, simply remove these. The find and replace option will be used to do this.
3. If a cell has ‘Text’ formatting applied to it and you enter the formula and hit enter, it will continue to show the formula instead of the calculated value. To handle this problem, visit the Home tab, and with the Number group, change the formatting to General.
Did you learn about how to show Formula as text in excel? You can follow WPS Academy to learn more features of Word Document, Excel Spreadsheets, and PowerPoint Slides. You can also download WPS Office to edit word documents, excel, and PowerPoint for free of cost. Download now! And get an enjoyable and easy working experience.
- 1. How to get month name from a date in Excel (3 easy ways)
- 2. Check if value is in list in Excel (3 easy methods)
- 3. How to Copy File Names in Excel from a Folder?
- 4. How to add text to beginning or end of all cells in Excel
- 5. How to color cell based on value in Excel?
- 6. How to compare two excel sheets and highlight differences