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.
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:
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:
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:
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:
const int cA = 1;
excelWorksheet.Cells[7, cA] = [long string of text];
Excel.Range r = excelWorksheet.get_Range("B7", "B7");
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).