Using centre across selection in Excel rather than merging cells

This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

Using centre across selection in Excel rather than merging cells
After 25+ years of using and training in Excel, I have nothing new to learn. Or that's what I thought, until on a recent course a delegate asked me why I was merging cells to make titles ...

Posted by Andy Brown on 12 October 2023

In this blog

The Problem: Merging Cells
The Better Solution: Center Across Selection
Making this Easier to Choose

I've been a power user of Excel - and an Excel trainer - for over 25 years, and yet I didn't know this. So it's worth reading this blog because either you'll learn something new, or you can just feel insuffferably smug!

Thanks to Mike at Clearwater for bringing this to my attention! I think I promised you a yellow star, Mike: I've ordered one from Amazon, and it should be with you soon.

The Problem: Merging Cells

So it's a familiar problem - you want to centre a title:

You want to put a title in the selected cells, and centre it across the selection.

I've always solved this by clicking on the Merge & Center tool:

Problem solved! Except it isn't really ...

This gives rises to some annoying problems, which I've always lived witth:

For example, if you select down from cell C1 as shown, you will widen your selection to include cells B1 and D1 too.

The Better Solution: Center Across Selection

Unbelievably, I never knew there is a better alternative! You can just select the 3 cells:

First select the 3 cells across which you want to centre your text.

You can then choose to centre them across your selection:

Choose this option from the Format Cells dialog box.

So now when I select down from cell C1, for example, Excel considers the banner title cells to be distinct:

So much better!

Making this Easier to Choose

Why on earth don't Microsoft make this easier to choose? There's nothing you can choose on the ribbon:

None of these will help.

Nor can you add a tool from the Quick Access toolbar - there isn't one:

There are icons for virtually everything you can do in Excel - but not centring across selection.

It seems that the only way that you can provide a short-cut to this is by writing a macro:

Sub CenterAcrossSelection()

'center current selection horizontally

Selection.HorizontalAlignment = xlCenterAcrossSelection

End Sub

However, you still then have to assign this to your Quick Access toolbar. So although I'm delighted to have discovered this new feature, I'm disappointed I have to go into the Format Cells dialog box to apply it!

Some other pages relevant to the above blog include: