Merge and Centre in Excel can cause a lots of headaches down the track.
Read this short post to the end and take evasive action now. You'll thank me later.
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 use this obscure setting to eliminate all the problems that Merge and Centre creates.
1. Why Merge & Centre is a problem
Perhaps you use the Merge and Centre feature, available in Excel 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 and ‘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.
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.
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.
3. What next?
Please feel free to post any questions you have into the comments below. And before you leave this page, I want to give you one quick little action item!
ACTION ITEM: Comment below with ONE thing you picked up from this post and share how it has helped, or will help, you. It's good to share your successes because it encourages others and provides an instant boost.
Enjoy the rest of your day!