EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login

Merge Cells in Excel

By Manisha SharmaManisha Sharma

Home » Excel » Blog » Excel Tools » Merge Cells in Excel

Merge Cells in Excel

Merge Cells in Excel (Table of Contents)

  • Merge Cells Using Merge & Center
  • Best ways to Merge cells in Excel
    • Merge cells Using CONCATENATE
    • Merge Cells Using Ampersand (&) Operator
  • Merge Option
  • How to Unmerge cells in Excel?
  • The trick to Quickly Merge Multiple Cells

Merge Cells in Excel

Merge cells in excel is used for merging more than one cell. This is quite useful when we need to assign one value of the cell to 2 or more cells. To merge the cells in excel, select those cells, and from the home menu tab, select Merge & Center from the alignment section. This will combine both of those selected cells, but it will keep only the first value to be seen, and others will be eliminated.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

Merge Cells in Excel Using Merge & Center Option

Merging essentially means having the combined values of multiple cells in one. Perhaps the most used and one of the simplest ways to achieve this using the “Merge & Center” option available in the Home ribbon.

Merge and Center

There is, however, a drawback with this method of merging. If we have some data in cells A1 and B1, using this form of merging would result in the retention of the left-hand value and the overwriting of the right-hand value.

Let us look at an example to get a better understanding of this drawback.

We have a list where the first column (A) contains the first names and the second column (B) has the last names. If we wish to merge the contents of “First Name” and “Last Name”, then this method fails as it takes only the left-hand value, completely overwriting the right-hand values.

Merge Cells Example 1-1

Step 1: We will need to select the cells that we wish to merge together. In this scenario, we shall select cells A2 and B2.

Popular Course in this category
Sale
Excel Advanced Training (16 Courses, 23+ Projects)16 Online Courses | 23 Hands-on Projects | 140+ Hours | Verifiable Certificate of Completion | Lifetime Access
4.8 (11,442 ratings)
Course Price

View Course

Related Courses
Excel Training (21 Courses, 9+ Projects)

Merge Cells Example 1-2

Step 2: We will click on the “Merge & Center” option on the Home ribbon next.

Merge Cells Example 1-3

Step 3: The moment we click on “Merge & Center”, we get a dialogue box pop up showing a warning, mentioning that only the left-hand value would be considered, and the right-hand value would be discarded.

Merge Cells Example 1-4

Step 4: Click on “OK”

Merge Cells Example 1-5

Step 5: Once we click on “OK”, we shall get the following result

Merge Cells Example 1-6

So, what happened here is that Excel merged the two cells together instead of the cell values. Due to this, we did get a merged cell, but we lost the “Last Name” in the process. This is the main drawback of using the “Merge & Center” option.

Now we shall discuss the two best ways to merge cells in Excel.

Best ways to Merge cells in Excel

So you might have the question – What is the correct and most effective way to merge cells in Excel? Here’s the answer to that.

  • Concatenation formula
  • Ampersand (&) Operator

Let us see what each of these options is in detail.

Merge cells Using CONCATENATE

As we have seen by now that Excel has a vast array of formulas for various needs. The CONCATENATE formula can merge the contents of multiple cells into one cell. By using this method, we can successfully merge the “First Name” and the “Last Name” without losing any data in the process. However, the result would be available in a new cell.

Step 1: First, we shall select Cell C2.

merge cells Using Concatenation 1

Step 2: Next, we shall be applying the CONCATENATE formula as shown below.

merge cells Using Concatenation 2

On applying this formula, we shall now get the correct results:

merge cells Using Concatenation 3

Similarly, We will Get Other Results.

merge cells Using Concatenation 4

Now, if we look at the breakup of the formula:

A2 = The first cell that we wish to merge.

“ “ = The space between the First Name and the Last Name.

B2 = The last cell that we wish to merge.

We can merge multiple cells into one using this method, but it will be in a different cell.

Merge Cells Using Ampersand (&) Operator

Similar to the way in which we merged two cells using the CONCATENATE function, we shall use the ampersand (&) operator. The only difference will be that we shall use the “&” operator instead of the CONCATENATE function.

Step 1: First, we shall select the cell C2:

merge cells Using Ampersand 1

Step 2: Next, we shall apply the formula as shown below.

merge cells Using Ampersand 2

This would give us the following result:

merge cells Using Ampersand 3

Similarly, We will get another Result.

merge cells Using Ampersand 4

Merge Option

Let us discuss the different options in the “Merge” option mentioned briefly earlier.

  • Merge & Center: This option will merge the selected cells by keeping only the left-hand value and making the text alignment as the center.

Merge and Center option

  • Merger Across: “Merge Across” would merge the selected cells but would not change the alignment.

Merger Across option

  • Merge Cells: This is perhaps the simplest option – It would just merge the selected cells.

Merge Cells option

  • Unmerge Cells: “Unmerge Cells” is the exact opposite of Merge. It unmerges the cells.

Unmerge Cells option

So now that we have covered how to merge cells in Excel, perhaps it is interesting to know that we can also “Unmerge” cells!

How to Unmerge cells in Excel?

Suppose we have the same set of merged cells that we had seen earlier with the “Merge & Center” option.

You can download this Merge Cells in Excel Template here – Merge Cells in Excel Template

Unmerge Cells 1

Now to unmerge these cells, we will need to once again to the Home ribbon and navigate to the “Merge & Center” option and click on the drop-down arrow at the side, to open up the underlying options.

Unmerge Cells 2

Now, here we shall select the “Unmerge Cells” option to unmerge the selected cells.

Unmerge Cells 3

Hence the merged cells have been “unmerged” by using this option.

Unmerge Cells 4

The trick to Quickly Merge Multiple Cells in Excel

Suppose we have to merge multiple cells in Excel. Our objective here is to merge all the cells for a “Year” for all entries for a particular year.

Trick to merge many cells 1

Step 1: We shall proceed with selecting the cells A2 to A4.

Trick to merge many cells 2

Step 2: Next, we shall go to the Home ribbon and click on “Merge & Center”.

Trick to merge many cells 3

As we see in the below screenshot, the cells A2, A3 and A4 have been successfully merged.

Trick to merge many cells 4

Step 3: Now, we shall simply click on this merged cell and then click on “Format Painter” in the Home Ribbon.

Trick to merge many cells 5

This causes the selected cells to be highlighted, as shown below.

Trick to merge many cells 6

Step 4: Next, we shall select and drag cells A5 to A10.

Trick to merge many cells 7

As we can see, the merging has been carried out for the rest of the years by simply clicking on “Format Painter”. The format Painter essentially copies the same format from the source cells(s) and applies it across all the destination cells.

Things to Remember About Merge Cells in Excel

  • Sorting data over merged cells in Excel is not possible.
  • Navigating through merged cells can become difficult and cumbersome.
  • It is advisable to use the merge cells option only for headings.
  • The merge option is limited to merging cells and not the cell values.
  • If we have to merge the cell values, we will need to use either the CONCATENATE function or the Ampersand (&) Operator.
  • There is a shortcut to merge cells in Excel: Alt + H + M + U. This combination of key presses merges as well as unmerges the cells that are selected in the excel sheet.

Recommended Articles

This has been a guide to Merging Cells in Excel. Here we discuss how to Merge Cells in Excel along with practical examples and a downloadable excel template. You can also go through our other suggested articles –

  1. Excel Unmerge Cells
  2. Excel Merge and Center
  3. Excel Merge Two Tables
  4. Excel Shortcut For Merge Cells

All in One Excel VBA Bundle (120+ Courses, 30+ Projects)

120+ Online Courses

30+ Projects

500+ Hours

Verifiable Certificates

Lifetime Access

Learn More

