How To Add A Center Across Selection Button To Excel’s Home Ribbon Tab
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
Sub CenterAcrossSelection()
'PURPOSE: Center text across selection
'SOURCE: www.TheSpreadsheetGuru.com
On Error GoTo Select_Cell:
With Selection
If .HorizontalAlignment = xlCenterAcrossSelection Then
.HorizontalAlignment = xlGeneral
Else
.HorizontalAlignment = xlCenterAcrossSelection
End If
End With
On Error GoTo 0
Exit Sub
'ERROR HANDLERS
Select_Cell:
MsgBox "Select a cell range in order to use this button."
Exit Sub
End SubFree 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 compatible with Excel 2010 or later (for either PC or Mac versions of Excel). 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.
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. and Jon Peltier who helped me figure out some of the XML coding for the Ribbon interface for this little project.
Learn my best Excel chart design hacks. These will turn your ugly charts into professionally crafted graphics, leaving your boss speechless!!!
Keep Learning
Chris Newman
Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.