Overlapping Cells

[catlady]

When I enter a long number, it overlaps into the next cell. However, I do
not get the # sign. Instead, a type of formula appears. For example if I
entered 22222222222222 (14 2's), what appears is 2.22222E+14. Why does it do
this? Or, if I select that column, right click then choose Format Cells and
change to Number Format, the cell width automatically adjusts itself. Is
there somewhere in Excel Options I can change this? What am I doing wrong?

 

[Dave Peterson]

When you type a long string (not a number), excel will overlap into the adjacent
cell. But numbers don't work that way.

So either that cell has been preformatted as Text or you're starting your entry
with an apostrophe (or you're not typing a number).

But I don't understand what you're want excel to do--and what you're typing into
that cell.

 

[catlady]

I am taking an Excel Intro Course at College right now. I was reading a
section in my text book about "Entering Data in a Cell", and typed in a long
string of 2's to see that it changes to #, as stated in the book. But my
Excel 2007 at home does not do that. It gives me that funny formula. I want
the # to appear instead. Going back to my book, it states "If however, you
enter a number in a cell, specify it as a number (rather than text) and the
number is too long to fit in the cell, Excel changes the display of the
number to number symbols (###). This is because Excel does not want you to
be misled by a number when you see only a portion of it".

Is this saying I will need to specify every cell as a "number" when I type
numbers? Is there an easier way for that to happen automatically? So I do
not have to format every cell?

Hopefully I explained it better this time.

Thank you
Tanis

 

[Dave Peterson]

If the cell is formatted as general, then excel will display it the way it
thinks is best. That could even by that scientific notation (it's not really a
formula).

Try typing in
1234.12431234123412341234
into A1 (make sure it's formatted as General)

Then change the columnwidth. Make it smaller, then smaller, then smaller.

When excel determines it can't display the whole number portion of your entry,
you'll see the ##'s.

The same thing will happen if you enter those 2's and make the columnwidth too
skinny to display the important portions:

2.22222E+14
is just
2.22222 * 10^14
or
2.22222 * 100000000000000
(1 followed by 14 0's)

And you'll notice that lots of the 2's were lost in the display. The cell still
contains all 14 of those 2's, but excel just displays them in the way it finds
best.

If you don't like the way excel displays the number, you can change the format.

After you do this, try changing the columnwidth and see when the #'s appear.

 

[catlady]

Thank you Dave, that helps

If the cell is formatted as general, then excel will display it the way it
thinks is best. That could even by that scientific notation (it's not really a
formula).

Try typing in
1234.12431234123412341234
into A1 (make sure it's formatted as General)

Then change the columnwidth. Make it smaller, then smaller, then smaller.

When excel determines it can't display the whole number portion of your entry,
you'll see the ##'s.

The same thing will happen if you enter those 2's and make the columnwidth too
skinny to display the important portions:

2.22222E+14
is just
2.22222 * 10^14
or
2.22222 * 100000000000000
(1 followed by 14 0's)

And you'll notice that lots of the 2's were lost in the display. The cell still
contains all 14 of those 2's, but excel just displays them in the way it finds
best.

If you don't like the way excel displays the number, you can change the format.

After you do this, try changing the columnwidth and see when the #'s appear.