How to merge cells in Microsoft Excel

It was well known that the respective Merge cells in Microsoft Excel are purposely used for the purpose of merging out more than one cell effectively, and this is quite useful when we are required to assign one value of the cell to 2 or more cells in the selected sheet.

And for the purpose of merging out the cells in Microsoft Excel, we will be then selecting only those particular cells, from the home menu tab, we will be also selecting Merge & Center option from the given alignment section, and this will effectively combine both of those selected cells. And still, it will be keeping only the first value, and other value will be got eliminated.

Now in this tutorial, we will be discussing the below-mentioned topics as the part of the "Merge cell" in detail:

1. How we can make use of the "Merge and center" option to merge cells in Microsoft Excel?
2. How one can merge cells in Microsoft Excel by making use of the "Ribbon"?
3. How can one easily merge cells by just making use of the keyboard shortcut in Excel?
4. What are the best ways to be used Merging out the Cell in Microsoft Excel?
   1. Merging of the Cell by using the CONCATENATE option in Microsoft Excel.
   2. Merging of the cells by making use of the Ampersand (&) operator in Microsoft Excel.
5. What is the different Merge Option which is available in Microsoft Excel?
6. How can one easily unmerge cells in Microsoft Excel?
7. Explain the trick to Merge Multiple cells in Microsoft Excel quickly.
8. What are the various alternatives associated with the Merging of the Cells in Microsoft Excel?
9. How can one troubleshoot Common Issues related to the Merged Cells in Excel?
10. What are the important things which need to be remembered by an individual while working with the Merging of cells in Microsoft Excel?

How we can make use of the "Merge and center" option to merge Cells in Microsoft Excel?

The word "Merging" basically means having the combined values of the multiple cells in the single cell. And perhaps it is one of the most straightforward methods which can be used to achieve this by just making use of the "Merge & Center" option, which is made available in the Home ribbon option as well.

Besides all this, there is a drawback with this method of merging of the cells in Microsoft Excel. And if in case we have some amount of the data in the given cells that is A1 as well as B1, this form of Merging will effectively result in the retention of the left-hand value as well as the overwriting of the right-hand value.

After that we will be looking at an example for its better understanding of this drawback in an effective manner:

Now we will be consider that we have a list in which the first column (A) usually contains the first names, while the second column (B) used to contain the last names. And if we want to merge the contents of the "First Name" as well as the "Last Name," then this method would fail it is because of the reason that it only contains the left-hand value, completely just overwriting the right-hand values.

Step 1: First of all, we are required to select out the cells which we wish to merge together in a given cell. And in this particular scenario, we shall also select the cells A2 and B2.

Step 2: Next, we will click on the "Merge & Center" option, which is effectively present on the Home ribbon.

Step 3: At the moment we click on the "Merge & Center" option, we will be then immediately getting a dialogue box pop-up which shows a warning message.

Step 4: After that, we will also click on the "OK" option.

Step 5: After that in this respective step, we will be clicking on the "OK" option, and soon we will be getting our result.

So, what happened here is that, the Microsoft Excel will now merge out the given two cells instead of the cell values as well. Due to this, we did get a merged cell, but we will lost the "Last Name" in this while process as well, and this would become the main drawback of making use of the "Merge & Center" option in Microsoft Excel.

How to merge cells in Microsoft Excel by making use of the "Ribbon"?

Now to merge the Cell in Excel by making use of the "Ribbon" option, we are required to follow this step in the given order:

1: First of all, we are required to select the cells which we want to merge by just dragging over the respective cells or just clicking in the first cell, and after that, we will Shift-click in the last cell as well. And the cells must be adjacent to each other respectively.

2: Now, after that, we will be clicking on the Home tab, which is present under the Ribbon.

3: And in this step, in the Alignment group, we will be right-clicking on the arrow beside the Merge & Center, and a drop-down menu will get appears on our screen as well.

4: Then, after that, we will select the merge option we want. And the data in the leftmost top Cell or the uppermost Cell will merge across the individual selected cells. And the data in the other cells will be removed efficiently.

And the respective options to merge cells as well as unmerge cells will get appear in the Ribbon when we will click on the arrow beside Merge & Center as well:

How can one merge cells by using the keyboard shortcut in Excel?

Now if we want to access the respective commands on the Ribbon in Microsoft Excel by using our keyboard, then in that scenario, we can quickly press the Alt shortcut button from our keyboard to display critical tips and then, after that, will press the displayed key(s) as well.

Merging of the cells by making use of the keyboard

# Example 1:

In this particular example, below is the report which is basically related to the final sales, and shows the sales data organized by year, sales manager, as well as in accordance to the region, under the main heading of the "YEAR," each year is listed as the subheading, for the purpose of improving the visual appeal of the report; and for this we are required to merge the "YEAR" cell with the adjacent cells.

