What is Merge Cells | Best 02 Methods to Merge columns in Excel

What is Merge Cells | Best 02 Methods to Merge columns in Excel

By

Merge Cells option is a useful tool in Excel that combines the row and column space of multiple cells into one cell. Simply we can say combine two cells into a single cell or merge columns in Excel into a single column.

Merge cells never combine the contents of cells. Rather, it combines a group of cells into a single cell that occupies the same space.

We can merge any number of cells occupying any number of rows and columns. Similarly, in the same way, we can unmerge them also.

It is typically used for row and column headings in tables of data, where we would like one title to apply to many cells.  

The range that intends to merge should be empty, except for the upper-left cell. If any of the other cells that intend to merge are not empty, Excel displays a warning. If we continue, all the data (except in the upper-left cell) will be deleted. To avoid deleting data, click Cancel in response to the warning.

To merge cells, select the cells that we want to merge and then click the Merge & Center” button. The cells will be merged, and the content in the upper-left cells will be centered horizontally. The Merge & Center button acts as a toggle.

Similarly, in the case of unmerging cells, select the merged cells and click the Merge & Center button again.

 


I. Options of Merge Cells in Excel

 

Options of Merge Cells in Excel

 

“Merge & Center” contains a drop-down list with below four options:

1. Merge and Center: Merge the selected cells and center the text across the merged cells. Use Excel shortcut Alt+H+M+C sequentially.

2. Merge Across: When a multi-row range is selected, this command creates multiple merged cells — one for each row. Use Excel shortcut Alt+H+M+A sequentially.

3. Merge Cells: Merges the selected cells without applying the Center attribute. Use Excel shortcut Alt+H+M+M sequentially.

4. Unmerge Cells: Unmerges the selected cells. Use Excel shortcut Alt+H+M+U sequentially.

 


II. Method to Merge Cells in Excel

It is a very important method of how to merge cells or combine columns in Excel and vice versa. 

Using the Ribbon ⇒

To merge cells, select the cells that we want to merge ➪ go to the ‘Home‘ tab ➪ Alignment section ➪ click on the ‘Merge & Centre‘ control button ➪ the cells will be merged, and the content in the upper-left cells will be centered horizontally. The Merge & Center button acts as a toggle.

 

Alternatively, using Excel Shortcut ⇒

To merge cells, select the cells to want to merge ➪ press sequentially Alt+H+M+C to apply the Merge & Center.

 


III. Method to Unmerge Cells in Excel

Option 1: Method to Merge & Unmerge Cells
Option 1: Method to Merge & Unmerge Cells

 

Using the Ribbon ⇒

Similarly, to unmerge cells, select the merged cells go to the ‘Home‘ tab Alignment section click on the ‘Merge & Center‘ control button again. 

 

Alternatively, using Excel Shortcut ⇒

In the case of unmerging cells, select the merged cells to press sequentially Alt+H+M+C again.

These methods will split all merged cells, but only the upper-left cells will be filled with data. Rest cells are kept blank.

 
Option 2: Method to Merge & Unmerge Cells
Option 2: Method to Merge & Unmerge Cells

IV. Limitations of Merge Cells in Excel

In Excel, generally, we apply to merge cells when the same values of multiple cells to be merged into one, or we may need to present the data differently.

Now the question arises in mind, why do we find the merged cells in our worksheet or database?

As because of Merge cells create big limitations in Excel and we should know about those step by step:

 

(01) Merge cells generally form either linear shape or a rectangular shape.

For example, we can merge cells in linear shape like A1, A2,….A5 or A1, B1, C1,….E1

 

Linear shaped Merge Cells
Linear shaped merge cells

Rectangular shaped Merge Cells
Rectangular shaped Merge Cells

 

We can merge cells in rectangular shape likes, A1, A2, A3 and B1, B2, B3; but we cannot merge just cells A1, A2, A3 and B1, B2. In this case, only first linear cells are merged, i.e. A1 to A3 but not B1 & B2.

 

Merge Cells Limitations-1
Merge Cells Limitations-2

 

(02) In Excel, merge cells only merge the two or more cells, but can’t merge the contents.

If only one of the original cells contains data, the merged cell will keep this data.

 

Merge Cells Limitations-3

 

However, if more than one of the original cells contains data, the merged cell will only keep the data from one of the original cells (generally the left upper cell in the range). Additionally, a warning message appears about this, before Excel completes the merge.

 

Merge Cells Limitations-4

 

