How to merge Cells in Excel without losing the data

What is called merging the Cell in Excel?

As the name suggests, merging the Cell is combining two or more adjacent cells into a single cell. It combines multiple cells into a single cell, which creates a large space for data or formats data in a more visually appealing way. The process of merging the Cell is done for formatting purposes, such as creating the header or title which spans several columns or combining the cells which contain similar cells. Based on the data requirement, the Cell is combined. If the user wants to combine two or more cells, the standard excel merges the Cell by keeping the upper left Cell, and the value in the other data is discarded. Is there any solution to merge the Cell without losing the data? Yes, it is possible using Excel functions and formulas.

The purpose of Merging Cell

The purpose for merging cells is listed below as follows,

1. To create a title or header
2. Similar data is combined, for example, if the data contains product names, prices or quantities are combined for easier calculation. It makes the data more organizable and readable.
3. It creates a larger space for data. For example, a larger space for a title or header, picture, or chart.
4. It creates the table structure with a required number of rows or columns.
5. It aligns the data in the Center in a merged cell.

How to merge cells in Excel?

Merge Cell using the Alignment option

Excel provides a default option to merge cells. The alignment option aligns the data in the Cell based on the user's preference.

Method 1: To merge Cell in Excel, the steps to be followed are,

Step 1: Select the Cell which needs to be merged. Highlight the Cell with the mouse from the selected Cell, holding shift using the arrows to select the end.

In the worksheet, the List of Items is present in cell A1. The cells will be merged to expand and make the "List of Items" a title.

Step 2: In the Home Tab, select the Format button. Another option is to use the keyboard shortcut Ctrl+1, which displays the Format cell dialogue box.

Step 3: In the Format Cells dialogue box, click the Alignment option and choose the Merge Cell option in the Format Cells.

Step 4: Click Ok. The merge cell option merges the Cell in the worksheet. Place the title towards the Center and choose the background color according to your preference.

Centre Cross Function

As the name suggests, the Centre Cross Function aligns the text without combining the Cell. For a larger data set, this method helps to combine easily, saving the users time without merging the Cell.

Method 2: Merge the cells using the Center across the Selection function

Here is another alternative method to merge the cells using the Centre across Selection Method. The Cell need not be merged while using this method. The steps to be followed are,

Step 1: Select the Cell containing the data that needs to be placed in the Center using the Shift key or keyboard arrows.

Similar to example 1, the Cell containing the "List of Items" needs to be placed Center.

Step 2: In the Home Tab, select the Format button. Another option is to use the keyboard shortcut Ctrl+1, which displays the Format cell dialogue box.

Step 3: In the Format button, click the Alignment tab. In the Horizontal tab, click the drop-down list. There display several options that choose "Centre Across Selection" from the list. Click OK.

Step 4: The title automatically aligns toward the Center of the worksheet.

Here the title "List of Items" is aligned towards the Center, and the background color is selected based on the preference.

Combining cells without losing the data

From Method 1 and Method 2, the steps to combine Cells are explained. Data loss may occur if the worksheet contains a large data set while merging the cells. To avoid data loss, Excel provides formulas and functions to merge the data present in the Cell. The methods are explained in the following example.

Ampersand symbol

The ampersand symbol represents the meaning 'AND .'It is used in titles, headings, and labels to represent conjunction between two or more items. It is used in a programming language, which indicates the meaning of AND logical operator.

Example 1: Combine the data present in two Cell using the ampersand (&) symbol

The Ampersand symbol is used to combine two cells. The steps to be followed are,

Step 1: Enter the required data in Cell A1 and B1.

Step 2: Type the "=" symbol in the new Cell, A2, and select the first Cell that needs to be combined. Here cell A1 is selected.

Step 3: Type the ampersand symbol and place the quotation mark with the enclosed space. Place the ampersand symbol after the quotation mark. Select the next Cell which needs to be combined. Here cell B1 is selected.

Step 4: Hence the formula is written as =A1&" "&B1. Press Enter. The result will be displayed in the selected Cell, namely A2.

Here in the worksheet, the data present in the two cells are merged without losing the data. Jack is present in first cell A1, and Fruit is present in B1, combined using the formula, and the original data is present in the worksheet.

Example 1.1 Combine the multiple columns of data in a single column using the ampersand symbol.

In Example 1, the formula using the ampersand symbol is used to combine the cells without data loss. Similarly, another method uses an ampersand symbol to combine multiple columns of data in a single column.

Step 1: Enter the data in the worksheet, namely A1:C7.

Step 2: Select a cell where the user wants to display the result, D2, and enter the formula as =A2&B2&C2.

Step 3: Press Enter. The result will be displayed in cell D2, the combination of cells A2, B2, and C2.

Step 4: To get the result for the remaining data, drag the Autofill Handle down to apply the formula towards cell D7.

In the worksheet, the multiple columns of data are merged into a single column. The formula varies for Example 1 and Example 1.1 using the ampersand symbol.

CONCATENATE or CONCAT function

Generally, Concatenation refers to the operation of joining two or more strings, lists, arrays, or together. The syntax for the concatenation function varies depending on the programming language. In Excel, the syntax for the CONCAT function is,

=CONCATENATE (A1,",", B1). Here A1 and B1 are referred to as cells containing the data.

Example 1: Combine the data present in two Cell using CONCATENATE function

The concatenation function is used to merge the data present in the second Cell into the first Cell. The steps to be followed are,

Step 1: Enter the required data in Cell A1 and B1.

Step 2: Select a new cell, A2, where the user wants to display the result.

Step 3: In cell A2, type the formula as =CONCATENATE (A1,",",B1). Press Enter.