Step 1: First of all, we are required to select out the number of the cells which we primarily want to merge. And in this particular example, we want to merge the out cells containing the "YEAR" as the main heading from cell B1 to E1. And in order to achieve this quickly, we will be also clicking on the shortcut key that is ALT + H + M + M from our keyboard respectively.

Step 2: After that, we are required to merge the cells which are containing the region names and to achieve this; we will select the cells A1:A2, and then we will make use of the same shortcut key ALT + H + M + M.

Performing the above will efficiently merge the two cells into one. With the merged cells, the report looks much neater and easier to read to the client.

# Example 2:

So in this example, we will be having the data for the "Target" as well as the Actual numbers for the years that is none other than 2021, 2022, and 2023, after that we will be seeing how one can easily merge out the cells for each year as well as displaying out the Target and the Actual data below the corresponding year.

Step 1: First of all, we are required to select the cells A1:B1 and will click on the shortcut key that is none other than ALT + H + M + M from our keyboard.

Step 2: Now after that, we do not have to make use of the merge function for all the remaining years, rather than just simply selecting out the following year, and will click on the F4 function key to repeat the previous action. And it will recall the previous action which we have performed.

Different Alignment Options in Microsoft Excel

It was well known that, under the alignment option, we would have different alignment options. Wrapping of the Text, left alignment, right alignment.

Under the "merge & center" function, there are primarily four options which are as follows:

1. Merge & Centre: Merge center option will effectively merge out the cells as well as make the alignment in the center.

2. Merge Across option will now merge out the cell and make out the alignment in the left alignment.

3. Merge Cells: This will now effectively merge out the cell and makes out the left alignment.

4. Unmerge Cells: Unmerge cell is responsible for the purpose of removing out all merged cells to make the value allocated to one cell.

What are the best ways to be used Merging out the Cell in Microsoft Excel?

So, what is the correct as well as the most effective way that can be used to merge cells in Microsoft Excel? Here is the answer to that.

1. Concatenation formula
2. Ampersand (&) Operator

Let us now we will see each of these options in detail respectively:

a) Merging of the cells by making use of the CONCATENATE.

We all know that the respective Microsoft Excel has a vast array of the formulas which are purposely used for the various needs as well. And the particular CONCATENATE formula can helps us to merge the contents of multiple cells into one cell. By making use of this method, we can successfully merge out the "First Name" as well as the "Last Name" without losing any of the data in the process.

Step 1: First of all, we are required to select out the cell that is C2.

Step 2: After that, we will apply the CONCATENATE formula as depicted below.

Step 3: So now, after applying this formula, we will be now getting the correct results as well:

Formula:

=CONCATENATE (A2,"",B2)

Step 4: And after that, we will be getting out the other Results respectively.

After that, if we look at the breakup of the formula, we will better understand it:

• A2: A2 is considered the very first cell we wish to merge.
• ": It is primarily a space which lies in between of the First Name as well as the Last Name respectively.
• B2 = And cell B2 is termed the last Cell 3, which we wish to merge respectively.

We can easily merge out the various multiple cells into one by just making use of this method, but it will also be used in a different cell.

b) Merging of the Cells by making use of the Ampersand (&) Operator

Similar to how we have effectively merged out the two cells by just using the CONCATENATE function, we shall make use of the Ampersand (&) Operator as well, but the only difference will be that we shall make use of the "&" Operator instead of making use of the CONCATENATE function respectively.

Step 1: First of all, we will be selecting out cell C2:

Step 2: After that, we must apply the formula as depicted below.

Formula:

=A2&""&B2

And performing the above would give us the following result as well:

And similarly, we will get another Result respectively.

What is the various merge option which is available in Microsoft Excel?

Let us now briefly discuss out the different options which are available in the "Merge" option, as mentioned earlier.

Merge & Center: The Merge and Center option used efficiently used for the purpose of merging out the selected cells by keeping only the left-hand value as well as making out the text alignment on the center.

Merger Across: And the "Merge Across" options in Microsoft Excel help us to merge out the selected cells but not change the alignment.

Merge Cells: The "Merge Cells" option is perhaps the most straightforward option, and it would just merge out the selected cells respectively.

Unmerge Cells: The "Unmerge Cells" option is precisely the opposite of Merge; it unmerges the cells respectively:

How can one easily unmerge cells in Microsoft Excel?

We will be then assumes that we are having the same set of merged cells which we had seen earlier with the "Merge & Center" option as well.

So for the purpose of unmerging out these cells, we are required to again go to the Home ribbon, navigate to the "Merge & Center" option, and then we will click on the drop-down arrow at the side to open up the underlying options as well.

And just after that, we will select the "Unmerge" option to unmerge the selected cells effectively.

And hence the merged cells have been "unmerged" by just making use of this option as well:

Explain the trick to merge multiple cells in Microsoft Excel quickly.

