Contents
When working with tables in Excel, users often need to merge some cells. By itself, this task is not difficult if there is no data in these cells, i.e. they are empty. But what about the situation when the cells contain any information? Will data be lost after merging? In this article, we will analyze this issue in detail.
Content
How to merge cells
The procedure is quite easy and can be used in the following cases:
- Merge empty cells.
- Merging cells where only one contains filled data.
First of all, you need to select the cells to be merged with the left mouse button. Then we go to the program menu on the “Home” tab and look for the parameter we need there – “Merge and place in the center”.
With this method, the selected cells will be merged into one single cell, and the content will be centered.
If you want the information to be not centered, but taking into account the formatting of the cell, you should click the small down arrow located next to the cell merge icon and select the “Merge Cells” item in the menu that opens.
With this method of merging, the data will be aligned to the right edge of the merged cell (by default).
The program provides the possibility of line-by-line merging of cells. To execute it, select the required range of cells, which includes several rows, and click on the “Merge by Rows” item.
With this method of merging, the result is somewhat different: the cells are merged into one, but the row breakdown is preserved.
Cells can also be merged using the context menu. To perform this task, select the area to be combined with the cursor, right-click, and then select “Format Cells” from the list.
And in the window that appears, select the item “Alignment” and put a tick in front of “Merge cells”. In this menu, you can also select other merging options: text wrapping, auto-width, horizontal and vertical orientation, direction, various alignment options, and more. After all the parameters are set, click on “OK”.
So, as we wanted, the cells merged into one.
How to merge cells without losing data
But what about when multiple cells contain data? Indeed, with a simple merger, all information, except for the upper left cell, will be deleted.
And this seemingly difficult task has a solution. To do this, you can use the “CONNECT” function.
The first step is to do the following. An empty cell must be added between the merged cells. To do this, you need to right-click on the column / row number before which we want to add a new column / row and select “Insert” from the menu that opens.
In the resulting new cell, write the formula according to the following template: “=CONCATENATE(X,Y)“. In this case, X and Y are the values of the coordinates of the cells being merged.
In our case, we need to concatenate cells B2 and D2, which means we write the formula “=CONCATENATE(B2,D2)” to cell C2.
The result will be gluing the data in the merged cell. However, as you can see, we got three whole cells, instead of one merged one: two original ones and, accordingly, the merged one itself.
In order to remove extra cells, click (right-click) on the resulting merged cell. In the drop-down list, click “Copy”.
Next, go to the cell to the right of the merged one (which contains the original data), right-click on it, and then select the “Paste Special” option from the list.
In the window that opens, select “Values” from all the options and click “OK”.
As a result, this cell will contain the result of cell C2, in which we combined the initial values of cells B2 and D2.
Now, after we have inserted the result into cell D2, we can delete extra cells that are no longer needed (B2 and C2). To do this, select extra cells / columns with the left mouse button, then right-click on the selected range and select “Delete” in the menu that opens.
As a result, only one cell should remain, in which the combined data will be displayed. And all the extra cells that have arisen at the intermediate stages of work will be removed from the table.
Conclusion
Thus, there is nothing complicated in the usual cell merging. But to merge cells while retaining the data, you have to work a little. But still, this task is quite feasible thanks to the convenient functionality of the Excel program. The main thing is to be patient and follow the correct sequence of actions. We recommend that before starting work, just in case, make a copy of the document, if suddenly something does not work out and the data is lost.
Note: All of the above operations can be applied to both column cells (multiple columns) and row cells (multiple rows). The sequence of actions and the availability of functions remain the same.