Today, in Stupid Excel Tricks.
Jan. 30th, 2013 10:14 am![[personal profile]](https://www.dreamwidth.org/img/silk/identity/user.png){kind=small}
theweaselkingThere's a thing, in Excel, where sometimes you'll want to merge cells. Instant problem: As soon as you merge cells, you can't get them to auto-adjust row height when wrapping any more.
This is a known thing about Excel. OpenOffice Calc doesn't have a problem with it, but Excel has never been able to auto-adjust row height for merged cells. And the standard workaround is "give the merged cell a name, and have a macro resize it". This is, to put it bluntly, annoying.
I've found a less annoying workaround.
Step 1: Pick a column WAAAAY off to the right. Like, "AA" or something. Something off where you're not using it and never going to look at it.
Step 2: Set the width of that column to the width of the whole merged cell range - so if you merged C3-K3, set column AA to be as wide as the whole C-K distance.
Step 3: Set the content of AA3 to be "=C3" so anything you put in C3 is duplicated far off in the vast yonder.
Step 4: Turn on text wrapping in both AA3 and the C3-K3 merge. Ta-da! Text will now wrap and row heights will autoadjust in your merged cell - because they're autoadjusting the non-merged cell in the same row, but who cares?
Step 5: Set your print area to exclude your far off column of dumb.
This, of course, IS annoying and stupid, compared to simply "making row height work right for merged cells" - but MS has claimed this is a feature, not a bug, for more than a decade now, so they obviously have no intention of fixing it. And by doing this you can get the result you actually wanted, which is nice.
This is a known thing about Excel. OpenOffice Calc doesn't have a problem with it, but Excel has never been able to auto-adjust row height for merged cells. And the standard workaround is "give the merged cell a name, and have a macro resize it". This is, to put it bluntly, annoying.
I've found a less annoying workaround.
Step 1: Pick a column WAAAAY off to the right. Like, "AA" or something. Something off where you're not using it and never going to look at it.
Step 2: Set the width of that column to the width of the whole merged cell range - so if you merged C3-K3, set column AA to be as wide as the whole C-K distance.
Step 3: Set the content of AA3 to be "=C3" so anything you put in C3 is duplicated far off in the vast yonder.
Step 4: Turn on text wrapping in both AA3 and the C3-K3 merge. Ta-da! Text will now wrap and row heights will autoadjust in your merged cell - because they're autoadjusting the non-merged cell in the same row, but who cares?
Step 5: Set your print area to exclude your far off column of dumb.
This, of course, IS annoying and stupid, compared to simply "making row height work right for merged cells" - but MS has claimed this is a feature, not a bug, for more than a decade now, so they obviously have no intention of fixing it. And by doing this you can get the result you actually wanted, which is nice.