Suppose that, we are required to merge out the various multiple cells in Microsoft Excel, and our basic objective here is to just merge all the cells for a given "Year" and for all entries for the given year as well.

Step 1: We will proceed further by selecting the cells from A2 to A4.

Step 2: Now in this step, we will moving to the Home ribbon option and will click on the "Merge & Center" option respectively.

As per the above screenshot we can see that, the selected cells such as A2, A3, and A4 have been successfully merged out.

Step 3: And in these we will be clicking on this merged cell and will click on "Format Painter" in the Home Ribbon.

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

Step 4: After that, we must select and drag out the given cells from A5 to A10.

And we can see that, the Merging has been carried out for the rest of the years by just simply clicking on the "Format Painter." And the format Painter will be essentially copy out the same format from the source cells(s) and after then it will apply it across all the destination cells effectively.

What are the various alternatives associated with the Merging of the Cells in Microsoft Excel?

We all know that merging the individual cells in Microsoft Excel can sometimes cause formatting and sorting issues and make it difficult to enter the data. And here are some of the alternatives that can be used for the purpose of merging cells efficiently:

• Center-align Text: Now we can easily center-align out the Text across the multiple cells without merging them, and to do this, we are required to select the cells which we want to center-align, and after that, we will click on the "Alignment," group in the "Home" tab, and then click on the "Center across Selection" as well.
• Wrap Text: Much more we can also make use of the "Wrap Text" option to display the Text in multiple lines within a given cell, and this option is present just under the "Alignment" group in the "Home" tab.
• Column Width: We can also effectively increase the width of a column to fit out the content without merging the cells, and to achieve this, we will be dragging out the right border of the column header to the desired width as well.
• Conditional Formatting: We can also make use of the respective conditional formatting to highlight the specific cells which will meet out certain criteria, which could be achieved without merging the cells. For this purpose, we will then select the cells we want to format, click on the "Conditional Formatting" option in the "Home" tab, and choose the formatting option that best suits our requirements.
• Tables: The tables can also effectively help us organize the data to make it easier to view and work with without merging the cells. We can also create a table by just selecting the data range and then will press the shortcut from our keyboard that is none other than "Ctrl + T" or by just clicking on the "Table" option present under the "Insert "option.

How can one troubleshoot Common Issues related to the Merged Cells in Excel?

• Sorting: When an individual is merging out the cells, then sorting the data by column may not work as expected, and to fix this, one can easily unmerge the cells as well as sort the data again, and the individual can also try using the "Sort Left to Right" option in the "Sort & Filter" group on the "Data."
• Alignment: And merging of the cells sometimes causes alignment issues, especially if the cells are merged and have different formatting styles as well, and to fix this, we can easily select the merged cell as well as adjust the alignment settings, like as horizontal as well as the vertical alignment, to our liking respectively.
• Data Entry: Merging cells can make it challenging to enter out the data, especially if in case the merged cell is not wide enough, and to fix this, we will be unmerging the cells as well as adjusting the respective column width to fit the data, and alter, can make use of the "Center Across Selection" option which are present under the "Alignment" group on the "Home" tab to center-align the Text across the multiple cells without merging them.
• Formatting: Merging of the cells can sometimes cause formatting issues, as merged cells have various fonts or the colors, to fix this we can easily unmerge the cells as well.

What are the important things which need to be remembered by an individual while working with the Merging of cells in Microsoft Excel?

The various essential things which need to be remembered by an individual while working with the Merging of Cell in Microsoft Excel are as follows:

1. An individual should remember that the sorting of the data over the merged cells in Excel is quite impossible in nature.
2. And it must be kept in mind that the Navigation of the data via merged cells can become complex and cumbersome.
3. And it is advisable to make use of the merge cells option only for the headings.
4. The respective merge option is very much limited to the merging cells and not to the cell values.
5. If an individual merges the cell values, then in that case, an individual must use the CONCATENATE function or the Ampersand (&) Operator, respectively.
6. And there is a shortcut to merge cells in Excel that is none other than Alt + H + M + U. This combination of key presses merges and unmerges the cells selected in the Microsoft Excel sheet.
7. If an individual is required to merge the given cells horizontally, then in that case, an individual can easily select the required cells and will press out the shortcut keys from their keyboard, "Alt + H + M + M" in sequence.
8. And if, in case, an individual is required to merge the given cells vertically, then in that case, an individual must need to select the required cells and will then press the shortcut keys from their keyboard "Alt + H + M + V" in sequence.
9. When an individual merges out the cells in Microsoft Excel, then the Text in the upper-leftmost cell will remain same, and the contents present in the other cell will be get deleted.
10. And it must be kept in mind that the keyboard shortcuts used for the purpose of merging the cells may vary depending on the version of the Microsoft Excel which an individual make use of it.