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 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.

Format Excel Charts Like a Pro!!!

Learn my best Excel chart design hacks. These will turn your ugly charts into professionally crafted graphics, leaving your boss speechless!!!

Get These Hacks Now For Free!

Keep Learning

Explore more →

Which Excel Versions Have XLOOKUP Function? [Solved]

Versions Of Microsoft Excel For XLOOKUP Function The following versions of Microsoft Excel include the XLOOKUP function: Excel 2021 or...

How To Create MSI Auto-Installers For VBA Add-ins

I’ve had quite a few IT administrators request that I provide an MSI Installer file (often referred to as a...

How To Show The Developer Ribbon Tab [With Pictures]

Steps To Add The Developer Tab In Excel For PCAdd The Developer Tab in Microsoft WordAdd The Developer Tab in...

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.