559 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
|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 ...|
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.
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.
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!
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:
'center current selection horizontally
Selection.HorizontalAlignment = xlCenterAcrossSelection
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:
25 Aytoun Street