• Excel Book Excerpt

Excel How to Fit a Multiline Heading into One Cell

This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

How to Fit a Multiline Heading
into One Cell

Problem: In “How to Set Up Your Data for Easy Sorting and Subtotals," you say that headings should occupy only one row to allow for easy sorting. My manager requires that I format a report to have the heading “Prior Year" split, with “Prior" in one row and “Year" in a second row. How can I make my manager happy while also following the list format rules?

LE10000604.jpg 

Figure 584 Your manager wants this heading on two rows.

Strategy: This is a very real problem, where form meets function. The right thing to do in Excel is to have “Prior Year" in one cell. But some managers absolutely, positively want the formatting to be exactly as they specify. Luckily, there is a strategy that makes it possible to make the manager happy and to correctly set up the data set in Excel, too.

In cell X5, you type the word Prior. Then you hold down Alt while pressing Enter and type the word Year. The Alt+Enter combination adds a linefeed character in the cell. You can delete the old heading in X4 by moving the cell pointer there and pressing the Delete key.

LE10000605.jpg 

Figure 585 Use Alt+Enter to go to the next line.

Results: You have a single cell that contains two lines of text. The cell will work as a heading in pivot tables, subtotals, sorting, and so on.

Additional Details: Using Alt+Enter automatically turns on the Wrap Text option for the cell. You could also turn on the Wrap Text option by choosing Home, Wrap Text icon.

LE10000609.jpg

Figure 586 Wrap Text finally has an icon starting in Excel 2007.

Turning on Wrap Text in this manner will probably work for a brief heading like “Prior Year." However, if you want to have control over a long heading, such as “Prior Year Results (Adjusted for Spin-off of the Widget Division)," then it is better to use Alt+Enter to specify exactly where the line break should occur. Wrap text uses a somewhat haphazard splitting of words.

LE10000606.jpg

Figure 587 Excel decides where to break lines.

 

As you make this column wider, Excel changes the way the words are wrapped. It is frustrating to keep adjusting the column widths until you get the words to wrap correctly.

LE10000607.jpg

Figure 588 Adjusting column widths to change line breaks rarely works.

 

Gotcha: After resizing a cell with Wrap Text, you often end up with a row height that is too tall. To correct this, you select the cell and then choose Home, Format dropdown, Autofit Row Height.

Using Alt+Enter gives you absolute control over where the heading breaks. To create the figure below, type Prior Year Results <Alt+Enter> (Adjusted for the <Alt+Enter> Spin-off of the <Alt+Enter> Widget Division). You can make the column wider and center it for the perfect-looking cell.

LE10000608.jpg

Figure 589 Press Alt+Enter to wrap the text at logical points.

<-Previous Topic Next Topic->

For more resources for Microsoft Excel: