When working with tables in Excel, users often need to merge some cells. In itself, this task is not difficult if these cells have no data, i.e. they are empty. But what about a situation where cells contain some information? Won’t the data be lost after merging? In this article, we will examine this question in detail.
How to merge cells
The procedure is quite easy and can be used in the following cases:
- Merging empty cells.
- Combining cells, among which only one contains filled data.
First of all, you should select the cells to be merged with the left mouse button. Then go to the “Home” tab of the program menu and find there the option you need – “Merge and center”.
With this method, the selected cells will be combined into one single cell and the contents will be centered.
If you want the information is not in the center, but taking into account the formatting of the cell, you should press the small down arrow next to the icon combining cells and in the menu that appears, select “Merge Cells”.
This method of merging will align the data to the right edge of the merged cell (the default).
The program allows you to merge cells line by line as well. To do this, select the desired range of cells, including several rows and click the item “Merge Across”.
With this way of combining the result is slightly different: the cells are merged into one, but with the breakdown of the rows remains.
How to merge cells using the context menu
Cells can also be merged by using the context menu. To perform this task, select the area to be merged with the cursor, right-click the mouse, and then select Format Cells from the list.
In the window that appears, select the “Alignment” item and check the “Merge cells” checkbox. In this menu, you can also choose other options for merging: wrap text, auto-width, horizontal and vertical orientation, direction, different 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 a situation where several cells contain data? After all, a simple merge will delete all information except for the top left cell.
And there is a solution to this seemingly difficult problem. To do this, you can use the “CONCATENATE” function.
The first step is to do the following. You must add an empty cell between the cells to be merged. To do this, right-click on the number of the column/row before which you want to add a new column/row and select “Insert” from the pop-up menu.
In the resulting new cell, write the formula according to the following pattern: “=CONCATENATE(X;Y)”. X and Y are coordinate values of the cells to be merged.
In our case, cells B2 and D2 need to be connected, so we write the formula “=CONCATENATE(B2;D2)” in cell C2.
The result is that the data in the merged cell will be glued together. However, as you can see, we have as many as three cells instead of one merged cell: two original cells and, consequently, the merged cell itself.
To remove extra cells, right-click on the merged cell. In the drop-down list click “Copy”.
Next, go to the cell to the right of the merged (which contains the original data), right-click on it, and then select from the list the option “Paste Special”.
In the window that appears, choose “Values” among all the options and click “OK”.
As a result, this cell will be the result of cell C2, in which we combined the original values of cells B2 and D2.
Now, after we have inserted the result into cell D2, we can delete the extra cells that are now unnecessary (B2 and C2). To do this, select unnecessary cells/columns with the left mouse button, then right-click on the selected range and select “Delete” in the menu that appears.
As a result, only one cell should remain, which will show the combined data. And all unnecessary cells that arose during the intermediate stages of the work will be removed from the table.
Conclusion
So, there is nothing complicated in the usual merging of cells. But to merge the cells by saving the data you will have to work a little harder. But still, this task is quite doable thanks to the handy functionality of Excel. The main thing is to have patience and follow the correct sequence of steps. We recommend that you make a copy of the document before you begin, just in case something goes wrong and the data are lost.
Note: All operations described above can be applied to both column cells (several columns) and row cells (several rows). The sequence of operations and the availability of functions remains the same.
