Numbers view different when clicked on cell

[joey]

I have a data export that views correctly with numbers at two decimals
(12.56) but when I click on the cell - the number display with eight
decimals (12.561212230. i have tried changing the format globally but it
won't change. I can change them individually but was wondering what would
make this happen?

Jo

 

[Sheeloo]

If you enter or import 12.561212230 in a cell that is what will be stored by
Excel. Display will change based on the cell format but underlying value will
remain same.

See http://www.cpearson.com/excel/rounding.htm for an excellent discussion.

 

[joey]

Thanks so much! So good to know the "whys"

If you enter or import 12.561212230 in a cell that is what will be stored by
Excel. Display will change based on the cell format but underlying value will
remain same.

See http://www.cpearson.com/excel/rounding.htm for an excellent discussion.

 

[Jerry W. Lewis]

Sheeloo correctly noted that formatting changes the display, not the
underlying value. There is an Excel calculation option "Precision as
displayed" that will permanently truncate all cell values to format
precision. In Excel 2003 (I don't know about 2007), it does not truncate to
the display precision that is an artifact of column width rather than cell
formatting. Where a cell contains a formula, the calculated result will be
truncated to the formatted precision, but the formula itself will not be
changed in any way.

Beware that there may be unintended consequences due to reducing the
precision of formula results. For example if A1 contains the formula =3.1/2
formatted to display no decimal places, then the formula =2*A1 will return 4
instead of 3. 1. However, turning once the option is turned off, =2*A1 will
again return 3.1, because the formula =3.1/2 was never changed. Thus you
could turn the option on to reduce the precision of all constants, and then
turn it off.

A less pervasive solution would be to selectively use the ROUND function.

Jerry