Tip to Stop Excel Displaying the Formula Instead of the Result

A quite common complaint of Excel users is that they can see the formula of a cell instead of the formula results; for example:

instead of:

This usually happens because one of two problems.

1. The current worksheet is set to display the formulas instead of the results
2. The cell's format was set as text before the formula was entered, so Excel considers the contents of the cell as text and will not calculate them.

The Solution

To fix the first issue, either type Ctrl+Tilde (the top-left button on the keyboard, showing the ~ sign) or use a menu/ribbon command.
If you are using Excel 2003 and older, select the Options command from the Tools menu, click the View tab, and clear the Show Formulas check box. If you are using Excel 2007 and newer, click the Formulas tab on the ribbon and toggle the Show Formulas button in the Formula Auditing area.

To handle the second issue, right-click the cell to show the context menu then select 'Format Cells...'

In the Number tab, select 'General' instead of 'Text', and press OK.

This will still not restore the formula to work as one more step is needed. As you will see, the formula is still in the cell. You will now have to edit the cell contents (either by pressing F2 on the keyboard or clicking in the formula bar) and then press Enter on the keyboard to OK the edit. Excel will now calculate the formula and display the results.

Summary

This is a remarkably common problem and as you can see, while the solution is usually simple, it is only easy when you k now how!

About the author

Yoav is the CEO of Cogniview, a company that creates software to convert PDF to Excel.

He also co-authors Codswallop, a technology and productivity blog.

Join Yoav on Facebook or Twitter for more Excel tips.