Any self-respecting Excel expert will tell you that the ‘Merge and Center’ functionality is an abomination. The only benefit I can think of is the justification you can offer when you launch your office shooting spree.
“It was that message again” (eye twitching). “Can not change that part of merged cells” . Your temporary insanity plea will go right through.
A better option is to apply the ‘Center Across Selection’ formatting option. Center Across Selection essentially gives the same visual effect as ‘Merge & Center’, but without actually merging the cells.
You can apply this formatting option manually by choosing it in the Alignment tab of the Format Cells dialog box. The idea is to select the range of cells you would normally merge, but choose Center Across Selection instead.
In a hilarious email, Al Portillo shared a macro he uses to get around the ‘Merge and Center’
Mike, I hear that you think “Merge and Center” is an abomination. I
concur. I have a “center across” macro right next to the Merge icon in
order to continuously mock it. Merge knows it’s toast.
He sent me this screenshot.
He essentially assigned this macro to a button:
Sub CenterAcrossSelection()
With Selection
If .HorizontalAlignment = xlCenterAcrossSelection Then
.HorizontalAlignment = xlGeneral
Else
.HorizontalAlignment = xlCenterAcrossSelection
.VerticalAlignment = xlCenter
End If
End With
End Sub
This macro applies the Center Across Selection formatting option in the Format Cells dialog box. Again, the idea is to select the range of cells you would normally merge, and fire this macro.
If I had my druthers, I would replace the ‘Merge & Center’ icon on the Ribbon with ‘Center Across Selection’.
Come on Microsoft, at least give us a ‘Center Across Selection’ icon next to ‘Merge & Center’. That way, we can all mock ‘Merge & Center’ on a daily basis.
I can imagine hovering over ‘Merge & Center’ for a second (just to tease it), then clicking ‘Center Across Selection’ instead.
Update: Tim suggested a useful addition to the code that will allow for you to undo the Center Across Selection. Thanks Tim, I’ve updated the code.
RELATED STUFF
Thank you for this.
Didn’t know about this feature. I figure I will start using this instead of the “Merge and Center” feature. Already added this to my toolbar
Here in New Zealand – where guns are a bit harder to come by – we simply go on office chart-doctoring sprees. Not a pretty sight, I can tell you. This kinda thing isn’t uncommon:
“What? A GRAY background. Are you a total Excel 2007 Dashboard and Reports DUMMY?”
“Whaddayamean? It’s not GRAY. It’s GREY”
[Aside: How the heck did it come to be called grey (international and some parts of the U.S.), and gray in some U.S. parts only? I think I'm starting to see the problem with your foreign policy...you can't even agree with EACH OTHER]
Annnnnnyway…MrExcel had a podcast recently (April 2009) on a take of this at http://www.howcast.com/videos/173687-MrExcels-Learn-Excel-996-Center-Across-Selection
As Bill aka Mr Excel points out, one of the problems with merged cells is that if you say merge cells A1:A5 and then try to select a single row within this range, then when Excel ‘hits that merged cell, it selects all 5 rows’.
The upshot is that center across selection only works horizontally. If you want to do this vertically, then you need to use a workaround.
Bill’s workaround is aText Box snapped to the grid.Much, much easier than my solution, which involved getting drunk enough so that I had no option but to set my head rotation setting to -90 degrees. (+270 degrees on a REAL bender)
Righto, gotta get back to binge drinking. I know it’s only Thursday night (here) but surely it’s Saturday night SOMEWHERE in the world. (Where’s the beer o’clock setting in this confounded software, anyway?)
Besides,I gotta work up to your forthcoming (sorry for the pun) pig recipe – the drunker I am, the tastier it gets.
Later dude.
Jeez..I just watched one of your videos. Absolutely hypnotizing. Do you gesticulate as wildly with your hands as you do with the mouse?
Love the macro idea, but how did you get the button to look like that? A smily face just isn’t the same.
@Lisa – you can copy/paste the image from any existing button. After getting into the customization mode (Tools/Customize…), select the Merge & Center button by clicking on it (should have a black border around it). Then right click on the button and there’s a menu option to “Copy Button Image”. Now select your new “Center Across Selection” button, right click and select paste. From there, right click on your new button again and select “Edit Button Image” to change the color.
I made my “good” button green and the evil Merge & Center button red.
@jeffrey weir
You should attend one of his seminars!
Great tips plus great entertainment.
Nice tip. The macro as written will not undo the “center across” action. I suggest a slight mod to make it reversing as well.
Sub CenterAcrossSelection()
With Selection
If .HorizontalAlignment = xlCenterAcrossSelection Then
.HorizontalAlignment = xlGeneral
Else
.HorizontalAlignment = xlCenterAcrossSelection
.VerticalAlignment = xlCenter
End If
End With
End Sub
Thanks for the copy button info!
Can someone please tell me how to add a macro button the ribbon?
@Jared… you can add a macro to ribbon by,
go to excel options from office button and select “customize” tab.
change the “choose commands from” option to “macros” from “popular commands”
Now you will see all the available macros in the list. Just select the one you have created and add it to quick launch bar by moving it to the other column.
You can change the icon by selecting the macro in the quick launch bar list and clicking “modify” button…
I am sure you can tweak the ribbon xml code to include stuff there, but that would be too much customization.
Thanks Chandoo!
@Chandoo
Thank you!
OK, I added it to the Quick Bar but how do I get the icon the way it is in the screen shot? The trick described above does not work in 2007 (does it?).
David:
1. Go back to the areas where you can customize your Quick Access Toolbar.
2. Find and select your macro in the listbox on the right hand side of the dialog box.
3. Click the Modify button. This will open a choice of icons to choose from.
Thanks for making me aware of this, the first thing I did for Excel 2010 TP was to look for a button for this (couldn’t find), and then request that they add it.
Don’t think this has been covered above.
Sometimes we might need to format adjacent sets of cells on the same row to center-across-selection. Yet, following the advice above, if a set is empty, any adjacent occupied cells just gobble up the empty ones and they all just mash up and centre together. Is there some way of fencing these things off?
I am in 2007, and am stymied by the “center across selection command” – when I use it, the borders do not show properly for the end cell of the selection.
I have always hated the merge and center option, but will have to got to it if I cannot get my borders to work.
Never knew this. Thanks