Merge Cells in Excel

Merge Cells in Excel (Table of Contents)

• Merge Cells Using Merge & Center
• Merge cells Using CONCATENATE
• Merge Cells Using Ampersand (&) Operator
• Trick to Quickly Merge Multiple Cells

Merge Cells in Excel

Our requirements while using Excel can be really varied, each scenario requiring the use of a different formula, some other feature of Excel, or a combination of both. Quite often our objective might be to merge the contents of multiple cells into one so as to present the data in a different manner.

There are a few ways to go about this- to merge cell values in Excel. The purpose of this article is to show the ways in which merging cells is possible in Excel. We shall discuss the advantages and the drawbacks of each method and thus try to suggest which method is ideal for merging cells in Excel.

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.

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.

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.

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

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.

Step 4: Click on “OK”

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

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

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

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

Similarly, We will Get Other Result.

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.

Using this method, we can merge multiple cells into one, 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 – instead of the CONCATENATE function, we shall use the “&” operator.

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

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

This would give us the following result:

Similarly, We will get another Result.

Merge Option

Now let us discuss the different options in the “Merge” option that we had 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 center.

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

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

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

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.

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.

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

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

 

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.

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

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

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

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

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

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

As we can see, the merging has been carried out for the rest of the years by simply clicking on “Format Painter”. 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.
• Merge option is limited to merging cells and not the cell values.
• In case we have to merge the cell values then 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 downloadable excel template. You can also go through our other suggested articles –

1. How to Insert Clustered Bar Chart?
2. Insert Pie Chart in Excel
3. How to Use ROUNDUP Function?
4. Guide to QUARTILE Function in Excel