Question Locked

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

0 helpful
15 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
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?

15 replies

by lookingforhelp

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

by seanferd

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?

by tedski2000

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.

by who am i really

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

by tedski2000

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.

by who am i really

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

ie.
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

ie.
if

12,000 cases of hotdogs in the large freezer

doesn't fit in the cell

you could put
hotdogs
or
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

by tobif

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.)

by seanferd

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

by orgle

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 :)

by gcram

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.

by dogknees

Select the cell, go to Format Cells / Alignment and set the Horizontal to Left(Indent).

Regards

by Wizard57M-TR

You do realize the age of this question? Unusual to see a long time
member awaken a zombie.
Wizard57M
TR Moderator

by pibel

This extends the answer of TobiF

To prevent cell content from spilling over (ON)
Select all blank cells, make them referencing to a chosen cell
Enter a SPACE into the chosen cell (step by step below)

1- Choose a cell that you will never need on your sheet (e.g. A1)
2- Go to the chosen cell (A1) , hit SPACE and then ENTER
3- Select the area where you do not want the overlapping
4- Select "F5", "Special", "blank", hit Okay
5- With all blank cells selected, enter in the formula bar +$A$1 (absolute reference to the chosen cell), then hit Ctrl+ENTER to enter the formula in all selected cells at once).

To revert the previous action (OFF)
Select all referenced cells and Delete the reference in all of them (step by step below)

1-Select the chosen cell (in our case go with the cursor to A1)
2- Select "F5", "Special", "Dependents", hit Okay, hit Delete and you are back to the original worksheet.

by mac3lite

To stop content from overlaying the blank cells to it's right, all you have to do is....

- Select the Cell that contains the content
- Right Click (Crtl-1) and select "Format Cells"
- Click on the "Alignment" tab
- Look in the Text Alignment area specifically at the Horizontal Drop Down Menu
- Under Horizontal you want to select "Fill" - Now click OK
- That's it... Problem Solved! -

*I'm currently using Excel:mac 2011 - but I'm pretty sure the "Fill" option has been available in most of the previous releases.
Good Day! :)

This discussion is closed to new replies.