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
“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
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.
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
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.
(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.
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.
(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.
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.
(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.
(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.
(06) If cells are merged into the main database, it is difficult to select a single column range.
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.
(07) If we try to paste a larger database over a smaller database, Excel easily overlaps the smaller database.
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.
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.
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.
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.
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.
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:
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.
- How to Protect Excel Formula, Cells, Worksheet & Workbook - June 8, 2020
- 08 Best Examples of Excel Conditional Formatting - June 2, 2020
- 04 Best Ways: How to Transpose Data in Excel - May 23, 2020
Advance Excel Forum
Option 1: Method to Merge & Unmerge Cells