Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Automatic Row Height For Merged Cells with Text Wrap.

Automatic Row Height For Merged Cells with Text Wrap

by Allen Wyatt
(last updated September 5, 2015)

20

Ernie asked if there was a way for Excel to automatically adjust the row height in cells that are merged. He points out that if a cell is set with text wrapping turned on, that Excel automatically adjusts the row height for the cell so that all the wrapped text is visible. If you subsequently merge that cell with an adjacent cell, even if the adjacent cell has text wrapping turned on, then the resulting merged cell's row height is not adjusted so that all the text is visible.

Exactly why Excel does this is unclear, but there is no intrinsic way around it—Excel just does it. At first blush you may think that you can use the AutoFit feature (Format | Rows | AutoFit) to adjust the height of the row in which the merged cell is located. Doing so, however, apparently has no affect—AutoFit seems to completely ignore merged cells in doing its magic.

One way around the problem is to use a macro to set the row height to the desired height. A good approach is to have the macro determine the column width of the merged area, unmerge the columns, set the first column to that width, and determine the row height required to AutoFit it. The macro could then reset the column width, merge the cells, and set the new row height. (If that sounds like a lot, it is. Such a macro wouldn't be that trivial to create.)

If you don't want to use a macro, you can fool Excel into setting the row height properly. You do this by using a separate column that is created for the express purpose of setting row height. The process is described in the following general steps. (These steps assume you are merging the cells in columns A and B, and that your data table only includes columns A through K.)

  1. Merge the cells in columns A and B as desired.
  2. In column Z (or some other column outside of your data table range, but not immediately adjacent), put a simple formula reference to column A, as in =A1.
  3. Ensure the text formatting of column Z is exactly the same as in the merged cell, with the exception of merging. (Column Z should not be merged with anything, nor should it be marked as merged.)
  4. Make the width of column Z slightly narrower than the combined width of columns A and B.

The effect of these steps is that Excel will set the row height based upon the contents of column Z, which just happen to match the contents of the merged cell in columns A and B. This, of course, allows all the text in the merged cell to be visible. The only thing you will need to do is make sure that you don't print the contents of column Z when you do your printing.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (10563) applies to Microsoft Excel 2007, 2010, and 2013. You can find a version of this tip for the older menu interface of Excel here: Automatic Row Height For Merged Cells with Text Wrap.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Automatic Blank Pages at the End of a Section

If you want to have a blank page at the end of a document section, you can insert one manually or you can use the technique ...

Discover More

Combining Cell Contents

Excel allows you to easily combine text together. Interestingly, it provides two ways you can perform such combinations. Both ...

Discover More

Mysterious Blue Line between Paragraphs

Do you ever have mysterious lines show up between paragraphs either on your screen or on your printouts? It could be related ...

Discover More

Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!

More ExcelTips (ribbon)

Adjusting Row Height for a Number of Worksheets

Adjusting the height of a row or range of rows is relatively easy in Excel. How do you adjust the height of those same rows ...

Discover More

Hiding and Unhiding Rows

When building a worksheet, you may need to hide some of the rows or unhide other, previously hidden, rows. It's easy to do; ...

Discover More

Adjusting Row Height when Wrapping Text

If you have some cells merged in a worksheet, and you wrap text within that merged cell, Excel won't automatically resize the ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is two more than 4?

2017-07-20 09:34:44

Sohail Ahmed

Effective and very simple. Thanks


2017-06-21 11:23:40

meg g

THANK YOU!!!! Amazing thinking! The "column z" solution is simple and quick. I've been trying to figure this out for the last few days. You saved my sanity - at least kept me from going more mental. :-) Thank again!!!!!


2017-02-28 02:50:54

Sandesh

This is really helpfull


2016-12-06 10:33:43

Ben

I developed a (in my opinion) slightly more elegant solution, by estimating the length of the string and dividing by the total width of the merged cells. Considering the font size, the old row height and a correction factor which may depend on the style (in my case 0.75). This method does not require any handling with additional sheets or cells and only uses private variables.
The example below shows my approach. An additional If-statement may be required to check if the actual row height is already larger than the desired one. Copy any (long) text (e.g. Lorem Ipsum) into the A1 cell and the Makro will (hopefully) do the rest.



Private Sub CommandButton1_Click()
Dim S As String
Dim F, L, NL As Integer
Range("A1").Select
S = ActiveCell.Value
Range("B4").Select
Range(ActiveCell, ActiveCell.Offset(0, 3)).MergeCells = True
ActiveCell.Value = S
F = ActiveCell.Font.Size
L = Len(S) * F
NL = WorksheetFunction.RoundUp(L * 0.75 / Range(ActiveCell, ActiveCell.Offset(0, 3)).Width, 1)
ActiveCell.EntireRow.RowHeight = ActiveCell.EntireRow.RowHeight * NL
ActiveCell.WrapText = True
End Sub


2016-12-06 07:31:57

Carly Hook

That was super handy but didn't work on every merged cell. (?!)

Also, I need to pdf the doc & distribute, and I use the footer 'page # of #'- which of course is then inccorrect.
eg- my report is 4 pages long but due to the 'fixer Z' column, it reads as 'page # of 8'

Is there a way to solve that?

PS- Thanks Allen, I've totally learnt loads of crafty things from you.


2016-09-29 12:17:47

xls-er

This did not work; I think the issue is merged cells which include rows; not just merged columns. This seems to dis-allow any kind of automatic row height adjustment.


2016-09-28 02:56:18

Rob

It works better if you have the editable value in the single cell and have the merged cells with the formula.


2016-09-26 14:33:30

Peter Calvert

I tried this suggestion and, for it to work, I found that I had to toggle the Wrap Text button while on the Z column field to have the multiple lines appear. It didn't happen automatically. I'm using Excel 2016. Thanks, Peter


2016-07-25 10:12:00

Salman Khan

Life saver!


2016-06-14 17:08:19

Adam

What a great tip! I'm going to "fool" Excel into auto-sizing merged cells correctly using the "Column Z technique" from now on!


2016-05-16 06:07:14

JW

Great tip, thanks :)


2016-05-12 14:55:43

Junia Herd

Thank you for the workaround tip. It saved my sanity and a lot of manual effort. My team will have a better experience reviewing a spreadsheet.


2016-04-25 20:38:30

Carol Laxner

Thank you!! I was frustrated that I couldn't figure this formatting thing out, and your tip worked beautifully!! Thank you for making me look good at work!!


2016-04-01 16:02:30

Lori Aldrich

Thanks! You have totally solved one of my major headaches with formatting for excel! My forms are sooooo much easier now.


2016-03-24 10:29:36

Robert Wine

Hey, that tip was INCREDIBLE, exactly what I needed to fix an issue. Keep up the GREAT WORK.

Thanks


2016-03-15 05:53:25

Pramod

Thanks for the solution!

Another workaround could be to select any cell in the same row and do alt+enter (repeatedly to get the size of the merged row).

Cheers, Pramod


2016-02-03 10:36:32

aa

protect the sheet, and set font color of "template shadow" cell to equal with background color to render it invisible.


2016-01-22 14:58:56

Roxy

thx a million


2015-11-09 05:21:53

anny

Hi. Great solution!

But why "narrower"?

"Make the width of column Z slightly narrower than the combined width of columns A and B."

Col.A Width: 10.00 (75 pixels)
Col.B Width: 10.00 (75 pixels)
----------------
Col.Z Width: 20.00 (145 pixels), but 150 pixels are required to exactly match the size of columns A+B.

Is there something I'm missing?
Thank you.


2015-09-07 10:59:44

AJ

This appear to be the same exact instructions written for the pre 2007 users, and for me does not work in 2007. If I reverse it and say that merged A1 = Z1 and enter my info in Z1 it does work, but this makes it difficult to use the sheet. Any suggestions will be appreciated.


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.