AutoFit Rows for Merged Cells in Excel with Example

AutoFit Rows for Merged Cells in Microsoft Excel Spreadsheet or Worksheet with Example.

In MS Excel Worksheet / spreadsheet, when you enter text that is too wide for a column, the row height automatically adjusts to display all of the text wrapped on multiple lines in that cell if the cell is formatted to wrap text.

AutoFit Cells in Excel

However, if you merge cells in one row and format them to wrap text, Excel doesn't automatically adjust the row height. In this case, when you AutoFit the row height, Excel will shrink the cells to one row high.

You must manually adjust the row's height to display all of your text in the merged cells. If the text in these merged cells is constantly being changed, this can be a somewhat of an annoyance.

To understand this lets take an example below…..

1) In a new workbook, select cell C3 and, on the Home tab, click the Wrap Text button;

2) Type a long string of text in the cell and press Enter. As you can see, Excel automatically adjusts the row height so that all of the text is displayed in the cell;

3) Now, select cells C3 and D3 and merge them.

NOTE:- To merge two or more cells in Excel Go to the Home tab, click the dropdown arrow on the Merge & Center button in the Alignment group and select Merge Across;

4) Try to AutoFit the row height by double-clicking the bottom of the row heading. Instead of adjusting the row height to accommodate the wrapped text, Excel adjusts the cells to one row high, thereby hiding some of your text.

AutoFit Cells in Excel

To display all of the text within the merged cells, you will need to manually adjust the row height unless, of course, you know a technique that tricks Excel into automatically adjusting row heights for merged cells:

Here is also a trick, take a look…

1)  In a column to the right of your spreadsheet data and outside of your print area (e.g. column F), enter the formula =C3 and, if your worksheet has more cells that you want to AutoFit, copy it down to the last row that has data in column C;

2) Next, adjust the width of column F to be approximately equal to the width of your merged cells. For example, if column C width is 14.23 and column E width is 10.34, adjust column F to be 24.57;

3) Click the column F heading, then click the Wrap Text button on the Home tab;

4) Next, with column F still highlighted, on the Home tab, click the small arrow below the Format button in the Cells group and click AutoFit Row Height and you'll see that the row heights are automatically adjusted to accommodate the text in the merged cells.

AutoFit Cells in Excel

NOTE:- Note that if you manually adjust a row's height, Excel's AutoFit (as you enter data) feature will not work on that row. To reset the AutoFit feature, double-click the bottom of the row heading or on the Home tab, in the Cells group, click Format, AutoFit Row Height.