In the process of working with Excel, sometimes you want to merge cells together so that the cell is larger in size and better suited to the content. But you do not know how to merge cells in Excel without losing data. So let's see the instructions for merging cells in Excel 2016 that the article shares below, with Excel 2013 and 2010 you can do the same.
Here are 3 ways to merge cells in Excel, you can combine cells in Excel without fear of data loss, please follow along.
Method 1: Merge cells in Excel using Merge & Center feature
This method of merging will help you to merge cells quickly if the cell does not have data or data only in the first cell in the cells to merge. You do the following:
Step 1: In the Excel file, select the cells to merge.
Step 2 : Next, you select the Home tab , in the Alignment section select Merge & Center to merge the selected cells.
So the cells you select will be combined into one cell and the data in the upper left cell will be centered in the newly merged cell.
In addition, you have two other options to merge cells when you select the triangle icon next to Merge & Center :
- If you select Merge Across , the cells you have selected will be aggregated by row, the data in the first cell of each row you select will be retained and displayed from the first position of the merged cell.
- If you select Merge Cells , the cells you have selected are merged into one cell and the data in the first cell in the upper left is kept but not centered as you choose Merge & Center.
Method 2: Combine cells in Excel using VBA code
Using this method, the cells you select will be merged quickly and if your data in all the cells you want to merge will not lose data. You do the following:
Step 1 : In Excel file, select the cells to merge, next you choose View -> Macros tab or press Alt + F8 to open the Macro window .
Step 2: In the Macro window, enter a name for the Macro in the Macro name section and click Create .
Note: macro names must be written immediately without accents.
Step 3: A Visual Basic for Applications window appears , copy the code below into Sub . End Sub. Then click the green Run icon or you select Run -> Run Sub / UserForm to run the Macro macro.
Dim Cll As Range, Temp As String On Error Resume Next If Selection.MergeCells = False Then For Each Cll In Selection If Cll <> "" Then Temp = Temp + Cll.Text + "" Next Cll Selection.Merge Selection.Value = Left (Temp, Len (Temp) - 1) Else Selection.UnMerge End If Selection.HorizontalAlignment = xlCenter Selection.VerticalAlignment = xlCenter
The message appears, click OK , then you turn off Visual Basic, so the cells you selected have been merged.
If you want to merge other cells in the current Excel file, you just need to select (blacken) the cells to be merged, then select View -> Macros or combination of Alt + F8.
The Macro dialog box appears, double click on the name of the Macro that you created above to run the Macro.
So you have included the box.
Method 3: Merge cells in Excel using Concatenate and Merge & Center Functions.
This will also help you to merge cells in Excel and not lose data, but you need to perform an additional step using the Concatenate function to merge data before using Merge & Center to merge cells. You perform the following steps:
Step 1 : You select any cell to enter the CONCATENATE function to combine data in the cells you want to merge.
This function is quite simple to use, if you do not know you can see more about using CONCATENATE here.
For example: You want to combine cells B7, C7, D7; In cell E7 enter the CONCATENATE function as follows = CONCATENATE (B7; ""; C7; ""; D7)
So you have included the data in cells B7, C7, D7 separated by a space, you can customize the CONCATENATE function as you like.
Step 2 : After the data has been merged, you copy the data from the merged cell to the first cell in the cells to merge.
For example, you copy data in a cell containing CONCATENATE function, then select cell B7 (the first cell in the cells to combine), right-click and select Value in Paste Options to paste values only without pasting formulas jaw.
Thus all data will be moved to the position of the first cell on the left, next you delete the CONCATENATE function.
Step 3: Merge the box.
After you have transferred all the data to the first cell in the cells to merge, you select the cells to merge and choose Merge & Center or choose Merge Across or Merge Cell as you want to merge cells.
So you will pool cells in Excel without losing data.
Above are 3 ways to merge cells in Excel, you can apply to merge cells in Excel as needed. Hope this article will help you. Good luck!