Add this forgotten alignment trick to your toolbar

Merging cells in Excel is a trap: it can disrupt the structure of the spreadsheet and make simple tasks like sorting virtually impossible. Focusing on the selection is the best option and a simple macro allows you to pin it to your Quick Access Toolbar (QAT).

Fused cells can cause problems

Center Across Selection is the winner

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

Combining cells in Excel is often one of the first things we learn in school computer classes, along with bolding text and changing font colors. It seems like a natural way to tidy up a header, but it’s actually just a cosmetic solution 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 pivot tables without first undoing the merge.

Clicking on a single column selects the entire combined block, making it impossible to apply specific formats or formulas to just a portion of your data. What’s more, merged cells can generate unexpected null values ​​in tools like Energy consultationbecause only the top left cell keeps the data. It is one of the reasons why Excel will not allow you to convert a data set containing merged cells into a table until those merged cells are deleted.

Centering on the selection gives you the same visual result as a join without merging the cells. Each column remains independent and selectable, meaning your formulas and filters still work perfectly.

However, there is a problem. Focus on selection is buried deep within Excel’s Alignment settings (Ctrl+1 > Alignment > Horizontal > Center on selection). Because it is not exposed as a standalone command on the ribbon, you cannot add it directly to the QAT. To get one-click access, you need to use a small snippet 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 that the tape can’t touch.

Step 1: Create your personal macro workbook

A permanent home for your custom tools

The word “macro” can often cause anxiety in the average Excel user. We’ve all been conditioned to treat macro-enabled files with suspicion, and rightly so, since they can be used to hide malicious scripts. However, in this context it is not an incomplete Internet archive. You are simply creating a private, local toolbox that resides solely on your computer.

Think of the Personal Macro Workbook as the tool belt for your spreadsheet. It is a hidden file that opens in the background every time you start Excel. Because the code resides here and not in your active workbook, you can use your new button in any standard XLSX file without saving the file as a macro-enabled workbook. When you send that file to someone else, the text remains centered for that person even if they don’t have your macro installed, since formatting is a native property of Excel; your macro simply gives you a faster way to activate it.

This is what you should 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 book is selected from the Save macro to drop-down menu and click OK.

4. Immediately click Stop recording icon on the Status bar at the bottom of your Excel window. If your status bar is hidden, go to Macros > Stop recording in it View eyelash.

Step 2: Insert custom code Center on selection

A small script for a huge workflow boost

Now that your personal macro workbook is initialized, you can enter the code that does the heavy lifting:

1. Press Alt+F11 to open the VBA editorand in the Project window on the left, look VBA Project (PERSONAL.XLSB). Please see the note below if you can’t see it.

2. Right click on the folder, select Insertand click Module.

3. Paste the following code into the window. Tells Excel to take any range you’ve selected and apply the Center alignment property on the selection instantly.

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

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

Step 3 – Add the button to the QAT

Putting your new tool at your fingertips

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

1. Right click on any eyelash on the tape. if you see Show quick access toolbarclick on it. If not, it is already enabled, so you can continue.

2. Right click on any existing icon in your QAT at the top of the Excel window and select Customize the Quick Access Toolbar.

3. Low Choose commandsselect macros.

4. Find PERSONAL.XLSB!CenterAcrossSelectionthen click Add to move it to the list on the right.

5. Click Modify to choose a better iconand change the display name to Focus on selection either CAS. This is what you will see when you hover over the icon on your QAT.

6. when you click OKyou will see the selected icon in the QAT.

Center text without breaking the grid

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

1. Write some text in the cell A1 of a blank Excel workbook.

2. Select cells A1:E1.

3. Click on your new Focus on selection in the QAT and watch your text jump to the middle of the selected range.

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

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

SW

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, 1TB of OneDrive storage, and more.