AutoFit, which Excel uses for row height, does not work with merged cells. There are, however, ways around this limitation, but unfortunately, none of them are easy OR automatic. If you are intent on using them, you can try the trick below as long as the merged cells are in A2:M2.
Follow along to learn how:
- Select a cell that is distinct and apart from your data table, such as O 53.
- In that cell, enter this formula: =A2. Cell O53 should now contain the same text that ws in the merged cells As:M2.
- Be certain that the formatting of the cell is the same as the formatting of cells A2:M2. The only formatting that should be different is that cell O53 should not be merged with another cell in any way.
- Also, be sure that the Wrap setting is enabled for that cell.
- Calculate the combined width of all the cells that make up the merged cell. (i.e., if columns A through M have individual widths of 9, then the combined width would be 117. Subtract 2 from this sum which would make it 115).
- Set the width of column O to the calculated width you determined above.
Your text should now be wrapped to multiple lines and the row height automatically adjusts.
- Check the row height of row 53 by clicking the Home tab and click on Format | Row Height in the Cells group.
- Manually set the height of row 2 to the same row height you determined in Row 53, above.
- Delete column O and row 53.
The reason you subtracted two when calculating the width is that it forces a slightly narrower column width, and slightly different text wrapping. This will come in handy at a later time when your are viewing your worksheet using a different zoom factor or when you might use a different printer driver.