Wednesday, March 04, 2015

Excuse me, your formula's showing

Formatting slip



Try this experiment. On a sample worksheet, enter some arbitrary data in say the A1:B5 range.
Select the C column and format it as Text.
(Right click choose Format Cells - on the Number tab choose Text)

In cell C1 enter a function, such as =sum(a1:b1)

With C1 still selected, double click the Fill handle
(the tiny box at the lower right corner of the cell.)

The formula is filled down the column as long as there is data in an adjacent column.

But wait! I don't see the value. I see the formulas!
In addition, the formulas are still in lower case and the relative references have not been updated.

Easy to fix, I hear you say. Just reformat the column as General.

Nothing happens.

To fix the problem, make sure the column is formatted as General.

Select the first cell. Click in the formula bar and hit the Enter key.

Now double click the Fill handle.

(You could also use Edit>Replace to replace = with =. However, Relative references will be incorrect and unless you have reformatted the whole column as General, any new formulas will still display as text.)

The reverse also causes a problem. In a column formatted as General, enter some formulas.
Now reformat the column as Text. The formulas still work, but if you edit one of them, it reverts to a text display.

Microsoft KB:
Cell Linked to Text-Formatted Cell Shows Formula Not Value

Formulas can, of course, be toggled using CTRL+~ (Tilde)
(Though it really should be called CTRL+` (Grave Accent), since the Shift key is not used.)



See all Topics

No comments: