How to merge cells in Excel without losing the data

When it comes to creating a document, it is crucial to establish a clear structure that ensures readability and well-organized information. In Microsoft Word, we can utilize the Styles function to assign different formats to various sections of the text. In Microsoft Excel, the function we need to use in conjunction with Styles for table formatting is called “Merge Cells.”

When creating a table, it is important to include an appropriate title that helps identify the displayed data. This title should be positioned at the top center of the table, indicating that all the data presented relates to that specific table. Many users commonly attempt to write the title by selecting the most central cell and manually adjusting the size and font, resulting in a visually and functionally unsatisfactory outcome. The Merge Cells function, as the name implies, enables us to combine multiple cells both vertically and horizontally, creating a single cell for the title or any desired formatting purpose.

merge excel cells

How to merge cells in Excel

In Microsoft Excel, the function to merge cells can be accessed through the Merge and Center button located on the Home ribbon. However, if we click on the inverted triangle next to the button, a drop-down menu will appear, presenting us with various options: Merge and Center (the default and most commonly used function), Merge Across, Merge Cells, and Unmerge Cells. Each of these options offers different outcomes, which we will explain below.

Merge cells Excel

  • Merge and Center: This function combines all the selected cells into one, occupying the same size as the original cells and centering the text within the merged cell. We can use this function both vertically and horizontally without any limitations.
  • Merge Across: The Merge Across function allows us to merge cells horizontally, combining multiple rows of data independently while keeping each column separate.
  • Merge Cells: By selecting this function, Excel will merge the selected cells without aligning the text to the center. This option is useful when we want to merge cells while preserving the original alignment, resulting in a more visually appealing outcome.
  • Unmerge Cells: This function is used when we want to separate cells that have previously been merged. To do so, simply place the mouse cursor on the merged cell and click the Unmerge Cells button.

Show data from two cells in one

If the goal is to combine the data from multiple cells into a single cell without losing any information, the Combine Cells function in Excel may not be suitable since it results in the disappearance of the text from one of the cells. In such cases, a simple solution is to apply a formula and hide the columns from which the data is being extracted. Here’s how you can do it:

Using &

To combine the data from two columns into a third column using a specific format, you can use the CONCATENATE function or the ampersand (&) operator in Excel. In your case, where you want to combine the first name from cell A2 with a space and the first last name from cell B2, the formula would be:

`=A2 & ” ” & B2`

Here’s how to use it:

1. In the cell where you want the combined data to appear (e.g., C2), enter the formula `=A2 & ” ” & B2`.
2. Press Enter to apply the formula. The data from cell A2, followed by a space, and then the data from cell B2 will be combined in the third column (e.g., C2).

You can then drag the formula down to apply it to the rest of the cells in the third column, combining the corresponding data from columns A and B.

Combine data two columns into one

With the Concat function

Another approach is to use the CONCAT function, which allows us to combine multiple cell values into one. In the given example, if we want column C to show the first and last name from columns A and B, separated by a space, we can use the formula =CONCAT(A2, ” “, B2) in each cell of column C.

Combine data two columns into one

If you encounter any difficulties while applying the formula, you can utilize the Excel formula wizard. You can access it by clicking on the fx button located to the left of the formula input bar. The wizard will provide instructions to guide you through the process.