×

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

By Chris Newman •  Updated: 05/24/16 •  3 min read
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 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.

Keep Learning

Chris Newman

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.

[FREE Training] 10 Amazing Excel Efficiency Tricks. Why Don't People Know These?!

X