Step 4: The result will be displayed in cell A2, the combination of data in cells A1 and B1.

Similarly, the formula is modified for a large number of data.

Example 1.1 Merge the multiple cells in a row in a single cell using the Concatenation function

In a limited data set, to combine the multiple cells present in the row, the formula used is =A1&B1&C1. For larger data sets, it takes more time and is difficult. To rectify this problem, using Concatenation, the formula is modified. The steps to be followed are,

Example 1.1 Merge the cells in the multiple rows in a single column using the Concatenation function.

Step 1: Enter the data in worksheet A1: C7.

Step 2: Select a new cell where the user wants to display the result, namely D2, and enter the formula as =CONCATENATE ("1001," KASHMIR," APPLE,"). Here the data 1001, KASHMIR, and APPLE are the data in A2, B2, and C2.

Step 3: Press Enter. The result will be displayed in cell D2, the combination of data in cells A2, B2, and C2.

Step 4: To get the result for the remaining data, drag Auto Fill handle down to cell D7. It follows the pattern in cell D2 and fills the result for the remaining Cell.

The data in the worksheet are separated using a comma with the CONCATENATE function.

TEXTJOIN Method to merge the cells without data loss

TEXTJOIN is one of the methods to merge the data present in the Cell without data loss. The steps to be followed are,

Example 1: Combine the data present in a single column in a single row.

Step 1: Enter the data in the worksheet, namely A1:A7

Step 2: Select a new cell where the user wants to display the result, namely B1, and enter the formula as =TEXTJOIN (", TRUE, A1:A7)

Step 3: Press Enter. The result will be displayed in cell B1, the combination of Cell A1:A7.

Here the data present in A1:A7 is combined using TEXTJOIN without data loss.

The formula contains three arguments as follows,

Delimiter- It is used to add in between values that need to merge into one Cell

Ignore empty - True or false value is defined to determine whether the user wants to ignore the empty Cell.

range_array - Range array is the range of cells that must be merged.

Merging the cells in Clipboard without data loss

Using a clipboard, the user can merge the columns of data in one Cell without data loss. The steps to be followed are,

Step 1: Enter the required data in the worksheet, namely A1:B7. Click the Clipboard symbol present in the top left in the Home tab.

Step 2: Select the range of cells that must merge and press Ctrl+C. Another method to copy the data is choosing Home>Copy.

The data in the worksheet are copied and pasted into the Clipboard.

Step 3: Double-click the empty Cell where the data needs to be pasted. Here the Cell selected is E1. After clicking the empty Cell, click the data present in the Clipboard. The data will be pasted into the selected Cell.

The columns of data are pasted in a single cell without losing the data.

Merge the cells using Excel's Merge and Centre Option

The default Excel option, Merge, and Center, combines the data in various cells quickly. The steps to be followed are,

Step1: Enter the data in the respective Cell, namely A1

Step 2: Select the Cell which needs to be merged. Here cell A1 is selected along with the required number of empty Cells, namely B1 and C1, to center the data. In cell A1 the various fruits' names are entered, such as Apple, Orange, Mango, and Plums.

Step 3: Select the Merge and Centre Options in the Home tab.

Here the name of four fruits is entered in cell A1. The Merge and Center option fits and makes the data center.

List of options present in Merge and Centre

The Excel option present in Merge and Centre is as follows,

Merge Across

The Merge across option combines each row individually.

Step 1: Enter the data in the respective Cell, namely A1

Step 2: Select the Cell which needs to be merged. Here cell A1 is selected along with the required number of empty Cells, namely B1 and C1, to merge across the data. In cell A1, the various product names are entered, such as Water Bottle, Pen, Pencil, and Paper.

Step 3: Select the Merge and Centre Options in the Home tab. There display several options that choose Merge and across the option.

The data present in A1 are arranged in Merge and across the manner.

Merge Cells

The merge cell option combines the selected Cell without centering the text.

Step 1: Enter the data in the respective Cell, namely A1

Step 2: Select the Cell which needs to be merged. Here cell A1 is selected along with the required number of empty Cells, namely B1 and C1, to merge across the data. In cell A1 the various products name are entered, such as Carrot, Beans, Tomato, and Cabbage

Step 3: Select the Merge and Centre Options in the Home tab. There display several options that choose the Merge Cells option.

The data present in cell A1 is combined with cells B1 and C1 without centering the data.

If the text alignment needs to be changed after merging, choose the alignment option in the Home tab and choose the desired option.

Merging the data in a single column

The data in various cells are aligned in a single column using the Excel default function called Justify. It keeps the data in a content way by assigning it to a single area column.

Step 1: Enter the data in cells A1, A2, and A3.

Step 2: Increase the column width of the data which fits the selected data. Here cell A1 is selected along with cells A2 and A3. In cells A1, A2, and A3, the various title named Groceries, fruits, and Vegetables are entered.

Step 3: Select the Fill option in the Edit group from the Home tab. There display several options in the drop-down list. Choose the option called Justify.

Here, the data in cells A1, A2, and A3 are aligned in a single column using the Justify function.

Unmerge the Cells in Excel

If the user wants to unmerge the Cell after the Cell is merged, Excel provides several options to unmerge the Cell.

Step 1: Cell A1 contains the merged data of jam, glass, and Ice

Step 2: The cells A1 is selected.

Step 3: Select the Merge and Centre option in the Home tab. Select the option Unmerge in the drop-down list.

The unmerge cells unmerge the data present in cell A1.

Summary

The various functions, formulas, and methods which are used to merge cells without the loss of data in Excel are explained in this tutorial. Based on the data requirement, the formula is modified. The described methods are quick and effective. One is used to calculate the data.