Automatic Row Height For Merged Cells with Text Wrap
Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Automatic Row Height For Merged Cells with Text Wrap.
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.)
- Merge the cells in columns A and B as desired.
- 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.
- 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.)
- 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 (3207) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Automatic Row Height For Merged Cells with Text Wrap.
Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!
Comments for this tip:
AJ 19 May 2015, 09:14
This sounds great and simple, but it is not working 2007 version.
JAugusto 25 Feb 2015, 13:59
Not working... Need to, manually, click on merge cell or wrap text. Excel 2013
Christi 12 Nov 2014, 09:18
Not working for me...
Mary Gouldin 11 Nov 2014, 13:28
Not working for me :(
Brooks 02 Jul 2014, 15:12
Thanks for the tip...but...I'm still very annoyed that this basic problem has been in MS Excel for sooo long, through a number of major releases, and STILL exists in Excel 2013 :(
Tom 06 May 2014, 08:27
Very clever and thank you indeed! It kills me that I did not think of it myself!
Carl 29 Apr 2014, 09:33
This is the BEST way to go WITHOUT using a macro. If anyone does not get how to use this in there spreadsheet send me an email and I will help you ... its that EZ
Eric 05 Mar 2014, 09:39
Brilliant workaround! Thanks
Johnny Lindulf 08 Apr 2013, 10:42
Great. Thanks to you and to Google to put you on the first page. Solves my problems.
I put it into a VBA loop to adjust the row heigts individually to each row from row_1 to row_n.
If you delete the assisting column afterwards, the row heigts will revert to standard height.
If you run through your rows a second time within the merged area and simply find the row height, put it into a variable and then set the row height to the value of the variable, the row heights will prevail.
'for each row, freeze the row heigt
For r = row_1 To row_n
Range("B" & r).Select
rh = Selection.RowHeight
Selection.RowHeight = rh
Adam E 29 Mar 2013, 12:19
Quite ingenious, thanks so much for this tip!
Nees 22 Mar 2013, 14:43
I'm using Excel 2010 but the row height doesn't changes automatically. I have to double click the row seperator.
Katie 18 Feb 2013, 23:36
Thank you SO much! My manager wanted something that did not inolve VBA or macros, and this worked perfectly!!!
Dragan Bogdanovic 06 Dec 2012, 11:33
Thank You for my time!
Angela 28 Nov 2012, 16:55
Sounds like a tip that I can use! However, I'm unsure how to set it up. I have columns A-H merged on several different rows of which I need the text to wrap. I'm unsure how to write the reference and whether I need to write several references. Please advise. Thanks!
Dave 10 Oct 2012, 14:30
Good option, but doesn't seem to work if you use Alt+Enter to create new lines. Thanks.
Gabriel 09 Aug 2012, 11:53
This is a solutions for dummies like me. Simple nice and easy. Cheers, thank you very much.
Steffen 09 Aug 2012, 05:06
What a great (and actually simple) tip to solve the auto row height problem with merged cells in Excel. Thank you very much!!!
Gadi Bizinyan 10 May 2012, 16:11
Great tip, I'm sure a lot of users run into this issue one time or another. I wonder why this has never been fixed in the newer versions of Excel.
For each set of merged cells in a row, you have to make an additional column with a reference formula. For what you described, you'll need 5 reference columns.
It's very helpful if you trigger the row to expand with the Sheet_Change event. When you use the following line in your macro:
Rows(Target.Row & ":" & Target.Row).EntireRow.AutoFit
Excel will automatically adjust the row according to the cell that requires the largest row height.
Barb 10 Apr 2012, 21:15
Automatic Row Height For Merged Cells with Text Wrap
What if you have 10 columns (a,b,c,d,e,f,g,h,i,j) - (a&b merged, c&d merged, e&f merged, g&h merged, i&j merged). You will never know which one of the merged cells will have the most information in it. ie. 1 week e&f could have 3 lines of text, the second week a&b could have 4 lines of text. Can you have it automatically work out which merge has the most and conform the autofit to the height needed for that cell merge.
JGarrity 29 Mar 2012, 11:37
So simple, it's BRILLIANT!
Cathy 27 Feb 2012, 07:55
Thank you very much
C.P. Gardiner 15 Jan 2012, 15:59
THANK YOU, THANK YOU, very much indeed, for this tip! I just spent a frustrating morning trying to find a workaround.
This problem (failing to adjust line height automatically to display wrapped text in merged cell) is typical of the inability of Microsoft products to do a good job on text.
The problem does not occur in QuattroPro, the spreadsheet in WordPerfect Office, which handles text much better than Excel; and of course, WordPerfect is still superior to MS Word. Alas, I have to use Excel to produce some forms for a person who only has MS Office (preloaded, of course--that's how MS has come to dominate the market with inferior products!
Thank you also for listening to my rant!
Greg Olson 30 Nov 2011, 14:08
You are a genius. Excellent suggestion on working around Excel's limited merge wrap functions.
Paul 12 Nov 2011, 19:14
You are a damn genious, you have no idea how much this just helped me. Thank you
Leave your own comment: