Merge and Center in Excel allows you to quickly merge two or more cells (rows or columns). This can be useful when you want to create headings or labels that are meant for multiple cells at the same time.
Something as shown below (cells A1:A3 are merged):
In this Excel tutorial, I will show you some ways to merge and center in Excel (including a keyboard shortcut)
Merge and Center Keyboard Shortcut
Let me start with the keyboard shortcut.
Suppose you want to merge and center three cells (A1:A3).
To do this, first select these cells.
And then use the below keyboard shortcut to merge and center these three cells:
ALT + H + M + C
You need to use this keyboard shortcut with successive key presses. This means that you should hit the ALT key, then leave it and hit the H key, and so on.
When you merge cells, only the value in the top-left most cell is retained, and the rest is discarded. Thankfully, Excel first shows you a prompt as shown below before making the change. If you don’t want this to happen, you can click on the Cancel button.
Merge and Center Using the Ribbon Options
If you’re not a huge fan of keyboard shortcut (and I agree that the keyboard shortcut for merge and center is a bit long and hard to remember), you can also use the option in the Excel ribbon.
And it only two clicks away!
Suppose you want to merge the three cells (A1:A3).
Below are the steps to do this:
- Select the cells you want to merge
- Click the Home tab
- In the alignment group, click on the Merge and Center option
The above steps would instantly merge the cells and align the text in these to the center.
Again, in case you have any text in the cells already, only the text in the top-left cell would be retained and rest will be discarded.
Adding Merge and Center Button to the Quick Access Toolbar (One Click)
Another really quick way is to add the Merge and Center option in the Quick Access Toolbar (QAT).
When you do this, you can access merge and center options with a single click, as the QAT is always visible when you’re working in Excel.
Below are the steps to add Merge and Center option to the QAT:
- In the Quick Access Toolbar, click on the ‘Customize Quick Access Toolbar’ option
- Click on ‘More Commands’
- In the Excel Options dialog box, select the Merge & Center option (you may have to scroll down a bit)
- Click on the ‘Add’ button
- Click on OK.
The above steps would add the Merge & Center button in the QAT.
Now when you want to merge cells, simply select those and click on the icon in the QAT.
How to Unmerge Cells
Just like you can merge the cells, you can use the same steps to unmerge these cells as well.
Just select the cells that are merged and then you can use the shortcut, or the option in the ribbon (or QAT if you have added it).
Note that when you unmerge cells that already has some text in it, all the text goes to the top-left cell of the selection.
In case you have a lot of merged cells and you want to quickly unmerge all of these, you can use the FIND and REPLACE option to quickly find all the merged cells and then unmerge all at once.
The Problem with Merge and Center
There is a right way to do something in Excel and there is a wrong way to do it.
And in some cases, merging cells is the wrong way!
When you merge cells in Excel (as shown below), it robs you of the ability to correctly sort or filter the data.
And what’s the right way to do it?
Center Across Selection
When you use Center Across Selection, it does the same thing that Merge & Center does, without actually making the selection one single cell. This makes sure that you can continue to sort and filter the data.
Here is a tutorial where I cover this alternative of merge and center
You may also like the following Excel tutorials: