How to merge cells in Microsoft ExcelIt 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:
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.
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:
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:
How can one troubleshoot Common Issues related to the Merged Cells in Excel?
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:
Next TopicHow to enable macros in excel |