0 Shares
Share
Tweet
Share
Primary Sidebar
Excel Functions
  • Excel Tools
    • Excel Shortcut Redo
    • Reduce Excel File Size
    • Quick Analysis in Excel
    • Goal Seek in Excel
    • Compare Two Lists in Excel
    • Excel Quick Analysis
    • Estimate Template in Excel
    • Pivot Table Count Unique
    • CSV Files into Excel
    • Excel Business Plan Template
    • Excel Export to PDF
    • Free Excel Template
    • Excel Repair
    • Color in Excel
    • Form Controls in Excel
    • Timecard Template in Excel
    • How to Unhide All Sheets in Excel?
    • Power Query in Excel
    • Power View in Excel
    • XML in Excel
    • Excel Evaluate Formula
    • Examples of Excel Macros
    • Consolidation in Excel
    • Ribbon in Excel
    • Excel Conditional Formatting for Dates
    • Protect Sheet in Excel
    • Data Model in Excel
    • Pivot Table Examples
    • Pivot Table Slicer
    • Pivot Table Filter
    • Watch Window in Excel
    • Slicer in Excel
    • Print Gridlines in Excel
    • Convert Numbers to Text in Excel
    • Freeze Columns in Excel
    • Spelling Check in Excel
    • Name Box in Excel
    • Solve Equation in Excel
    • Excel Spreadsheet Examples
    • What If Analysis in Excel
    • How to Print Labels From Excel
    • Excel Named Range
    • Excel TRANSPOSE Formula
    • Excel Merge and Center
    • Excel Freeze Rows
    • HLOOKUP Formula in Excel
    • Excel Create Database
    • Excel Gridlines
    • Excel Spreadsheet Formulas
    • Excel Sort By Number
    • Excel Concatenating Columns
    • Excel AutoCorrect
    • Conditional Formatting For Blank Cells
    • Excel Icon Sets
    • Excel CTRL D
    • Excel Accounting Number Format
    • Excel Regression Analysis
    • Excel Import Data
    • Excel Freeze Panes
    • Excel Calendar
    • Excel Developer Tab
    • Excel Enable Macros
    • Excel Autosave
    • Heat Map in Excel
    • Excel Toolbar
    • Excel Error Bar
    • Excel Status Bar
    • Excel Unprotect Sheet
    • Check mark in Excel
    • Excel Column Filter
    • Excel Header and Footer
    • Excel Drawing
    • Range in Excel
    • Timeline in Excel
    • Excel Lock Formula
    • Excel Table styles
    • Insert New Worksheet in Excel
    • Excel Column Lock
    • Excel Forms for Data Entry
    • QUOTIENT in Excel
    • Excel Sorting
    • Excel Sort by color
    • Excel Data Bars
    • Excel Tool for Data Analysis
    • Excel Flash Fill
    • Excel Auto Fill
    • Excel Quick Access Toolbar
    • Excel Wrap Text
    • Excel Exponential Smoothing
    • Excel ANOVA
    • Excel Merge Two Tables
    • Excel Conditional Formatting in Pivot Table
    • Dynamic Tables in Excel
    • Excel Sort by date
    • Excel Dynamic Range
    • Record Macro in Excel
    • Two Variable Data Table in Excel
    • Merge Cells in Excel
    • One Variable Data Table in Excel
    • Excel Fill Handle
    • CheckBox in Excel
    • Excel Table
    • Excel Combo Box
    • Auto Format in Excel
    • Advanced Filter in Excel
    • Excel AutoFilter
    • Excel Data Filter
    • Excel Data Validation
    • Excel Radio Button
    • Data Table in Excel
    • Text to Columns in Excel
    • Excel List box
    • Excel Solver Tool
    • Scrollbar in Excel
  • Excel Functions (12+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (14+)
  • Lookup Reference Functions in Excel (34+)
  • Maths Function in Excel (47+)
  • TEXT and String Functions in Excel (26+)
  • Date and Time Function in Excel (23+)
  • Statistical Functions in Excel (59+)
  • Information Functions in Excel (4+)
  • Excel Charts (56+)
  • Excel Tips (222+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (15+)
  • Excel Formula and Functions (21+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • EXCEL ADVANCED Training
  • EXCEL Training COURSE
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

Download Merge Cells in Excel Template

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

डाउनलोड Merge Cells in Excel Template

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

Independence Day Offer - EXCEL ADVANCED Training Learn More