Got lots of text in a cell? Make it readable

Today’s blog post is brought to you by Gary Willoughby. Gary is a writer on Office.com who has created and edited content about Excel, Access, and Project.

Sometimes, I find I have text in a cell that spans far past its own column or…yikes, maybe even off the screen. Because I tend to be a little borderline compulsive, that bothers me just a bit.

Of course, if I autosize the cell by clicking the column border on its right edge, all the cells below that cell could end up being unnecessarily too wide…and worse, the rightmost part of the cell might go off the screen entirely. I think I hate that even more. In fact, yes…I’m sure I do. I want my columns back.

At this point, I realize I need to wrap the text. By now, I’m fully invested in making this look just right. To wrap the text, I use…the Wrap Text command (that’s a great name!). In Excel 2010 and Excel 2007, it’s right there, waiting to be used, in the Alignment group on the Ribbon. For those of you who prefer the keyboard, you can also use the KeyTip Alt+H+W in Excel 2010 and 2007.

In other versions, such as Excel 2003 and earlier, you get to the Wrap Text command through the Format Cells dialog box (on the Format menu, click Cells and then click the Alignment tab). And for those fans of keyboard combinations…uh, here it is: Alt+O+C+A+Alt+W. Or you can use the slightly shorter Ctrl+1+A+Alt+W. And that makes me appreciate the versions of Excel that have the Ribbon even more.

Now, with the cell selected, I click Wrap Text. Most of the time, wrapping text will initially result in a very tall cell with just a few characters per line. OK, so I’m not done quite yet. The perfectionist in me just won’t let it go. My next step is to make the row the height I want (maybe two or three rows high), so I drag its bottom border up until its height is what I have in mind. At this point, I might see only part of the cell’s text. That means I’ll need to make the column wider.

I drag the right border of the column until it’s the width that makes me happy (or just happier? I’m not that easy to please). Now, looking at the following image, you might notice that it’s a little taller than it needs to be.

So I double-click the bottom border of that cell to autosize it. And that’s a wrap!

An alternative method that some people use to wrap text is to move the cursor to the end of the cell in edit mode, press ALT+Enter, and then press Enter. A downside of this method is that you’ve just added a line break to the end of the cell. To get rid of that line break, you need to select the cell again and then manually delete that blank line in edit mode.
But if you like this method, I won’t try to stop you (and really, how could I?).

In addition to wrapping text, there may be times when I want to force new lines (also known as line breaks) in a cell. I do this by pressing ALT+Enter (that key combination I just discussed, above) when I’m editing inside the cell. I can add as many lines as I want. Here, I want to break to new lines after the first, second, and fourth sentences. After all, what the world really needs is more open space.

Here’s the same cell with line breaks that I inserted by pressing ALT+Enter twice (to get both a new line and a blank line) at the end of the first and third sentences:

There. Looking at this cell makes me feel all warm and fuzzy now!