Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Office 365. 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 February 18, 2020)


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 (Cells | Format | AutoFit or 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, 2013, 2016, 2019, and Excel in Office 365. 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. ...


Understanding Fill Effects

Want to fill a drawing shape with more than just a color? Word allows you to use all sorts of fills, as described in this ...

Discover More

Removing Duplicate Rows

Too much data in your worksheet? Does too much of that data duplicate other data? Here's how to get rid of the duplicates ...

Discover More

Creating a Copy without Formulas

Excel makes copying worksheets (duplicating them) rather easy. However, you may want a worksheet copy that differs from ...

Discover More

Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!

More ExcelTips (ribbon)

Hiding and Unhiding Rows

When building a worksheet, you may need to hide some of the rows or unhide other, previously hidden, rows. It's easy to ...

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

Formatting Subtotal Rows

Excel automatically formats subtotals for you. But what if you want to change the default to something more suitable for ...

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 seven more than 9?

2020-01-06 18:09:03

Eduard Baba

This does not work.

2019-10-28 06:04:46


Thanks for this great tip!
I made a macro for this purpose. It sums the width of the columns of merged cells, and sets the width of 100th column for this total width and it does all the needed steps as you wrote in this article.
It works almost well, but I realized that a single columns with setting the width for the sums of width of merged cells will be not the same width as the merged cells, in reality. Please look at the attached image as an example.
The default widht of a column is 8.43. Sum of width of 10 columns is 84.3. Then if you create another column with the setting width for 84.3, it will be narrower in the reality than the merged 10 cells. You can see the same number of charactes in row 3 - the text is owerflow into the 12th column.
My workaround is multiplying the sum of widths with 1.07 will give the approximate same with for both - but I'm sure it's not a correct solution.
Do you know why is this difference and how can I get the real width of merged cells?
Thanks a lot for you reply!

the main part of my macro:

auxColNewWidth = 0
For i = 1 To lastCol
auxColNewWidth = auxColNewWidth + Columns(i).ColumnWidth * 1.07
Next i
Columns(101).ColumnWidth = auxColNewWidth

ActiveCell.Offset(0, 0).Select
ActiveCell.Offset(0, 100).Select
ActiveCell.Offset(0, -100).Select
Application.CutCopyMode = False

Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(0, lastCol - 1)).Select
Rows(ActiveCell().Row).RowHeight = Rows(ActiveCell().Row).RowHeight + 0.01

(see Figure 1 below)

Figure 1. different widths

2019-07-02 10:39:50

Dave Bonin

On a related note, with un-merged cells, I often find it distracting when:
1) Excel autofits the row heights to, say, 20, 40, 60 and 80 based on how much text was wrapped, and
2) Excel autofits the row heights so that there's little white space between the text and the top and bottom cell borders.

In that case, I use something like what Allen suggested. I use a helper column (often column A) to set the height. For each row used, I enter the letter "X" in column A and give it a really large font size -- large enough to provide white space between the top and bottom borders for most text. Combining my two items, I might make the font in column A large enough so that the minimum row height is 50.

The result is that the row height variation is much less jarring to the eye and the added whitespace in most rows improve readability.

To make the "X" unobtrusive, I give it a very light gray font color. I can see it if I look for it, but most people don't notice it. Of course, I don't print column A.

I suppose I could use a non-breaking space (character 160) in column A, but it's too easy for me to miss copying or entering that character when inserting new rows. Same if I was to use white font color for the "X".

2019-06-21 06:24:02

Anwesh Gangula

This was a great tip.

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.