Software·43,767 discussions

Excel formulas showing as text instead of displaying result

Locked

I have a large database in Excel that utilises a variety of formulas. On occasion when I edit one of these formulas it changes from displaying a result to a text version of the formula, eg vlookup changes to displaying =vlookup(…) rather than the outcome from the formula. It happens when I edit an existing formula within a column, or attempt to copy and paste a formula over from another column to try and sort out this problem. Until the problem arises the formulas I’ve been using have worked fine and I stress that there isn’t an problem with the formula structure, it’s more like the number format within the edited cell changes to text when I edit it for some reason. I’ve tried using the format painter from a “good” cell but still no dice – I’d be grateful for any advice or suggestions – many thanks

Topic

Clarifications

In reply to Excel formulas showing as text instead of displaying result

Clarifications

F9?

In reply to Excel formulas showing as text instead of displaying result

Have you tried forcing a recalculation? (I think it’s F9)
Theory: Perhaps this sometimes happens if you have automatic recalculation disabled (reasonable for large amounts of data)

Ctrl ~

In reply to Excel formulas showing as text instead of displaying result

To toggle between formula view and result view, hit Ctrl-tilde (the squiggle line that looks sort-of like an S on its side)

that will change back and forth between (e.g.)
=2+2
and
4

I think this is the right answer

In reply to Excel formulas showing as text instead of displaying result

The following I tried in Excel 2003.

If a cell is formatted as “Text”, then the format painter won’t change that.

You need to change to a suitable number format instead.
Press Ctrl-1 (Ctrl and number “one”) and select, say, “general” under the “Number” tab.

Please also note, that if a formula is treated as a text value, then the formula won’t correctly change references when it is copied of moved around.

[Author]

Replies