Question Locked

Excel formulas showing as text instead of displaying result

0 helpful
4 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
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

4 replies

by tobif

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)

by oldbaritone

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

by tobif

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.

This discussion is closed to new replies.