How To Add A Center Across Selection Button To Excel's Home Ribbon Tab

Excel Center Across Selection Ribbon Button

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:

Excel Center Across Selection Ribbon Button Range Alignment Group

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 Sub

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.

 
     Already Subscribed? Click HERE to log-in to the "Example Files" section

     Already Subscribed? Click HERE to log-in to the "Example Files" section

 

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.