(03) Once cells have been merged in a table, the Excel ‘Sort‘ command will not work and gives a warning message ‘To do this, all the merged cells need to be the same size. Remember that any column in a table will not be sorted either it has merged cells or not, does not matter.

Merge Cells Limitations-5

For example, in the given table, we find that the “Priority scope” column has merged cells; but if we managed to sort another column in the same table/dataset likes “Project Completion Days” will give a warning message, though it has no merged cell at all.

Merge Cells Limitations-6

 

(04) Once criteria cells are merged inside a table, any excel formula only captures the value of the left upper cells, rest merged cells considered as blanks.

For example, in the first instance, especially for merging cells, COUNTIFS functions count the scope of India 1 instead of 3.

 

Merge Cells Limitations-7

 

(05) Similarly, if the criteria cells are merged in a table heading, COUNTIFS (Conditional Count) or SUMIFS (conditional Sum) formula returns an erroneous/inappropriate result. In that case, Excel only considers the first upper left cell value in the formula.

 

Merge Cells Limitations-8

 

Merge Cells Limitations-9

 

(06) If cells are merged into the main database, it is difficult to select a single column range.

The VLOOKUP function not working due to merge cells
Figure: The VLOOKUP function not working due to merge cells

 

Excel formulas like VLOOKUP, HLOOKUP, COUNTIFS, SUMIFS, INDEX, and MATCH, etc., considered the entire range as an array range until the end of the merge cells. So in this scenario, formulas cannot work dynamically. Then we count the columns (for the col_index_num) manually for VLOOKUP.  

 

The COUNTIFS function not working due to merge cells
Figure: Similarly, The COUNTIFS function not working due to merge cells
 

(07) If we try to paste a larger database over a smaller database, Excel easily overlaps the smaller database.

 

Merge Cells Limitations-12

 

But if we try to paste a smaller database (having merged cells) into a larger database (also having merged cells), Excel will not allow pasting over it.

 

Merge Cells Limitations-13

 


V. Method to Find Merge Cells in Excel

 

So the next question arises in our mind, how do we find merged cells in our worksheet? As merging cells relate to alignment, and alignment is part of the formatting, thus Excel can find the merge cells by format.

 

Step 01: Open the Find dialog box

First, we open the Find dialog box using the two methods:

Using the Ribbon ⇒

Go to the Home tab ➪ in the Editing section, click on ‘Find & Select‘ ➪ click on Find to open the Find tab under ‘Find and Replace‘ dialog box.

 

Method to Find Merge Cells in Excel using the ribbon
Figure: Method to Find Merge Cells in Excel using the ribbon

 

Alternatively, Using the Excel Shortcut ⇒

Place cursor anywhere in the worksheet. Use the excel shortcut Ctrl+F to open the Find tab under ‘Find and Replace‘ dialog box.

Method to Find Merge Cells in Excel using the Excel shortcut

Figure: Method to Find Merge Cells in Excel using the Excel shortcut

Step 02: Switch to the ‘Alignment’ tab

In the Find dialog box, click on the ‘Options button ➪ then click on the ‘Format… box  ➪ switch to the ‘Alignment tab.

 

Method to Find Merge Cells in Excel-3

 

Step 03: Check the ‘Merge cells’ checkbox

In the Alignment tab, select the Merge cells checkbox under the Text control section ➪ click OK or press Enter.

 

Method to Find Merge Cells in Excel-4

 

Step 04: Return to the ‘Find and Replace’ dialog box

We return to the Find tab under ‘Find and Replace‘ dialog box again and click either on the:

  • Find Next to get to the next merged cell.
  • Find All to get a list of all merged cells.

When we click on one of the list items, Excel will trace the corresponding merged cell in the worksheet:

 

Method to Find Merge Cells in Excel-5

 

Note: If there are any merged cells in a specific range (either in a column or a row range), select that range and cast a glance at the ‘Merge & Center button. If we find that the button is highlighted, which means there is at least one merged cell in the selected range.

 

Method to Find Merge Cells in Excel-6

Read-More_4

 

Follow AEF
Sudipta Mondal is a writer, author as well as founder of "Advance Excel Forum (AEF)". It is the best learning platform on Advance Excel.

According to Sudipta, "Advanced Excel is a very necessary modern tool in the advanced world that can only be achieved through a committed focus and self-analyzing skills".
Sudipta Mondal
Follow AEF
Advance Excel Forum

Option 1: Method to Merge & Unmerge Cells

Editor's Rating:
5

Leave a Comment

You may also like