Excel Merge and Center (Table of Contents)
Merge and Center in Excel
Merge and Center in Excel is used to merge multiple cells and row in single cell or row. This helps in categorizing the data in a different format. By merging any cell or row, helps in assigning one cell or row to 2 or more heading or columns so that if one value needs to come under 2 or more headings of columns, then by this we can make this work.
How to use Merge and Center in Excel?
We use Merge and Center in Excel to merge a group of cells or to center the headings or text. Merge and Center is located in Home menu tab under Alignment section. Using Merge and Center in Excel is very easy and convenient.
Let’s understand how to Merge and Center in Excel with some examples.
Merge and Center in Excel – Example #1
We have sales data for products of a month below. And we have named this table as Sales and Order, which is on the top row in font size 20, just to have pure clarity and visibility of the performed function.
Now to merge table name Sales and Order in one cell, so that it will become the heading of table, select cells which you want to be merged in with Sales and Order text as shown below.
Now go to Home, and click on Merge and Center in Alignment section.
As we click on Merge and Center, we will see the text Sales and Order is now merged in a single cell. You can later change the alignment of merged text to any position as per your need.
You can unmerge the cells as well if you want to. For that, follow the same path and click on down arrow available in the right corner of Merge and Center option. We will have more option and one of them is Unmerge Cells.
4.8 (1,353 ratings)
Click on it. It will unmerge the cell and text will come back to the same previous format.
Merge and Center in Excel – Example #2
We will see another function of Merge and Center in Excel, which is Merge Across. Unlike Merge and Center in Excel, Merge Across is used to merge multiple rows or multiple line items. Where in Merge and Center, we could only merge one cell with multiple cells.
In Merge Across, a text comes to one side of the selected cells or row.
Below, we have some numbers in different rows. Here, we will merge them together in one row.
Select all cells covered in the box. Select Merge Across from a drop-down list of Merge & Center. Once, we click on it, we will get the complete selected multiple rows merged with numbers entered from row 2 to 6 as below.
If we need to unmerge the merged cells, then select the Unmerge Cells options from the drop-down, as shown below.
Once we click on Unmerge Cells, the whole row will come back to the same condition as these were before.
Merge and Center in Excel – Example #3
We can directly merge any cell content with multiple cells. This is an easy and quick process to perform. For that, we have sample data below.
In the above screenshot, we have some numbers from 1 to 5 in sequence. Now Let’s try to merge any of the numbers with one or more than one cell. For that first select that range of those cells which we need to merge together as shown below.
Above we have selected the cell from B2 to D2 in a range. Now select Merge Cells from drop-down list of Merge & Center, as shown in below screenshot.
Once we click on it, the selected cells will get merged as one single cell. And the data present in the first cell will be the only cell data carried in other merged cells as shown in below screenshot.
Let’s see what happens when we merge the cell number B5 and B6 as selected in below screenshot.
Now select Merge Cells from a drop-down list of Merge & Center. Once we do that, we will get a warning message as shown below. Which means only the upper or first value will be kept and the rest will be discarded. Then, to proceed further, click on OK as shown below.
Once we do that, the selected cells will get merged with only one value which is the first or upper value from the range as shown below.
If I want to see the value in the previous format, then unmerge the cells by selecting Unmerge Cells option from a drop-down list of Merge & Center as shown below.
After that, all the merged cells will get separated and only upper or first value will be retained further as shown below, losing the rest of the value.
- It manages data to look good by having a single value for multiple headers.
- By changing the position of merged cells, we can adjust the data and its visibility as per requirement.
- Merge & Center can be used for rows and columns both.
- Not advised to use where any file has a huge amount of data and it is becoming bulky to handle it.
Things to Remember
- Always change the alignment of the merged cell so that it would help in adjusting and looking for correct attributes.
- Use Wrap Text, which is located above Merge & Center, so that if a cell has lengthy text then by wrapping, it will be visible in the same cell, and will be in its border.
- If all the selected cells are having data, then only first or upper cell value will be carried and merged.
This has been a guide to Merge and Center in Excel. Here we discussed How to use Merge and Center in Excel and also unmerge the cells, Pros and cons of Merge and Center in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –