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)


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


Adding AutoShapes

The graphics features of Excel allow you to add a number of predefined AutoShapes to a workbook. If you want to add ...

Discover More

Creating a Plus/Minus Button

Want a quick way to convert positive values to negative and vice versa? You can create your own plus/minus button by ...

Discover More

Bumping Numbers in a Document

If your documents include words that contain numbers (such as a list of parts numbers) you may need a way to increment ...

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 (ribbon)

Hiding a Huge Number of Rows

Need to hide a large number of rows? It's easy to do if you combine a few keyboard shortcuts. Here are several techniques ...

Discover More

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

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

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 two less than 5?

2019-02-28 07:25:17


I love simple workarounds to a complex problem - like this one! Thank you so much!

2018-11-14 03:45:54

freddy Lemmens

Excel 2016: it only works after the first cell settings, but when you modify the content of a cell, it does not work anymore.

2018-11-13 06:35:41

Willy Vanhaelen

Excel does this some times when the column width is very tight. Before printing you can Print Preview and then widen the column(s) involved. Or you can highlight the columns to print and select Format | AutoFit Column Width

2018-11-12 14:20:46

Rachel L Fuss

Thanks for the tip!
But how do I ensure that all text is visible when I print the spreadsheet?
Everything appears in cell when I look at cell/merged cells on computer, but text at end of cell doesn't always print to printer.
Any suggestions?

2018-08-22 09:17:26

Chris Wagner

This is a simple solution to what I always thought about doing via VBA but didn't feel like taking the time. Thanks!

2018-06-10 02:06:36


I made add-in for Auto fit row height of multiple merged cells.
Please use this, if you want to autofit row hight.
[Release Ver2.6 · toowaki/AutoFitRowEx · GitHub]

2018-04-18 14:01:25


Oh my god, this is genius, thanks a bunch, it resolved a big issue I was having at work.

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


This is really helpfull

2016-12-06 10:33:43


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
S = ActiveCell.Value
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


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


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


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


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.


2016-03-15 05:53:25


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


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


thx a million

2015-11-09 05:21:53


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


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

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.