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


Hash Marks Displayed Instead of Cell Contents

Have you ever entered information in a cell only for it to appear as hash marks? This tip explains why this happens, how ...

Discover More

Find and Replace in a Column or Row

Need to search for information in a table? Word allows you to easily limit your search to an entire column or row, as ...

Discover More

Non-standard Sorting

Information in a cell can be entered using line feeds, which results in multiple lines of data in the same cell. If you ...

Discover More

Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!

More ExcelTips (ribbon)

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

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

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 one less than 6?

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.