Merge and combine cells in Excel (Easy step-by-step guide)
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.
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
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:
Select the cells you want to merge.
Press and hold the Alt key to access the commands on the Excel ribbon until an overlay appears.
Press H to select the Home tab.
Press M to switch to Merge & Center.
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.
Note:To apply this Combine Rows, Columns or Cells without Losing Data feature, firstly, you should download and install Kutools for Excel.
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:
Select the Combine into single cell option;
Specify a separator to delimit the merged values, here I choose the Space option;
To merge multiple rows or columns into one row or one column, the Combine Rows, Columns or Cells without Losing Data feature can also do the trick. The demo below shows how to combine 5 columns of data without losing data .
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.
Here we introduce four different formulas you can apply to combine cells with no data loss.
Ampersand (&) - Available in all versions of Excel.
CONCAT - Available in Excel 2016 and newer versions, as well as in Office 365.
TEXTJOIN - Available in Excel 2019 and newer versions, as well as in Office 365.
TEXTJOIN is recommended for combining multiple cells as it offers greater flexibility and efficiency compared to CONCATENATE and CONCAT.
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)
In the above formulas, you can specify the separator as you need. For example, a comma with a space. The formulas now becomes:
=A2&", "&B2&", "&C2&", "&D2&", "&E2
=CONCATENATE(A2,", ",B2,", ",C2,", ",D2,", ",E2)
=CONCAT(A2,", ",B2,", ",C2,", ",D2,", ",E2)
If your goal is to merge the original cells, you can proceed as follows:
After concatenation, copy the resulting value and paste it as Values into the top-left cell of the range you're planning to merge. Then, employ the Merge & Center featureto merge the cells as desired.
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.
Now the contents of cells A2:A7 are merged and moved to the top-most cell (cell A2).
Limitations of the Justify feature:
Single Column Restriction: Justify can only be used to merge cells within a single column, and it won't work for merging cells in multiple columns simultaneously.
Text-Only Merging: This method is suitable for text merging only. If you have numerical values or formulas in the cells you want to merge, the Justify feature won't work.
Contiguous Cells Required: The cells to be merged must form a continuous, uninterrupted range in the column. If there are any blank cells in between the cells you wish to merge, using Justify will not retain the data correctly .
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
Click Options to expand more settings.
In the Find Format dialog box, go to the Alignment tab, and check the Merge cells option. Click OK.
Click Find All to locate the merged cells.
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.
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.
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.
How to merge cells in a range formatted as table in Excel? In Excel, you can easily merge some consecutive cells into one with the Merge & Center utility as below screenshot shown. However, it cannot merge the cells in a range which formatted as a table in Excel, is there any trick to handle it?
How to merge cells without centering the contents in Excel? In Excel, when merging two or more cells in Excel, the Merge & Center function can help you. But in sometimes, how can you do if you just want to merge cells but do not center the contents as below screenshot shown? Here, in this tutorial, I introduce the methods on solving this job in Excel.