Excel formulas showing as text instead of displaying result

By 5foot1 ·
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

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -


by TobiF In reply to Excel formulas showing as ...

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)

Collapse -

Ctrl ~

by oldbaritone In reply to Excel formulas showing as ...

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.)

Collapse -

I think this is the right answer

by TobiF In reply to Excel formulas showing as ...

The following I tried in Excel 2003.

If a cell is formatted as "Text", then the format painter <b>won't change that</b>.

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.

Related Discussions

Related Forums