How to annotate values in a cell?

Topic

New Reply

I’m trying to remember an Excel tip I learned years ago, and I’m drawing a blank. I think it is possible to enter a value or formula in a cell, enter some character such as a semi-colon and follow it by text. The text is displayed, but does not interfere with the usage of the value. Am I crazy? Or did/does such a capability exist.

Since I’m simply trying to jog my memory, I am not interested in work-arounds such as comments or concatenation. Thanks for your help.
ENA :confused:

Reply | Quote

Replies

You can do this with number formatting.
Format cells, number, custom and something like:
0.000″: =pi”

to display pi as:
3.142: =pi

Steve

Reply | Quote

Hmmm! That’s new. Not what I had in mind, but nifty. I wonder if I’m remembering a Lotus 1-2-3 feature and not Excel at all. I may have holes in my head.
Thanks.

Reply | Quote

I remember that. It was Lotus 1-2-3 and it was a semi-colon. It has always seemed to me like a useful capability, since it brings the explanation closer to the cell contents (as opposed to a comment). Particularly when the cell contains a formula. Not sure why Microsoft never incorporated it – or something similar.

Reply | Quote

If you want to annotate a cell, you can use comments or you can use the N function. Enter something like:
=5 + N(“This is a note”)

The cell will still have the value of 5, since N(“text”) = 0…

Steve

Reply | Quote

Thanks to Steve (sdckapr) for the N function tip – I’d never heard of that one before.

Reply | Quote

There are several things that XL never incorporated from Lotus123. Datalabels for charts from a range and the ability to format in a formula are my 2 main peeves. The datalabels can be done via a macro (and there is an addin to do it), but the 2nd, excel has no capability to do…

Steve

Reply | Quote

Use an apostrophe (‘) that will show the rest of your typing as text in the cell. For the two examples above ‘3.14=pi will show 3.14=pi in the cell and ‘=5 + N(“This is a note”) will show exactly that =5 + N(“This is a note”) – I know you don’t want to show the function but it could be of use to show what is being calculated or anything else you want to display. Just remember, there is no calculating on the value presented as it is actually text.

Reply | Quote

To annotate in Excel try this:

=A3&”text”

Reply | Quote