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.
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.
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.
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.
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.
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!
- 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?
Access all Undergrad and Masters lessons with a Campus Pass or CPE Pass. Includes on-demand training plus live office hours.