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.

Automatic Row Height For Merged Cells with Text Wrap

by Allen Wyatt
(last updated October 15, 2014)


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

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


Increasing Row Height for Printing

You may have a need to increase the height of the rows in your worksheet to "spread out" the data when it is printed. ...

Discover More

Converting a Range of URLs to Hyperlinks

Converting a single URL into a hyperlink is easy. Converting hundreds or thousands can be much harder if you have to rely ...

Discover More

Erratic Behavior of Ctrl+PgDn

Have you ever noticed that when you use Ctrl+PgDn or Ctrl+PgUp that Word may give you results you didn't expect. Here's ...

Discover More

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

More ExcelTips (menu)

Automatic Row Height for Wrapped Text

When you format a cell so that the information within it can wrap to multiple lines, you may be surprised if Excel ...

Discover More

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

Discover More

Hiding Rows Based on Two Values

It's easy to use filtering to hide rows based on the value in a cell, but how do you hide rows based on the values in two ...

Discover More

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

View most recent newsletter.


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 eight minus 5?

2017-10-11 04:38:07


Wow, so simple and so genius. We suffered long time in the office because of this bug. And your solution is so smart. Greetings from an office in Switzerland!

2016-07-23 10:16:27


I can't believe this is still a thing. Thank you for the information. But since I am trying to create a standard doc that multiple people will be using, I don't think i will be able to make it work. Whats up excel?

2016-02-24 16:27:08

Matt D

This is a great suggestion. Thank you so much!

2016-02-19 05:11:07

Ricky K

Brilliant idea :D
It works for me.

1. Merge cells you want, example: A1,B1,C1
2. Copy content of merged cell (in this case A1) to an unused column (but in a row with merged cell) example Z1
3. Get the size of merged cell (A1), and then apply the width to Z1 manually
4. At Z1, apply Wrap Text(Alignment > Wrap Text)

Maybe most of you who fail haven't applied "Wrap Text" to Z1 (in example above)

Thanks a lot

2015-11-25 20:46:53


weird. it doesn't aut adjust the height after i type my info in but when i go back and delete what I had typed, then it adjusts to what would have been the right height. any thoughts?

2015-10-23 21:10:39


Doesn't work here either but I'm running 2010 Office for Mac2011 :(

2015-10-09 05:31:50


Thank so much

2015-09-06 22:36:24


Unbeliverable, in 2015, it working.
Thank you very much

2015-05-19 09:14:52


This sounds great and simple, but it is not working 2007 version.

2015-02-25 13:59:33


Not working... Need to, manually, click on merge cell or wrap text. Excel 2013

2014-11-12 09:18:48


Not working for me...

2014-11-11 13:28:01

Mary Gouldin

Not working for me :(

2014-07-02 15:12:53


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

2014-05-06 08:27:40


Very clever and thank you indeed! It kills me that I did not think of it myself!

2014-04-29 09:33:29


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

2014-03-05 09:39:12


Brilliant workaround! Thanks

2013-04-08 10:42:54

Johnny Lindulf

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.
One Solution:
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
Next r

2013-03-29 12:19:43

Adam E

Quite ingenious, thanks so much for this tip!

2013-03-22 14:43:10


I'm using Excel 2010 but the row height doesn't changes automatically. I have to double click the row seperator.

2013-02-18 23:36:37


Thank you SO much! My manager wanted something that did not inolve VBA or macros, and this worked perfectly!!!

2012-12-06 11:33:52

Dragan Bogdanovic

Thank You for my time!

2012-11-28 16:55:45


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!

2012-10-10 14:30:06


Good option, but doesn't seem to work if you use Alt+Enter to create new lines. Thanks.

2012-08-09 11:53:15


This is a solutions for dummies like me. Simple nice and easy. Cheers, thank you very much.

2012-08-09 05:06:10


What a great (and actually simple) tip to solve the auto row height problem with merged cells in Excel. Thank you very much!!!

2012-05-10 16:11:03

Gadi Bizinyan

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.

To Barb:
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.

2012-04-10 21:15:40


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.

2012-03-29 11:37:13


So simple, it's BRILLIANT!

2012-02-27 07:55:14


Ditto ^10
Thank you very much

2012-01-15 15:59:55

C.P. Gardiner

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!

2011-11-30 14:08:04

Greg Olson

You are a genius. Excellent suggestion on working around Excel's limited merge wrap functions.

2011-11-12 19:14:12


You are a damn genious, you have no idea how much this just helped me. Thank you

This Site

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

Newest Tips

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.