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.
ALSO READ
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.
Figure 01: The Merge & Center icon
Figure 02: Four cells merged and centred
There are a number of problems that occur when using Merge and Centre.:
1. You cannot merge cells within a formatted Excel table.
Figure 03: Merge & Center is inactive when Format As Table is used
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.
Figure 04: Merged cells become un-merged when you Format As Table
3. You cannot sort or filter a range containing a merged cell. An error is given.
Figure 05: You get an error when you attempt to sort or filter a table that contains merged cells
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.
ALSO READ
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.
Figure 06: 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.
ALSO READ
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.
About the author
Jason Morrell
Jason Morrell is a professional trainer, consultant and course creator who lives on the glorious Gold Coast in Queensland, Australia.
He helps people of all levels unleash and leverage the power contained within Microsoft Office by delivering training, troubleshooting services and taking on client projects. He loves to simplify tricky concepts and provide helpful, proven, actionable advice that can be implemented for quick results.
Purely for amusement he sometimes talks about himself in the third person.
SHARE
How can we merge two or more columns. As ‘centre across selection’ works only on merging rows.
Hi Aarti. The idea of using ‘Centre across selection’ is that no cells have to be merged, but rather the content is centred within the range of cells that have been selected.
To answer the question “How can we merge two or more columns?”, select two or more cells going across the worksheet or down the worksheet, then either (1) click the ‘Merge and Center’ button on the Home ribbon, or (2) right-click and choose ‘Format Cells’, then on the Alignment tab, check/tick the ‘Merge Cells’ checkbox. I hope that helps.
If you move a block of cells around the spreadsheet and you have used the merge and center utility, you will get a message from excel that those cells are no longer centered. This does not happen with the text alignment formatting. Finally resolved that annoyance … BIG THANKS!!
This… This just saved me! It would be great if Microsoft just re-purposed the Merge and Center button to use this instead! Never gonna happen, but it would be great it if they did!
Indeed!
Great, and what if I would like my cell to not be centered horizontically…?
If you have 10 cells and you don’t want the text centred across the 10 cells, then enter your text into the first cell, leave it left-justified and leave all the following cells empty.
A very useful tip. Is there a way to create a shortcut for this command please?
Microsoft have well and truly hidden this essential feature and have not provided an easy way to shortcut it. Even Bill Jelen (Mr Excel himself) does not have an answer.
There is no command in the commands list so you can’t add it to the Quick Access Toolbar.
You could create a quick macro, but that has other implications.
Or you can create a cell style and use the ribbon shortcuts to access it, like this:
1. Click the Cell Styles icon (Home ribbon) and choose New Cell Style.
2. Name it Center Across Selection and clear all the check boxes except Alignment.
3. Click the Format button, go to the Alignment tab and choose Center Across Selection.
4. Click OK twice to close the dialogs.
5. To use it, press Alt+H then J then up arrow then Enter.
It’s ugly and certainly not sleek, but it’s the only real option available.
(If anyone has a better solution, please share the love!)