Development

Excel – wordwrap row autosize issue

45 Comments

I came upon an Excel annoyance last week. I have hit upon it before, but this week, I needed to find a solution.

What’s the problem?
The annoyance is this: merged cells that contain a lot of text do not automatically see their row size grow to fit the amount of test.

The solution is manual: you can resize the row manually, however you cannot double click on the row dividers and let Excel increase the row size to suit the amount of text in the merged cells.

Here’s a little more background to the problem. The screenshot below demonstrates that we can have a single (non-merged) cell word wrap correctly. However it does mean that the entire width of column A is affected, thus the date is pushed to the right.

excel1

The first solution that we might try is to merge a few cells. This works, date is not pushed to the right. However, Excel’s ability to increase the row size to suit the amount of text is lost – double clicking on the row divider does not work and simply results in a single line of visible text, as shown below:

excel4

In order to alleviate this problem, we have to resize the row manually. This is a tedious process and one that does not lend itself to automation, i.e. through programmatic means. Nonetheless, here’s a screenshot of the resized row:

excel2

The solution
The real solution, and it feels like a fudge, but it works very well, is this: use merged cells as before, somewhere to the right of the cell with merged text (e.g. A1), create a copy of the cell’s content (if your text is in cell A1, make cell Z1 “=A1”). Resize the copied cell (Z1) width to match the width (and formatting) of the merged cells (A1). Excel’s row auto-size feature will now work manually and programmatically. Here a screenshot that might help:

excel3

Perhaps this is best explained by use of an example spreadsheet.

Now, the reason why I making such a big thing about this is simple: Excel is great for producing tabular reports. However, to get some formating to work “just right”, it pays to using Excel automation, i.e. work with the format styles programatically. It is for this reason that I find myself writing the following snippet of code:


[C#]
const int cA = 1;
excelWorksheet.Cells[7, cA] = [long string of text];
Excel.Range r = excelWorksheet.get_Range("B7", "B7");
r.EntireRow.AutoFit();

It is the last two lines of code that perform the same function as double clicking on the row divider, thus the row grows to the correct height (albeit of the copied cell, Z1).