What is Merge Cells in Excel? How to Merge Cells in Excel? – Both questions are valid in every Excel users' mind. In this tutorial, we explain 03 different methods to apply Merge Cells and Unmerge Cells in Excel as well.
Merge Cells option is a useful feature in Excel that combines (or merges) the selected cells row-wise or column-wise and merges them into a single cell. As a result, merge the values of two or more columns (or multiple rows) into a single column (or single row).
Simply we can say merge cells combine two cells in a single cell or merge columns in 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 IN MERGE AND CENTER EXCEL
“Merge & Center” contains a drop-down list with four options:
Table: Merge Options
|Merge and Center||Alt+H+M+C (sequentially press Alt, H, M, C)||Merge the selected cells and center the text across the merged cells.|
|Merge Across||Alt+H+M+A (press sequentially Alt, H, M, A)|
Merges the selected cells, but keeps the text left-aligned or values right-aligned.
|Merge Cells||Alt+H+M+M (press sequentially Alt, H, M, M)|
Merges a range of cells on multiple rows as well as in multiple columns.
|Unmerge Cells||Alt+H+M+U (press sequentially Alt, H, M, U)|
Separates a merged cell into multiple cells again.
II. HOW TO MERGE CELLS IN EXCEL?
There are 3 important methods of how to merge cells in Excel and similarly, how to unmerge cells in Excel.
If we want to place a title at the top of a worksheet and center it over the columns of data in the worksheet. Then we can center main titles over all columns in the worksheet, and we can center category titles over groups of related columns. Similarly, we can also merge cells on adjacent rows.
Only data in the far-left cell (or top-right cell) are merged and any other data in the merged cells are deleted. Excel merges the selected cells together into one cell, and the merged cell address is that of the original cell on the left. The data are centered within the merged cell.
How To Merge and Center Cells, Complete the following steps:
• Step 1: Enter the text in the top-left cell of the range.
• Step 2: Select the range of cells across which we want to center the label.
• Step 3: We can select Merge and Center options any of the following 03 Methods:
➢ Method-1: Select Merge & Center Excel Using the Ribbon
Go to the ‘Home‘ tab ➪ Alignment section ➪ click on the ‘Merge & Centre‘ control button ➪ the cells to be merged, and the content in the upper-left cells will be centered horizontally. The Merge & Center button acts as a toggle.
Alternatively, go to the ‘Home‘ tab ➪ Alignment section ➪ click on the ‘Merge & Centre‘ drop-down ➪ select the Merge & Center option from the list.
➢ Method-2: Select Merge & Center Excel Using the Excel Shortcut
To Merge & Cente cells, press Excel shortcut Alt+H+M+C (sequentially press Alt, H, M, C).
➢ Method-3: Select Merge & Center Excel Using the Format Cells dialog box
Press Ctrl+1 which will open the ‘Format Cells' dialog box ➪ go to the ‘Alignment' tab ➪ choose ‘Center' from the Vertical: drop-down ➪ then check the Merge cells checkbox under the Text Control section ➪ press Enter or click OK to apply the condition.
FAQ ➪ What is the excel merge cells shortcut?
III. HOW TO UNMERGE CELLS IN EXCEL?
Similarly, if we want to split a merged cell into multiple cells, then follow any of the below methods. However, after unmerging the data places in the top-left cell.
➢ Method-1: Unmerge Cell in Excel Using the Ribbon
Select the merged cells ➪ then go to the ‘Home‘ tab ➪ Alignment section ➪ click on the ‘Merge & Centre‘ control button.
➢ Method-2: Unmerge Cell in Excel Using the Excel Shortcut
In the case of unmerging cells, select the merged cells ➪ then press Excel shortcut Alt+H+M+C (sequentially press Alt, H, M, C).
➢ Method-3: Unmerge Cell in Excel Using the Format Cells dialog box
Select the merged cells ➪ Press Ctrl+1 which will open the ‘Format Cells' dialog box ➪ go to the ‘Alignment' tab ➪ deselect the Merge cells checkbox under the Text Control section ➪ press Enter or click OK to apply the condition.
All the above methods split all the 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?
Because Merge cells create big limitations in Excel and we should know about those step by step:
(01) Merge cells generally form either a 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 the 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, the 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 be worked dynamically. So we manually count the column number (for the col_index_num) 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. HOW 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:
• Method 1: Using the Ribbon
Go to the Home tab ➪ in the Editing section, click on the ‘Find & Select‘ drop-down ➪ click on the Find option which will open the ‘Find' tab under the ‘Find and Replace‘ dialog box.
• Method 2: Using the Excel Shortcut
Place cursor anywhere in the worksheet. Then press the excel shortcut Ctrl+F which will open the ‘Find‘ tab under the ‘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 ➪ Finally, click OK, or press Enter.
➢ Step 04: Return to the ‘Find and Replace' dialog box
We return to the Find tab under the ‘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 found that the button is highlighted which indicates that there is at least one merged cell in the selected range.
Premium Courses on ed2go
Premium Courses on FutureLearn Limited
Premium Courses on Coursera
Premium Courses on Udemy
Join Our Community List
Subscribe to our mailing list and get interesting stuff and updates to your email inbox.