This week is my week to get my beefs about Excel on the record (for better or worse for my reputation in Microsoft).
My next beef is the nagging frustration that when cells are Merged they will no longer automatically Word-Wrap, thus making it very difficult to place a comment section on a spreadsheet report that will expand or shrink vertically depending on how much text is entered. (Sorry Marcus but there I go typing shit into a spreadsheet again – me bad).
This is unfortunate because very often spreadsheets are used for standard monthend reporting. Monthend reporting inherently includes comments (or excuses) and of course these are unique to each month and can be of any length. By laying out a Worksheet with a space for comments in merged cells I am not restricted in my use of the overall column-widths of the Worksheet for displaying columnar numeric data above or below the comments, Like so:
This may seem like a petty request but it would have saved me a lot of pain several years ago when this came up in an app for a major consumer products company that considered the lack of this capability a weakness on my part (despite the fact that I created a macro that emulated the capability we needed – sort of).
The only alternative I found was to have a hidden worksheet where I could
1. copy the text of the merged cells into Cell A1
2. format that cell Word-Wrap
3. capture the width of the source merged cell columns
4. then set the target cell’s column to that width
5. capture the row height that results
6. then set the row-height of the merged cells to that height (plus a row or so).
This worked for the most part but was nothing but a PAIN and despite my clever solution I lost a lot of cred (as did Excel) over this – and through zero fault of my own – IMHO :-).
I have mentioned this to Microsoft several times over the years and nothing has been done… too bad. Maybe it’s because they don’t want Excel to become a Word Processor but maybe they should let the users decide that …. hmmmm ?
Dick
Dick Moffat's Spreadsheet and BI Blog 
If users can use Excel as a database, why not as a word processor?
I’ve seen an approach in which a column out of view is set to the width of the merged cells, and the content from the merged cells is echoed in the corresponding row of this unmerged column. This unmerged cell dictates the height of the row, which should be correct for the merged cell. Obviously this works only if a one-row range of cells is merged.
Well, it’s another reason to avoid merged cells.
“Well, it’s another reason to avoid merged cells.”
yeah but they could be SO useful ……
Dick
I know they’re a bit of a pain, but there are always text box controls. Even more to the point, you could embed either a Word or WordPad object.
I only use merged cells for drop-down (data validation) list entries which could be longer than the column width. Don’t want the drop-down arrow button chopping the current entry in half. That’s the only compelling use I’ve found for them.
Hey Harlan:
“bit of a pain” !!!! How about BIG fooking pain ! (as Mr. Smurph would say :-))
Sure there are other ways to accomplish what we’re trying to do here, but I am just saying that IF the Merged Cells worked with Word Wrap it WOULD eliminate the need for kludgy work-arounds or clumsy Text Boxes or even (God help me) embedded Word (oh joy oh rapture unforeseen) ….
I guess my main argument is that because WE know work arounds which MAY make us feel good about ourselves, there’s no reason why the basic functionality shouldn’t be better in Excel.
I get paid (hopefully) to deliver solutions, not to be smart and clever and I have to keep telling myself that – because I enjoy feeling smart and clever as much as any of us do …. If I can use the functionality of Excel right out of the box I’m a happy guy.
And frankly this functionality could be used by regular Excel users to good effect too, which would make for better Excel results everywhere – which helps us all.
Thanx Harlan.
Dick
The .HorizontalAlignment = xlHAlignCenterAcrossSelection property retains the word wrap and autofit methods for a range.
You can also set the property manually. (center across selection) in Format | Cells | Alignment (tab)
Hey Jim:
Yes you COULD always use Center Across Seection but that’s the problem – it would be “centered” 🙂 …
If you’re using it for Comments (as opposed to as a Column Heading) then you want the text Left-Justified, so Center across Selection isn’t what I’m looking for.
Thanx anyway.
Dick
This begs the question what the best tool is for the task. Excel just isn’t the best tool for free form prose, even if word wrap worked correctly with merged cells.
When it comes to comments, how many lines should be allowed for them on any given printout page? If the standard printout page shows 54 lines of actual contents between page margins, header/footer lines and space between header/footer and actual page contents, and if non-comments contents took 50 lines including space between the last row of contents and the first line of comments, what should happen when the user enters, say, 6 lines of comments?
My own experience has taught me to make comment areas fixed size in order to guard against fubarred printouts. If users need more than, say, 4 lines of comments, the spreadsheet definitely isn’t the best place for them.
Hey Harlan:
Yes you’re right about the page fitting – but I can live with that if I have to tell the user a maximum number of rows. That way they can at least find that the distance between the text and the start of the next section is consistent (which seems to matter more than blank paper at the bottom of the page for some users. If they can live with the page breaks being wonky then that’s ok too – clients have starnge tastes most of the time.
Telling the user that they shouldn’t use the spreadsheet simply because of the number of lines of text is a bit more than I’m willing to say. I’d rather they live in one application and if the principal content is data from a spreadsheet then that’s where the text should live as well. Sorry – I disagree…
In the end I still want my Merged cells to Word-Wrap I’m afraid 🙂
Dick