Merge and Centre.
It's easy to do. Just select a range of cells and click Merge and Centre. Done.
Problem is, it can cause a lots of headaches down the track.
In this short post you'll see why using Merge and Centre is a bad idea and then discover a hidden setting that's so tucked away that hardly anybody knows it exists.
Experts have been using this obscure setting for years to eliminate all the problems that Merge and Centre creates.
How to handle duplicates in Excel
1. Six reasons why Merge & Centre is a problem
Perhaps you already use the Merge and Centre feature, found on the Home ribbon.
It merges a range of pre-selected cells and horizontally centres the text. If more than one of the cells contains text, only the text in the first cell is retained and the rest are discarded.
There are a number of problems that occur when using Merge and Centre.:
1. You cannot merge cells within a formatted Excel table.
2. When you select a cell range containing merged cells then ‘Format As Table’, the merged cells become un-merged and you cannot re-merge them.
3. You cannot sort or filter a range containing a merged cell. An error is given.
4. You cannot select just one of the merged cells.
5. Problems occur when you attempt to insert cells, columns or rows.
6. In older versions of Excel, if, say, 5 cells were merged vertically, you could not then select an individual row as every row belonging to the merged cells is selected.
How to show dates like 1st, 2nd, 3rd
2. The alternative to Merge & Centre
To get around the problems with Merge and Centre, you should use ‘Center Across Selection’ instead. Here’s how:
1. Select the cells you want to ‘merge’ (they won’t actually be merged but the end visual result is the same).
2. Press CTRL 1 (or right-click and choose Format Cells).
3. Select the Alignment tab.
4. Open the Horizontal drop-down list.
5. Choose Center Across Selection.
5. Click OK to close the dialog.
The text becomes centred but the individual cells remain intact so Excel can continue using the rows and columns that it so loves. All of the aforementioned problems disappear.
So stop using Merge and Centre and start using Center Across Selection.
How to calculate a percentage, properly
3. What next?
I hope you found plenty of value in this post. I'd love to hear your biggest takeaway in the comments below together with any questions you may have.
Have a fantastic day.