Excel: How can you restrain text from flowing into adjacent blank cells?

By tedski2000 ·
I use Excel to list my inventory for an online auction site. When you type in text that is longer than the width of the cell (say,cell B2), the text will spill over into the adjacent cell (B3) if it is blank. This makes it very difficult to tell at a glance if I have left cells blank that need to be filled in. I do not want to resort to "text wrap." Is there a way to restrain the text to its own cell?

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

Request for Clarification

by lookingforhelp In reply to Clarifications

tedski, did you ever solve this? I have the same problem.
Many thanks

Collapse -

Hm. So, what behavior would you like?

by seanferd In reply to Excel: How can you restra ...

Would you rather the cell expand to contain the text? Or would you prefer the text simply "disappear" but continue "under' the following cells? Something else?

Collapse -

I want truncated text.

by tedski2000 In reply to Hm. So, what behavior wou ...

I want the cell size to remain at whatever I set it at; I don't want it to expand to fit the text; and I don't want the text to wrap so that the cell expands vertically. I just want the text to, as you put it, disappear and continue under the following cells -- just the same as if the adjacent cell was already filled. I need my blank cells to remain blank, and not have overflow text covering them.

Collapse -

adjust to fit

by Who Am I Really In reply to Excel: How can you restra ...

double click the cell margin at the top
and it it will adjust the cell width to fit the widest entry

Collapse -

That is not suitable, either.

by tedski2000 In reply to adjust to fit

I want to keep all my columns within the screen, without scrolling horizontally. One or more cells could be as wide as the whole screen if I fit the cell to the text.

Collapse -

Excel 2003 hides text . . .

by Who Am I Really In reply to That is not suitable, eit ...

only when the cell next to it contains something
but overflows the cells when the next cell(s) are empty

if your sheet contained the following four entries:
|ffffffffffffffffffffff |
|ffffff| not empty cell |
|ffffffffffffffffffffff |

all three cells which contain the repeated "f"

have the same amount of "f"

but the middle one on the same row as the "not empty cell" gets truncated in the display and the cell boundaries

the others overflow because there is nothing in the cells beside

so to answer your question about knowing which cells are empty
the ones that overflow are next to empty cells

I don't know of any other way to prevent the overflow other than to expand the cells to fit or put something in the cell next to the overflowing one
the only other option I can think of is type the basic info necessary into the cell and the rest into a cell note


12,000 cases of hotdogs in the large freezer

doesn't fit in the cell

you could put
cases hotdogs

in the cell
and put the full description in a cell note

sure it makes extra work
having to add the notes
but the cells won't flood the next ones

edited: additional info

Collapse -

A couple of approaches

by TobiF In reply to Excel: How can you restra ...

If you have Excel 2007 or newer, then you can try "conditional formatting" to quickly spot empty cells.
(You have a minimum of conditional formatting in Excel 2003, but I don't see how it can be tamed to help you in this situation.

Another approach, which works fine with virtually any Excel version is this:
1. Select your table
2. Goto (F5) Special... Blanks
Now all empty cells in your region should be selected.
3. Hit "space" once, then ctrl+enter to insert the space in all selected cells at once.
(By the way, with all empty cells selected, you can walk through these cells using tab or enter.)

Collapse -

In 2k3

by seanferd In reply to A couple of approaches

shrink to fit may be the only option, but that may not be suitable either, depending on what tedski2000 wants.

Collapse -

Reponse To Answer

by Orgle In reply to A couple of approaches

I simply HAD to create an account just so I could thank you for this answer. I would be thrilled if Excel created a "Contrain Text" option next to "Wrap Text", but this seems to be the answer in the mean time :)

Collapse -

If you don't need to see all the text...

by gcram In reply to Excel: How can you restra ...

Format the column to "wrap text," then change the height of all of your rows to single-space height. That will hide the lower parts of the wrapped text.

Related Discussions

Related Forums