View Full Version : Excel 2000: Formating cells for numbers produces #### display

Billy T
27-11-2002, 01:52 PM
Hi Team

I am entering reams of data into Excel for analysis and graphing. Thr data is a series of whole and/or decmal fractions, both positive & negative values.

For eaxample a dta row mighr contin the following sequence:

-.026 -.040 .117 -.239 -.232 .184 .064 etc.

The cells defaulted to two places of decimal only which was affecting the accuracy of the data.

I tried to reformat the cells en masse but various cells changed to ####.

Even trying to reformat individual cells sometimes produces #### but other times they are ok.

I have experienced this effect before but never enough to worry about, I always got around it by one means or another, usually pasting correctly formated cells to the affected areas. This time there is to much data to do this. Any ideas on what the problem is ?:|


Billy 8-{) :(

27-11-2002, 01:58 PM
Highlight the columns affected, then go Format - Column - Autofit selection

If you want to increase the number of decimals, highlight all cells, click on the "," button (comma) on the toolbar (sets all highlighted cells to 2 d.p.) then click on the button with a left arrow and .0 .00 on it, it will increase the resolution 1 dp at a time.

27-11-2002, 08:25 PM
Page scaling v font size can also give the effect you are having too.


Heather P
27-11-2002, 08:53 PM
If you reduce the width of a cell containing text to less than the width of the text then it still makes sense. Either it will flow into the next cell if the cell is empty or else it will truncate - but it's obvious something has been shortened.

With numbers it's not so obvious. In order to aid the human brain if the cell just ain't big enough to show all the numbers - x's will show instead.

Widen the column and all will be revealed.

Billy T
27-11-2002, 10:38 PM
The legend lives on God :O

Once more your powers have rescued me. Your blood ought to be bottled :D

Thanks too to Heather who also put me right, and Craig (I tried your suggestion and it does too, but it wasn't the problem this time)

As soon as I looked at the problem again (with the benefit of God's & Heather's advice) I could see the answer, negative values require more space so they were the first to go to hash. D'oh :8}

Now! How do I stop the darned program from thinking random negative entries are formulae? I understand that a single quote ( ' ) at the beginning of the data entry will work but how come only the odd entry goes sad on me?


Billy 8-{) :D :D

28-11-2002, 05:43 AM
Billy, they are all numeric values. You can change -.287 to text by making it :


But fixing the width is easiest. Excel does have a tendency to truncate decimals and round them (in XP, anyway) when there is no room, it's only after it can't display the whole number that it has kittens.

Also, you are correct about the minus (-) requiring more width. Double clicking on the right hand border between two column headings will auto-adjust the width to the minimum width required to display the widest number.


28-11-2002, 07:56 AM

Although Excel may truncate to a couple of decimal places, I'm pretty sure this is for viewing purposes only (in this context) and it still stores the full number.

This means using that number in formulae, functions , analysis or graphing will not affect accuracy.

eg. Cell A1 2.5 (format cell with no decimal places)
Cell A2 2
Cell A3 =A1*A2

will produce this result


(ie it shows '3' but uses '2.5' when doing the calc)

28-11-2002, 08:53 AM
(a) Formatting is the display of an underlying number. In excel it doesn't change the number itself unless you are using round etc functions. You can always see the numer, if it is a number rather than formula etc., by highlighting the cell and looking at the formula bar and comparing that with what is actually displayed in the cell.
(b) You refer to decimal fractions. Are you clear what you mean. Excel can format numbers as decimals or as fractions, are you dealing with dollars of US shares or ?? It mightn't matter to you, but it does to the program.

Heather P
28-11-2002, 09:28 AM
Rule 1: Before playing - save! If undo doesn't work - close without saving and open the saved copy. If it's serious playing - make a backup copy first.

If you have a row full of numbers that are showing in odd ways - highlight the row (by clicking on the row name - eg "1") then Format, Cells and make your choice.

If the numbers are in columns - click on the column name - eg "A".

If you want to change the whole worksheet click on the empty box above the 1 and to the left of the A to highlight the whole thing.

Double-clicking the line between two column names will widen the cells to autofit. The same applies to a row (handy when you have word wrap set to on but can only read the first line).
Or you can drag the line if you want to set the width manually.

If you want to see whether numbers are rounded or just display as rounded - go play. Find a nice empty patch of spreadsheet (or a new one for playtime), enter a couple of numbers, format only those cells, then add them together in an unformatted cell. Are the results as expected? If yes then it worked. If no - try again.

Just remember the basic rule:
"But what if the string breaks?" said Piglet. "Well then," said Pooh "We try another piece of string."

Billy T
28-11-2002, 01:14 PM
Thanks guys n' gals

All my problems are now solved until next time :D

Heather, nice to meet up with another Winnie the Pooh fan. I just love 'em all.. My kids (affectionately?) refer to me as their 'bear of very little brain', either that or 'the old bald guy', a character I do not recall as being one of A A Milne's.

I also use the "close without saving" for minor excursions but for greater experimentation I save as a new working copy and play to my heart's content.


Billy 8-{) :D :D :D

28-11-2002, 01:20 PM
I might need you to proof read my next book on Excel. If there is another, that is.

Heather P
28-11-2002, 01:26 PM
I'm always in the market for a spot of paid employment. A multitude of skills including proof reading, testing and manual writing.

Susan B
28-11-2002, 01:45 PM
>> For eaxample a dta row mighr contin the following sequence:

:O Gosh Billy, you haven't been into the Xmas cake grog again, have you?

28-11-2002, 01:56 PM
Pay? You think they should PAY me? Gee, I never thought to ask about pay.....

Heather P
28-11-2002, 02:03 PM
Yeah.. the problem with computer knowledge is that everyone assumes it's like fishing tips - free for the asking. Quite often it is - but occasionally the cat needs feeding.

Billy T
28-11-2002, 04:36 PM
Sory Susen

I gess my fingrs kinda ran awey with me in the heet oof the momint. I was so anoyed at not being able to compleet my werk that I just fergot miself and my speling went to pot. Plese be jentil with me.

I'll go stand in the corner now and read my dictionary to get ready for tomorrows WFTWE. Nobody knows if I spell that right or not, except Jen who always checks to see if I'm kidding.

Wonders...........what will be topical or interesting tomorrow ?:| Good words are hard to find.


Billy 8-{) :D

Susan B
28-11-2002, 04:40 PM
>> Wonders...........what will be topical or interesting tomorrow Good words are hard to find.

Billy, I'm sure you would find PLENTY of inspiration in Liam's Chat Room (http://www.chatf1.net.nz/). Eight o'clock onwards.... ]:) :D