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)

31

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

MORE FROM ALLEN

Changing Your Sound Theme

Don't like the sounds that Windows has chosen to play when system events occur? Here's how to change the sound theme that ...

Discover More

Making Live URLs Into Normal Text

Convert those URLs into regular text! It's easy to do when you follow the steps in this tip.

Discover More

Displaying an Input Format in a Cell

Want to show a user, in a cell, what you expect their input to look like? Unfortuantely it cannot be done natively in Excel. ...

Discover More

Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!

More ExcelTips (menu)

Setting Row Height

When you enter information into a row on a worksheet, Excel automatically adjusts the height of the row based on what you ...

Discover More

Detecting Hidden Rows

Excel allows you to easily hide rows in a worksheet, so their contents are not visible. Figuring out how to detect where ...

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

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. 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 six more than 8?

2016-07-23 10:16:27

Deborah

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

ben

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

margo

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


2015-10-09 05:31:50

DaViD

Thank so much


2015-09-06 22:36:24

Nguyên

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


2015-05-19 09:14:52

AJ

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


2015-02-25 13:59:33

JAugusto

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


2014-11-12 09:18:48

Christi

Not working for me...


2014-11-11 13:28:01

Mary Gouldin

Not working for me :(


2014-07-02 15:12:53

Brooks

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

Tom

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


2014-04-29 09:33:29

Carl

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

Eric

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.
Problem:
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.
VBA
'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

Nees

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

Katie

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

Angela

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

Dave

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


2012-08-09 11:53:15

Gabriel

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


2012-08-09 05:06:10

Steffen

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

Barb

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

JGarrity

So simple, it's BRILLIANT!


2012-02-27 07:55:14

Cathy

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

Paul

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