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.
But, when there are an even number of related columns, there is no middle column. The screenshot below illustrates this condition.
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.
This will center the January label across the four related cells without merging them, as shown below.
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.
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.
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.
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.
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.
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
- Sample workbook with macro: CenterAcrossSelection
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.
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
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. 🙂
Ha, but I COULD put the shortcut reminder in the title for the QAT… so yep, perfect!
Why I can’t find corresponding options under Vertical?
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!!
Welcome Bill 🙂
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?
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
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.
Can this be done in Mac Excel 2011, which doesn’t have a QAT for it’s ribbon?
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!!
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.
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
Awesome! thankks
This worked beautifully in Excel 365. I nearly always use “center across selection” instead of “merge” and this makes it so much simpler. THANKS!