EXCEL 4.0 - Why can't numbers overflow a cell boundary, text can!
Art Mulder
In Excel 4.0 on a Mac (like most spreadsheets), when you type text
into a cell, and the text is wider than the cell, it will overflow
the cell to the right, until the first non-empty cell is
encountered.
In contrast, when you enter a number, and that number is wider than
the cell/column, you end up with a cell full of "#####".
WHY can't numbers overflow to the left, the same way text overflows
to the right?
I have a Mac Classic II. Small Screen. I don't *want* to make my
columns any wider. I want to fit as much data on the screen as
possible.
The problem usually only occurs with column totals. I set up my
colums to accomodate the numbers that I enter. But then down at the
bottom, where I total up the column, the number is often too wide.
Why should I have to make my entire column wider when I only need the
extra space for *one* cell!
I'm open to suggestions.
...art
--
...art mulder ( a...@cs.ualberta.ca ) | "Do not be conformed to this world,
Department of Computing Science | but be transformed by the renewal
University of Alberta, Edmonton, Canada | of your mind, ..." Romans 12:2
Ken Plochinski
[concerning Excel 4.0]
> when you enter a number, and that number is wider than
> the cell/column, you end up with a cell full of "#####".
>
> WHY can't numbers overflow to the left, the same way text overflows
> to the right?
You can get numbers to flow across more than one column by using the
"center across columns" tool (just right of the "right align" tool in the
standard toolbar). The number needs to be in the leftmost of the
columns you're centering across. This isn't exactly what you're
looking for, but it should work.
Ken
Charles Corbato
>This is an open question. (Some might call it a rhetorical one.)
>
> In Excel 4.0 on a Mac (like most spreadsheets), when you type text
> into a cell, and the text is wider than the cell, it will overflow
> the cell to the right, until the first non-empty cell is
> encountered.
> WHY can't numbers overflow to the left, the same way text overflows
> to the right?
> The problem usually only occurs with column totals. I set up my
> colums to accomodate the numbers that I enter. But then down at the
> bottom, where I total up the column, the number is often too wide.
> Why should I have to make my entire column wider when I only need the
> extra space for *one* cell!
One solution is to convert your total to text using the function
TEXT(value,format_text) and then right-justify it in the cell so it
spills to the left. [format_text is the format symbol that is normally
used to format cells and describes the format of the value you want to
convert to text.]
--
+----------------------------------------------------------------------+
| Charles Corbato Internet: corb...@osu.edu or |
| Ohio State Univ, Columbus, OH ccor...@magnus.acs.ohio-state.edu |
+----------------------------------------------------------------------+
Art Mulder
>In article <art.723228496@radway>, a...@cs.UAlberta.CA (Art Mulder) wrote:
>> In contrast, when you enter a number, and that number is wider than
>> the cell/column, you end up with a cell full of "#####".
>Because with numbers, if you don't get the whole number, and you're
>reviewing information the results can be disasterous. What if you printed
>and a zero was left off the end because of overflow? Bad news.
This is a good point, but it I think it could be quite easily dealt
with:
If the cell to the left of your number is empty, spill over
onto that cell, if not then fill cell with "#####".
>This ##### replacement of numbers that do not fit inside the defined field
>length is common wherever you find numbers and databases. Oracle's report
>writer does this to.
I realize that it is common, but I still don't like it, and would like
to be able to override it in my spreadsheet.
>Change the font in the totals row to be narrower or smaller.
The total is on of the more important values, I have no desire to
make it harder to read by shrinking the font.
Thanks for your comments though!
Dale Parkyn
I like the TEXT converter tip too. Here is mine.
I like to make a two column summary area on the sheet. The first column
will have the title of a column elsewhere. the second column has the
total from the bottom of that same column elsewhere.
Column titles can be copied and pasted (paste special?) transposed to
form the column of 'column titles'.
Now you need widen fewer columns...
Jim Prall
> In Excel 4.0 on a Mac (like most spreadsheets), when you type text
> into a cell, and the text is wider than the cell, it will overflow
> the cell to the right, until the first non-empty cell is
> encountered.
> In contrast, when you enter a number, and that number is wider than
> the cell/column, you end up with a cell full of "#####".
> WHY can't numbers overflow to the left, the same way text overflows
> to the right? I don't *want* to make my columns any wider.
> The problem usually only occurs with column totals. I set up my
> colums to accomodate the numbers that I enter. But then down at the
> bottom, where I total up the column, the number is often too wide.
> Why should I have to make my entire column wider when I only need the
> extra space for *one* cell!
Art,
Good question. Hope those spreadsheet publishers listen. For now, here
are a couple of workarounds:
If one or two cells in a column have more digits than the rest, change
the font on those cells to a narrower font. (smaller size, if needed,
though I know it makes little sense to have small type for a TOTAL!)
Assign a custom number format to the problem cells to shorten the
display, eg. leave out commas, $ or whatever - or use a formulat to
divide the total by 1000 and then label the total distinctly...
Or, here's a trick: put numbers and formulas in a column and make that
column have width zero or nearly zero. Beside it, make a column of the
desired width, and fill each cell with a function that converts the
neighboring number value to a string. The cells value is now text,
and it will be allowed to overflow, if only to the right instead of the
left. Oh well.
--
--
--Jim Prall jimp%trigra...@csri.toronto.edu
--Trigraph, Inc. (If your mailer gives bad reply path, use the above.)
--Toronto, CANADA "fax it to my car phone" "The CPU of the SLC is in the CRT"