03 Best Methods: How To Merge Cells in Excel?


03 Best Methods_ How To Merge Cells in Excel

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

OPTIONS IN MERGE AND CENTER EXCEL

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

Table: Merge Options

OptionExcel ShortcutResults
Merge and CenterAlt+H+M+C (sequentially press Alt, H, M, C)Merge the selected cells and center the text across the merged cells.
Merge AcrossAlt+H+M+A (press sequentially Alt, H, M, A)

Merges the selected cells, but keeps the text left-aligned or values right-aligned.

Merge CellsAlt+H+M+M (press sequentially Alt, H, M, M)

Merges a range of cells on multiple rows as well as in multiple columns.

Unmerge CellsAlt+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 theMerge & 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.

Select Merge & Center Excel Using the Ribbon_1

Alternatively, go to the ‘Home‘ tab ➪ Alignment section ➪ click on theMerge & Centre‘ drop-down ➪ select the Merge & Center option from the list.

Select Merge & Center Excel Using the Ribbon_2

➢ 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.

Select Merge & Center Excel Using the Format Cells dialog box

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.

HOW TO UNMERGE CELLS IN EXCEL

➢ Method-1: Unmerge Cell in Excel Using the Ribbon

Select the merged cells ➪ then go to the ‘Home‘ tab ➪ Alignment section ➪ click on theMerge & 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.

Unmerge Cell in Excel Using the Format Cells dialog box

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

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 the 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, the 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

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.

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. 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 theFind' tab under the Find and Replace dialog box.

How to Find Merge Cells in Excel using the ribbon

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.

How 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.

How 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 ➪ Finally, click OK, or press Enter.

How to Find Merge Cells in Excel-4

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:

How 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 found that the button is highlighted which indicates that there is at least one merged cell in the selected range.

How to Find Merge Cells in Excel-6
If you would like to improve your academic and professional career as well, then the below courses help you a lot. Instead of Advanced Excel, a number of best courses suggested you through this platform that boosts your confidence and flies your career high.
AEF_192by192
Advance Excel Forum

Premium Courses on ed2go

03 Best Methods: How To Merge Cells in Excel?03 Best Methods: How To Merge Cells in Excel?

Advanced Microsoft Excel 2019/Office 365

Bestseller
4.9/5
03 Best Methods: How To Merge Cells in Excel?03 Best Methods: How To Merge Cells in Excel?

Microsoft Excel 2019 Certification Training (Voucher Included)

Bestseller
4.9/5
03 Best Methods: How To Merge Cells in Excel?03 Best Methods: How To Merge Cells in Excel?

Accounts Payable Specialist Certification with Microsoft Excel 2019 (Voucher Included)

Bestseller
4.9/5
03 Best Methods: How To Merge Cells in Excel?03 Best Methods: How To Merge Cells in Excel?

Certified Internal Auditor with Microsoft Excel 2019

 

Bestseller
4.9/5
03 Best Methods: How To Merge Cells in Excel?03 Best Methods: How To Merge Cells in Excel?

Executive Assistant with Microsoft Office Specialist 2019 (Vouchers Included)

Bestseller
4.9/5
03 Best Methods: How To Merge Cells in Excel?03 Best Methods: How To Merge Cells in Excel?

Payroll Manager

Bestseller
4.9/5
03 Best Methods: How To Merge Cells in Excel?03 Best Methods: How To Merge Cells in Excel?

Microsoft Office Specialist 2019 (MOS) Certification Training (Vouchers Included)

Bestseller
4.9/5
03 Best Methods: How To Merge Cells in Excel?03 Best Methods: How To Merge Cells in Excel?

Microsoft Office Specialist 2013

Bestseller
4.9/5
03 Best Methods: How To Merge Cells in Excel?03 Best Methods: How To Merge Cells in Excel?

Certified Administrative Professional with Microsoft Office Specialist 2019 (Vouchers Included)

Bestseller
4.9/5

Premium Courses on FutureLearn Limited

Premium Courses on Coursera

03 Best Methods: How To Merge Cells in Excel?03 Best Methods: How To Merge Cells in Excel?

