How to Merge Cells, Columns & Rows in Excel?

Whenever we create any data in an excel sheet and we want to provide it with a title which must be centred over the entire range of the data, we have to merge cells of that particular section. The Merge & Centre option of Excel merges all the cells of the selected range and only the text in the upper left-most box is preserved and is placed at the centre of the merged cells.

In this section, we will discuss Merge & Centre, Merge Across, Unmerge Cells, Merge Columns and Rows.

Steps to Merge Cells, Columns & Rows in Excel 2016:

Step 1: Select the range of cells that you want to merge. Like, in the image below you can see that I have created information regarding sales done by the staff. And I want to give it a title as Sale Information.

So, I have written Sales Information in Cell A1 and I want it in the centre & above the entire information. For that, I have to merge cells A1 to D1. So, I have selected the range A1:D1.

Step 2: After selecting the range, go to the Home tab and click on Merge & Centre’s dropdown arrow in Alignment group.

Step 3: Click on Merge & Centre from the displayed options.

The effect of Merge & Centre would be like you can see in the image below. Cells A1, B1, C1 & D1 are merged to form one single large cell.

But, this is the case when only one cell has data content and other cells among the selected range are empty.

Let us see the effects of Merge & Centre when most of the cells in the selected range has some text. In the image below I have selected the range A4:D4 and all the cells in the range have some text. Again click on the Merge & Centre.

This time it displays a message that “Merging cells only keeps the upper-left value and discards other values”. If you agree with the condition, click OK.

In the image below you will see the effect of Merge & Centre if the selected range has text in most of the cells. You can observe that in the selected range we have four cells, earlier with the data Stieve in A4, 264 in B4, 220 in C4, 30 in D4. After Merge & Centre, the cells A1, B1, C1 & D1 are merged &  only Stieve text of A1 is preserved and is placed at the centre.

Unmerge Cells

Step 1: Select the range of cells that you have merged and click the dropdown arrow of Merge & Centre of Alignment group inHome tab. Among the displayed option click on the Unmerged Cells.

Step 2: In the image below you can see the effect of Unmerge Cells. Your earlier data will not be recovered when you unmerge the cells. Only the merged cells get unmerged.

Merge Columns

Step 1: Select the number of columns from the column header that you want to merge. Go to the Home tab and in the Alignment group click on Merge & Centre.

Step 2: In the image below you can see the effect of Merge & Centre on the selected columns.

Merge Rows

Step 1: Selected the number of rows that you want to merge and go to the Home tab and click on Merge & Centre of Alignment group.

Step 2: See the effect of Merge & Centre on the selected range of rows.

The image below shows the effect of Merge & Centre on the selected range of cells. You can observe the effect of Merge Across is different from Merge & Centre.

Merge Across merges the cells in the same row into one large cell, it merges all the columns in each row, it doesn’t merge rows in the selected range.

So, this was all about how Merge & Centre option works in Excel.