Center Across Selection QAT

Excel’s Merge and Center command has an icon, but, it is often better to Center Across Selection instead of merging cells. Unfortunately, the current version of Excel doesn’t have a Center Across Selection Ribbon icon. In this post, we’ll create a simple macro that we can activate with a Quick Access Toolbar (QAT) icon.

Objective

Let’s be clear about our objective. We want to center a cell value across multiple related columns. In worksheets that have an odd number of related columns, we can simply enter the text into the middle column and center it. This is illustrated with the January label in the screenshot below.

20150122a

But, when there are an even number of related columns, there is no middle column. The screenshot below illustrates this condition.

20150122c

 

While it may be tempting to select the January cell and the three cells to the right and then click the Merge and Center icon, it is preferred to avoid merging cells whenever possible due to the restrictions they may impose on the worksheet, such as column selections, sorting, and formatting.

A preferred approach is to select all four cells and then use the Horizontal alignment option Center Across Selection in the Format Cells dialog box, illustrated below.

20150122d

This will center the January label across the four related cells without merging them, as shown below.

20150122e

If you only need to center across selection occasionally, then using the Format Cells dialog is works just fine. If however you need to center across selection frequently, then, it may be faster to set up a macro and related icon in the QAT. We’ll cover the mechanics of setting this up below.

Macro

The first step is getting the macro set up. Fortunately, this is pretty easy to do. Probably the easiest way is by using the macro recorder. We’ll break the process down into the following steps:

  • Select a cell
  • Start recorder
  • Center across selection
  • Stop recorder

Let’s get to it.

Select a cell

The first step is to select a single cell that is not currently set with Center Across Selection. If it is, select a different cell…and confirm that it is not currently set to Center Across Selection. It is critical that the active cell is not currently formatted with the Center Across Selection option. The selected cell shouldn’t be the one you actually want to center…at this point, we are just training Excel to execute the Center Across Selection command. So, just select a cell in any worksheet.

Start recorder

Next, start recording the macro by selecting the following icon:

  • View > Macros > Record Macro

In the Record Macro dialog, enter a macro name without spaces, such as CenterAcrossSelection, and then opt to store the macro in the Personal Macro Workbook, as shown below.

20150122f

Storing the macro in the Personal Macro Workbook ensures you can run it on any worksheet anytime you have Excel open.

Click OK and then do NOT click any cell, or change the active cell or selection in any way. If you have moved the active cell or changed your selection already, stop the recorder and start again. It is critical that once you click the Record Macro dialog’s OK button that you do not change the active cell location or click the worksheet.

Center across selection

Next, open the Format Cells dialog. There are many ways to do this, one way is by clicking the following Ribbon icon:

  • Home > Format > Format Cells

In the Format Cells dialog box, navigate to the Alignment tab, and then select Center Across Selection from the Horizontal text alignment drop-down, as shown below.

20150122d

Then click the OK button and do NOT click a cell, or change the active cell in any way. Don’t click anywhere in the worksheet.

Stop recorder

Now, stop the macro recorder. There are many ways to do this, one way is by clicking the following Ribbon icon:

  • View > Macros > Stop Recording

Now, we are almost done. The macro should be recorded, and assuming the active cell wasn’t originally formatted with Center Across Selection, and that you didn’t change the active cell location while the macro was recording, you should be good.

To make the macro easy to run, we’ll set up an icon in the QAT.

QAT

Select the little drop-down on the right side of the QAT and select More Commands, as shown below.

20150122g

In the resulting Excel Options dialog, select Macros from the Choose commands from drop-down, select your new macro, CenterAcrossSelection and click the Add button. This will move the macro to the right side, as shown below.

20150122h

You can click the Modify button if desired to customize the icon.

Now, it is a good idea to close Excel, not just the workbook, but Excel…because Excel should ask you if you want to save changes to the Personal Macro Workbook as shown below.

20150122i

You need to save the changes, otherwise, your nifty new macro will be gone and you’ll have to re-record it.

Now, you can run the macro anytime by clicking the new QAT icon or by using the related Alt keyboard shortcut. You would select the cells first, and then run the macro to apply the Center Across Selection text alignment without opening the Format Cells dialog.

I’d love to hear your thoughts, or, if you have an alternative approach, post a comment below…thanks!

Additional Resources

Posted in ,