Excel Skills for Business

Bestseller
4.9/5
03 Best Methods: How To Merge Cells in Excel?03 Best Methods: How To Merge Cells in Excel?

Excel Skills for Business: Intermediate I

Bestseller
4.9/5
03 Best Methods: How To Merge Cells in Excel?03 Best Methods: How To Merge Cells in Excel?

Excel Skills for Business: Intermediate II

Bestseller
4.9/5
03 Best Methods: How To Merge Cells in Excel?03 Best Methods: How To Merge Cells in Excel?

Artificial Intelligence Data Fairness and Bias

Bestseller
4.9/5
03 Best Methods: How To Merge Cells in Excel?03 Best Methods: How To Merge Cells in Excel?

Data Visualization & Dashboarding with R

Bestseller
4.9/5
03 Best Methods: How To Merge Cells in Excel?03 Best Methods: How To Merge Cells in Excel?

Managing, Describing, and Analyzing Data

Bestseller
4.9/5
03 Best Methods: How To Merge Cells in Excel?03 Best Methods: How To Merge Cells in Excel?

Understanding and Visualizing Data with Python

Bestseller
4.9/5
03 Best Methods: How To Merge Cells in Excel?03 Best Methods: How To Merge Cells in Excel?

Advanced Data Science with IBM

Bestseller
4.9/5
03 Best Methods: How To Merge Cells in Excel?03 Best Methods: How To Merge Cells in Excel?

Predictive Analytics and Data Mining

Bestseller
4.9/5

Premium Courses on Udemy

03 Best Methods: How To Merge Cells in Excel?03 Best Methods: How To Merge Cells in Excel?

Excel Essentials for the Real World (Complete Excel Course)

Top Rated
4.7/5
03 Best Methods: How To Merge Cells in Excel?03 Best Methods: How To Merge Cells in Excel?

Master Excel Power Query: Beginner to Advanced (including M)

Bestseller
4.8/5
03 Best Methods: How To Merge Cells in Excel?03 Best Methods: How To Merge Cells in Excel?

Advanced Excel: Top Excel Tips & Formulas

Top Rated
4.7/5
03 Best Methods: How To Merge Cells in Excel?03 Best Methods: How To Merge Cells in Excel?

Learn python

Top Rated
4.7/5
03 Best Methods: How To Merge Cells in Excel?03 Best Methods: How To Merge Cells in Excel?

Google Professional Cloud Data Engineer

Bestseller
4.6/5
03 Best Methods: How To Merge Cells in Excel?03 Best Methods: How To Merge Cells in Excel?

Data Analysis with Machine Learning & Data Visualization

Top Rated
5/5
Read-More_4

Thanks for your interest joining to Advance Excel Forum community.

Something went wrong.

Join Our Community List

Subscribe to our mailing list and get interesting stuff and updates to your email inbox.


Like it? Share with your friends!

Share via

Join Our Community List

Community grow with You. * VERIFY & CONFIRM YOUR EMAIL *

Thanks for your interest joining to Advance Excel Forum community.

Fill the Correct Information.

Thanks for your interest joining to Advance Excel Forum community.

Something went wrong.

Join Our Community List

Subscribe to our mailing list and get interesting stuff and updates to your email inbox.

We use cookies to ensure that we give you the best experience on our website (by analyzing).  Please see our cookies page for further details or agree by clicking the 'Accept' button.

 

Cookie settings

Below you can choose which kind of cookies you allow on this website. Click on the "Save cookie settings" button to apply your choice.

FunctionalOur website uses functional cookies. These cookies are necessary to let our website work.

AnalyticalOur website uses analytical cookies to make it possible to analyze our website and optimize for the purpose of a.o. the usability.

Social mediaOur website places social media cookies to show you 3rd party content like YouTube and FaceBook. These cookies may track your personal data.

AdvertisingOur website places advertising cookies to show you 3rd party advertisements based on your interests. These cookies may track your personal data.

OtherOur website places 3rd party cookies from other 3rd party services which aren't Analytical, Social media or Advertising.