Add this forgotten alignment trick to your toolbar

Merging cells in Excel is a trap—it can disrupt your spreadsheet’s structure and make simple tasks like sorting pretty much impossible. Center Across Selection is the superior choice, and a simple macro lets you pin it to your Quick Access Toolbar (QAT).

Merged cells can cause problems

Center Across Selection is the winner

Credit: Lucas Gouveia/How-To Geek | Evgeny Atamanenko/Shutterstock

Merging cells in Excel is often one of the first things we learn in school computing classes, right alongside bolding text and changing font colors. It feels like a natural way to tidy up a header, but in reality, it’s just a cosmetic fix that creates structural chaos. When you merge cells, Excel no longer treats them as separate columns, so you can’t sort data ranges or use PivotTables without first undoing the merge.

Clicking a single column selects the entire merged block, making it impossible to apply specific formatting or formulas to just one part of your data. What’s more, merged cells can lead to unexpected null values in tools like Power Query, because only the top-left cell retains the data. It’s one of the reasons Excel won’t allow you to convert a dataset containing merged cells into a table until those merged cells are removed.

Center Across Selection gives you the same visual result as a merge without actually fusing the cells together. Each column remains independent and selectable, meaning your formulas and filters keep working perfectly.

However, there’s a catch. Center Across Selection is buried deep within Excel’s Alignment settings (Ctrl+1 > Alignment > Horizontal > Center Across Selection). Because it isn’t exposed as a standalone command on the ribbon, you can’t add it directly to the QAT. To get one-click access, you have to use a tiny bit of VBA code stored in your global settings.

Related

I use Excel all day—the simple Ctrl+1 shortcut does 80% of the work

It gives me access to hidden features the ribbon can’t touch.

Step 1: Create your personal macro workbook

A permanent home for your custom tools

The word “macro” can often trigger anxiety for the average Excel user. We’ve all been conditioned to treat macro-enabled files with suspicion—and for good reason, given how they can be used to hide malicious scripts. However, in this context, you aren’t dealing with a sketchy file from the internet. You’re simply creating a private, local toolbox that lives only on your computer.

Think of the Personal Macro Workbook as your spreadsheet’s utility belt. It’s a hidden file that opens in the background every time you launch Excel. Because the code lives here and not in your active workbook, you can use your new button on any standard XLSX file without saving the file itself as a macro-enabled workbook. When you send that file to someone else, the text stays centered for them even if they don’t have your macro installed, since the formatting is a native Excel property; your macro just provides a faster way for you to trigger it.

Here’s what you need to do:

1. Open any Excel workbook and open the View tab on the ribbon.
2. Click Macros and select Record Macro.
3. In the dialog box, make sure Personal Macro Workbook is selected from the Store macro in drop-down menu, and click OK.
4. Immediately click the Stop Recording icon in the Status Bar at the bottom of your Excel window. If your Status Bar is hidden, go to Macros > Stop Recording on the View tab.

Step 2: Insert the custom Center Across Selection code

A tiny script for a massive workflow boost

Now that your Personal Macro Workbook is initialized, you can drop in the code that does the heavy lifting:

1. Press Alt+F11 to open the VBA Editor, and in the Project window on the left, find VBAProject (PERSONAL.XLSB). See the note below if you can’t see it.
2. Right-click the folder, select Insert, and click Module.
3. Paste the following code into the window. It tells Excel to take whatever range you have selected and apply the Center Across Selection alignment property instantly.

Sub CenterAcrossSelection()
    With Selection
        .HorizontalAlignment = xlCenterAcrossSelection
    End With
End Sub

If the Project window is empty or doesn’t show PERSONAL.XLSB, switch back to your main Excel window, go to the View tab, and click Unhide. Then, select PERSONAL.XLSB from the list, click OK, and head back to the VBA Editor, where it will now be visible.

Now press Ctrl+S to save, then close the VBA window.

Step 3: Add the button to the QAT

Putting your new tool within reach

With the code now in place, you can move to the QAT settings. This puts the command right at the top of your Excel window for easy access:

1. Right-click any tab on the ribbon. If you see Show Quick Access Toolbar, click it. If not, it’s already enabled, so you can move on.
2. Right-click any existing icon on your QAT at the very top of your Excel window, and select Customize Quick Access Toolbar.
3. Under Choose commands from, select Macros.
4. Find PERSONAL.XLSB!CenterAcrossSelection, then click Add to move it to the right-hand list.
5. Click Modify to choose a better icon, and change the display name to Center Across Selection or CAS. This is what you’ll see when you hover your cursor over the icon on your QAT.
6. When you click OK, you’ll see your selected icon sitting in the QAT.

Centering text without breaking the grid

Now that the hard work is done, you can test out your new button:

1. Type some text into cell A1 of a blank Excel workbook.
2. Select cells A1:E1.
3. Click your new Center Across Selection icon in the QAT, and watch your text jump to the middle of the selected range.

Always enter your text into the leftmost cell of the selected range before applying Center Across Selection. If you type in cell C1 and try to center it across A1:E1, Excel won’t apply the Center Across Selection formatting.

You’ll notice that you can still select cells A1, B1, C1, D1, and E1 independently. This means your cell structures (and, thus, your columns) are still perfectly intact, so you can sort, filter, and select data without Excel ever complaining about merged cells again.

To remove the Center Across Selection alignment, select the leftmost cell and click one of the Horizontal Alignment icons (Left, Center, or Right) in the Alignment group of the Home tab. Pressing Ctrl+Z won’t work, as VBA-driven commands don’t integrate with Excel’s standard undo stack unless programmed to support it.

Avoiding that cursed Merge & Center button is the first step toward a spreadsheet that actually works. Now that your custom toolbar button makes professional alignment a one-click affair, you can focus on the bigger picture. Keeping your columns consistent and your rows clean ensures you’re improving your Excel data structure for every project.

OS

Windows, macOS, iPhone, iPad, Android

Free trial

1 month

Microsoft 365 includes access to Office apps like Word, Excel, and PowerPoint on up to five devices, 1 TB of OneDrive storage, and more.