Jeff Lenning

I love sharing the things I've learned about Excel, and I built Excel University to help me do that. My motto is: Learn Excel. Work Faster.

Excel is not what it used to be.

You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.

Want to learn Excel?

Our training programs start at $29 and will help you learn Excel quickly.

15 Comments

  1. Amir on April 19, 2015 at 1:49 am

    I have to say I wasn’t aware of the Center Across Selection option in the formatting cells… many thanks for this great tip!
    As they say, each day you learn something new :))
    Thanks,
    Amir

  2. piecevcake on February 23, 2016 at 9:20 pm

    Thank you. Your instructions worked perfectly in excel 2007. I added in, no internal vertical lines. I assigned a shortcut key shift+ctrl+J.
    I wish I had included the key shortcut in the macro title. Now I have to find and unhide personal.xlsb before I can change it.
    I have put the create/stop macro button on my status bar at the bottom of the excel window.
    I read elsewhere that running macros destroys the undo memory. This macro didn’t: I could undo and redo the actions of the macro and previous actions. 🙂

  3. piecevcake on February 23, 2016 at 9:38 pm

    Ha, but I COULD put the shortcut reminder in the title for the QAT… so yep, perfect!

  4. yushiu lin on August 24, 2017 at 3:26 pm

    Why I can’t find corresponding options under Vertical?

  5. Bill on March 20, 2018 at 3:49 pm

    I have not liked the Merge and Center command for a number of years and have had to disable it in many spreadsheets over the years to Center Across Selections. This macro is very easy to create and makes it easier to use a command I use almost every day. Thanks!!

    • Jeff Lenning on March 21, 2018 at 4:47 pm

      Welcome Bill 🙂

  6. Jordan on September 26, 2018 at 9:48 am

    Will using this macro prompt everyone I send a workbook to with a warning that the workbook may be unsafe due to the use of a macro?

    • Jeff Lenning on September 26, 2018 at 12:23 pm

      It will not prompt others … here is how that works. Macros are stored in a workbook, but, can operate on other workbooks. For example, if you store your macro in Workbook A, then, it can perform operations on other workbooks, like, Workbook B. But, the macro itself is not copied to Workbook B, so, a user can open Workbook B without macro security warnings.
      Thanks
      Jeff

  7. Jim Guertin on October 24, 2018 at 9:50 am

    Hi Jeff
    I am running Excel 2010 and it has most of what you teach in it so I am happy with it. One thing I would like to be able to do is something I was able to do in an earlier Excel version. Before I was able to actually make up a name for a macro or function and place that on the QAT (or whatever it was called before). For example I had the insert filename as a function on my toolbar and labeled it “filename” and thus I could see the name and click on it. Excel no longer (as far as I can determine) allows you to replace a toolbar icon with a name.

    Do you know a way to do that? Thanks.

  8. Richard on July 27, 2019 at 7:15 pm

    Can this be done in Mac Excel 2011, which doesn’t have a QAT for it’s ribbon?

  9. Holly on October 24, 2019 at 9:13 am

    Brilliant! Is it possible to do the same for rows? I frequently have worksheets that have a one-to-many relationship (ex: one row in Col A relates to many rows in Col B). If I merge the rows in Col A and apply a filter in Col B, whatever is in Col A is no longer visible. Is there a fix for this? Thanks!!

  10. Andy Stow on April 23, 2020 at 8:26 am

    Nice, thanks! I’ve been merging so many cells until now!

    One thing, Excel doesn’t know how to undo work done by macros, so once you’ve used this you’ll only be able to Ctrl-Z up to the last edit after you ran this.

  11. Marcus Murphy on August 5, 2022 at 4:55 am

    I generally tend to avoid using Macros where possible. However this is a good one to have. However this Macro Recording is going to include code for all the formats for the cell which is not needed, therefore it is better to strip the Macro down by going into the VBA editor and reducing the code to simply:

    Sub Center_Across_Selection()

    With Selection
    .HorizontalAlignment = xlCenterAcrossSelection
    End With

    End Sub

  12. SK on December 5, 2022 at 2:55 am

    Awesome! thankks

  13. Tim Rhyne on April 13, 2023 at 10:39 am

    This worked beautifully in Excel 365. I nearly always use “center across selection” instead of “merge” and this makes it so much simpler. THANKS!

Leave a Comment