Why Center Across?
I am a huge advocate for using the Excel functionality of Center Across Selection instead of Merge & Center. The main reason is it forgoes issues with copy/pasting cells that are merged. A few months ago I made a proposal on the Excel UserVoice site to squeeze in a button on the Home Tab that would provide the Center Across Selection functionality (you can read/upvote that request here).
As my request hasn't seemed to have gained much traction, I came up with this crazy idea....why don't I just do it myself! So, this weekend I spent a few hours figuring out how I could create an add-in that would hide the Alignment group on the Home tab and then re-create the Alignment group with an added Center Across button. With this little Excel Add-in you can make your Ribbon transform as follows:
The VBA Used For The Center Across Macro
Since macros cannot be undone, I created a reversible macro that toggles back and forth between centering across and a normal cell alignment format. Here is the VBA code I used that will get executed when you click the Center Across button
'PURPOSE: Center text across selection
On Error GoTo Select_Cell:
If .HorizontalAlignment = xlCenterAcrossSelection Then
.HorizontalAlignment = xlGeneral
.HorizontalAlignment = xlCenterAcrossSelection
On Error GoTo 0
MsgBox "Select a cell range in order to use this button."
Free Download For This Excel Add-in
As mentioned above, I ended up creating a very lightweight Excel add-in that will slightly modify your Home tab in the Excel Ribbon to include a Center Across button. This add-in file is only compatible with Excel 2010 or later. If you need any help installing this add-in file, you can read my "How To" article that explains how to install an add-in file in Excel.
As always, in order to download this or any example file from this website, you will need to be a subscriber of my free tips newsletter. If you click the green button below you can easily sign up and you will be emailed the password to get into the subscribers-only area of this website.
What Do You Think? Leave A Comment!
Was this a good idea or a complete waste of a rainy Saturday afternoon? Let me know your thoughts in the comments section below as I'm curious if anyone else out there was wanting this sort of functionality in their Home tab.
THANK YOU: I'd like to give a special thank you to Raymond T. who helped me figure out some of the XML coding for the Ribbon interface via LinkedIn for this little project.