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. If you have moved the active cell or changed your selection already, stop the recorder and start again. Storing the macro in the Personal Macro Workbook ensures you can run it on any worksheet anytime you have Excel open.Ĭlick OK and then do NOT click any cell, or change the active cell or selection in any way.

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. Next, start recording the macro by selecting the following icon: 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. It is critical that the active cell is not currently formatted with the Center Across Selection option. If it is, select a different cell…and confirm that it is not currently set to Center Across Selection. The first step is to select a single cell that is not currently set with Center Across Selection. We’ll break the process down into the following steps: Probably the easiest way is by using the macro recorder. The first step is getting the macro set up. We’ll cover the mechanics of setting this up below. 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. If you only need to center across selection occasionally, then using the Format Cells dialog is works just fine. This will center the January label across the four related cells without merging them, as shown below. 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.Ī 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. The screenshot below illustrates this condition. This is illustrated with the January label in the screenshot below.īut, when there are an even number of related columns, there is no middle column. In worksheets that have an odd number of related columns, we can simply enter the text into the middle column and center it.

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

Shortcut for align left in excel mac