In Excel, merging cells is a powerful feature that can help you optimize and enhance your spreadsheets. By combining multiple adjacent cells into one large cell, you can create a clearer and more readable data layout while highlighting specific information. This article provides a detailed guide on how to merge cells in Excel, along with practical tips and tricks.

Merge cells with Built-in feature

• Merge & Center feature
• Shortcuts

Merge cells without losing data

• Merge cells, columns, rows with a handy tool
• Combine cells with formulas
• Merge cells with Justify feature

Find merged cells

Unmerge cells

Video: Merge cells in Excel

Merge cells with Built-in feature

In this case, we need to merge cells A1, B1 and C1 into a single large cell, with cell A1 containing the header title "Q1 Sales" and the rest of the cells being empty. To achieve this goal, you can use the Merge & Center feature or the Merge Cells shortcuts.

Alert of Potential Data Loss:

Be aware that when you use the Merge & Center feature or shortcuts to merge cells, only the value of the upper-left cell will be retained, while the values of the remaining cells will be discarded.

Merge & Center feature

One of the easiest ways to merge cells in Excel is to use the built-in Merge & Center feature. To merge cells with this feature, please do as follows:

Step 1: Select the adjacent cells that you want to merge

Here I select cells A1:C1.

Step 2: On the Home tab, click on the Merge & Center button in the Alignment group

Result

You can see the selected cells are merged into one cell and the text is centered.

Other options of Merge & Center feature

There are other options of the Merge & Center feature. Just click the little drop-down arrow to the right of the Merge & Center button and select the option you want.

Merge Across: Merges the selected cells in the same row into a single large cell.

For example, to merge cells A1, B1, and C1 into a new cell, and merge A2, B2, and C2 into a new cell at the same time, you should select range A1:C2 first, then select the Merge Across option.

Merge Cells: Merges the selected cells into a single cell without centering the text.

For example, to merge cells in range A1:C2 into one large cell without centering the text, you should select range A1:C2 first, then apply the Merge Cells option.

Unmerge Cells: Splits the currently merged cell into multiple separate cells.

For example, to split the merged cell A1 into individual cells, you should select cell A1 first, then apply the Unmerge Cells option. You can see the content of the merged cell is placed into the upper-left cell, and other unmerged cells are empty.

Shortcuts to merge cells

Another way to merge cells in Excel is to use shortcuts. It saves a lot of time when you need to merge cells multiple times.

Merge & Center: + + +

Merge Across: + + +

Merge Cells: + + +

Unmerge Cells: + + +

To use the shortcuts, please follow these steps:

1. Select the cells you want to merge.
2. Press and hold the Alt key to access the commands on the Excel ribbon until an overlay appears.
3. Press H to select the Home tab.
4. Press M to switch to Merge & Center.
5. Press one of the following keys:
• C to merge and center the selected cells.
• A to merge cells in each individual row.
• M to merge cells without centering.
• U to split the merged cells.

Merge Cells without Losing Data

When using the Merge & Center feature or shortcuts to merge cells, it's important to note that data loss can occur, as only the value of the upper-left cell is retained. For instance, if we use the Merge & Center feature to merge the address fields in cells A2, B2, C2, D2, and E2, only the value in cell A2 will be preserved, while the values in the remaining cells will be lost.

To merge cells and keep all the data from the original cells, we can use the following methods.

Merge cells, columns, rows with a handy tool

The Combine Rows, Columns or Cells without Losing Data feature of Kutools for Excel can not only merge two or more cells to a new large cell, but also merge multiple rows or columns into one row or one column, while keeping all the values. Here we demonstrate the method of merging cells into one cell.

After selecting the cells you want to merge, click Kutools > Merge & Split > Combine Rows, Columns or Cells without Losing Data to enable the feature. Then apply the below steps:

1. Select the Combine into single cell option;
2. Specify a separator to delimit the merged values, here I choose the Space option;
3. Click OK.

Result

Combine cells with formulas

Another method for merging cells in Excel without losing data is to use formulas. This approach enables you to combine the contents of multiple cells into a new cell while preserving the original data.

Step 1: Select an empty cell where you want to place the combined data.

Here, I select cell A6 as the destination to place the combined data.

Step 2: Input the formula

Choose one of the following four formulas and enter it into cell A6. Press Enter to get the combined value. (Here I choose the TEXTJOIN formula due to its flexibility and efficiency.)

=A2&" "&B2&" "&C2&" "&D2&" "&E2

=CONCATENATE(A2," ",B2," ",C2," ",D2," ",E2)

=CONCAT(A2," ",B2," ",C2," ",D2," ",E2)

=TEXTJOIN(" ",TRUE,A2:E2)

Result

Merge cells with Justify feature

Using Excel's Justify feature is a quick and easy way to merge cells without data loss. Note that this method only works for contiguous cells within a single column.

Step 1: Adjust the column width to fit all data in one cell

Drag the right border of the heading of column A until column A is set to the desired width. This ensures that the column is wide enough to fit the contents of all cells that need to be merged.

Step 2: Select the cells that you want to merge

Here I select cells A2:A7.

Step 3: Use the Justify feature to merge cells

Go to the Home tab, and click Fill > Justify in the Editing group.

Result

Now the contents of cells A2:A7 are merged and moved to the top-most cell (cell A2).

Find merged cells

Since Excel doesn't sort data in a column that contains merged cells, it's important to determine whether there are any merged cells in your worksheet and identify their locations. To find the merged cells in your worksheet, you can use the Find and Replace feature in Excel.

Step 1: Press Ctrl + F to open the Find and Replace dialog box

Step 2: Specify the Format settings to find merged cells

1. Click Options to expand more settings.



2. Click Format.



3. In the Find Format dialog box, go to the Alignment tab, and check the Merge cells option. Click OK.



4. Click Find All to locate the merged cells.

Result

The list of merged cells is now displayed at the bottom of the dialog box. You can click any item in the list to select the merged cell, or you can use the Ctrl + A keys to select all the merged cells in your worksheet.

Unmerge Cells

To unmerge cells in Excel, please do as follows:

Step 1: Select the merged cells you want to unmerge

Here I selected the merged cells A2, A5 and A8.

Step 2: Use the Unmerge Cells option to split cells

On the Home tab, click on the drop-down arrow near the Merge & Center button in the Alignment group. Then click the Unmerge cells option from the drop-down menu.

Result

You can see that after unmerging the cells, the previously merged cells are separated. The content from each of the original merged cells is now placed into the upper-left cell, leaving the rest of the unmerged cells empty.

Related articles