There are two similar text formatting options in Excel. The well-known Merge & Center and the hidden away Center across selection.
Both have their advantages, the choice is yours.
Merge & Center
Most Excel users know ‘Merge & Center’ because it’s atop the Merge list at Home | Alignment | Merge.
It does two things:
- Combines/merges the selected cells to make a single cell
- Sets the horizontal alignment to Center or, if you prefer Centre.
Use the option for headings that span many columns.
Merge & Center is commonly used and understood. Opening a workbook you’ve never seen before, anything formatted with Merge & Center is quite obvious.
Center Across Selection
The lesser known cousin is Format Cells | Alignment | Horizontal | Center Across Selection.
The individual cells remain in place, there’s no merging.
Look closely at the above image. There’s no vertical cell marks in ‘Merge & Center’ (because it’s a single cell instead of five). But ‘Center across Selection’ leaves the five cells (notice the vertical cell marks just before and after the text) with the text seemingly overlaying the cells.
To sing the praises of Center Across Selection, we hand over to Greg H from lovely Adelaide, South Australia:
“ For mine Center Across Selection covers all regular uses of merge cells and has none of its irritations:
– scrolling down a column with arrow and selection moving away from where you want
– column selection
– I’m a keyboard shortcut fiend and Ctrl-Space can end up selecting more columns than you want, and often have to grab the mouse to select the column letter instead
– cell reference – can get trickier to unpack a formula in a busy sheet as the reference is the top left corner of the cell, not where the text might appear visually
– copy/paste gets trickier in the destination when you’re copying ranges to a new place e.g. cells need to be same width and it only has to be out by 0.01
To some these may appear petty – however I’m an old-excel-school keyboard based speed demon.
I “inherit” a lot of “other people’s work” here in the office, and resorted to writing some simple VBA to replace merged text with Centre across selection for a selected range.
The Excel toolbar adds to this bias for merge by having Merge and Centre as a default item. I’m yet to find a “Centre across selection” button.
Please keep up the good work and I’m sure you’ll find I’m not the only one who’s not a fan of “merge”. “
