Excel showing formula not result

Excel showing formula not result

If you have come across a situation where Excel is showing a formula and not the result you know how frustrating this can be. The reason for this is fairly obvious but how to get it back to being a formula requires that you trick Excel.

The reason for Excel showing formula not result

The reason this happens is because the cells which contain the formula have been formatted as text. You may have explicitly formatted them as text but more often it is a download or import from another system and the system has made all cells text. If you now come along and try add formula to the spreadsheet it may assume that you want to see the text.

To get the formula to work you may need to do 2 steps.

Step 1 is to format the cells as General, or a number, or anything else besides text. If you do this before you create the formula you should be OK.

If you have already created the formula and now do this, you may find that the formula stay behind. If you click in a cell and then click enter if becomes a formula but you do not want to spend the next few hours clicking in and out of cells.

Step 2 then is to trick Excel into going into and out of the cells. On the assumption that all formula start with an equals (=) sign you can highlight the affected cells and do a FIND/ REPLACE where you find all the = and replace them with =. Although we are doing nothing new to the cells, Excel will go into each cell and remove the = and put in a = which will activate the cell.