In your Excel worksheets, you may find the need of Merging and splitting data into cells in Excel worksheets into one huge cell on a regular basis. For example, you might want to join numerous cells to improve data presentation or organization. In some circumstances, there may be too much content for one cell to display, so you merge it with surrounding blank cells.
Whatever the reason, merging cells in Excel is not as simple as it appears. If at least two of the cells you are attempting to connect have data, the regular Excel Merge Cells tool will maintain only the upper-left cell value and ignore values in other cells.
Is there, however, a way to merge cells in Excel without losing data? There is, of course. And farther down in this article, you will find a few methods that work in all versions of Excel 2016, 2013, 2010, and earlier.
Excel’s Merge and Center tool can be used to combine cells
The built-in Merge and Centre option in Excel is the quickest and easiest way to integrate two or more cells. The entire procedure is completed in only two brief steps:
- Choose the contiguous cells that you want to integrate.
- Click the Merge & Centre button on the Home tab > Alignment group.
In this example, we have a list of fruits in cell A1 that we want to combine with two empty cells to the right (B2 and C2) to form a huge cell that can hold the complete list.
When you click Merge and Centre, the selected cells are combined into one cell and the text is centered, as shown in the screenshot below.
Other Excel merging options
Click the little drop-down arrow next to the Merge & Centre button to access a couple more merge options supplied by Excel, and then select the option you want from the drop-down menu:
- Merge Across – separately combine the specified cells in each row.
- Merge Cells – combine the selected cells into a single cell without centered text.
Simply pick the merged cell and click the desired alignment in the Alignment group on the Home tab to change the text alignment after merging.
The limitations and specificities of Excel’s merging features
There are a few things to consider while utilizing Excel’s built-in functions to combine cells:
- Make sure that all of the data you want to include in a merged cell is typed in the left-most cell of the specified range, as only the content of the upper-left cell will survive merging; data in all other cells will be erased. If you want to combine two or more cells that include data, see How to Merge Cells Without Losing Data.
- If the Merge and Centre button is greyed out, the chosen cells are most likely in Edit mode. To exit Edit mode, use the Enter key, and then try to merge cells.
- None of the regular Excel merging options operate for cells included within an Excel table. You must first convert a table to a regular range (right-click the table and pick Table> Convert to Range from the context menu), then merge the cells.
- A range comprising both merged and unmerged cells cannot be sorted.
Merging Cells Without Losing Data in Excel
As previously stated, the typical Excel merge functions only maintain the content of the top-left cell. And, despite the fact that Microsoft has made significant changes in previous versions of Excel, the Merge Cells functionality appears to have slipped through the cracks, and this key constraint exists even in Excel 2013 and Excel 2016. There is, however, a workaround if there is no obvious way.
Method 1: Combine cells in a single column (Justify feature)
Below is one of the quickest and simple approaches for combining cells while retaining all of their content. It does, however, require that all of the cells be merged and reside in the same area in a single column.
- Choose all of the cells you want to combine.
- Make the column long enough to accommodate the contents of all cells.
- Fill > Justify may be found on the Home tab, in the Editing group. The contents of the selected cells will be moved to the top-most cell.
- Depending on whether you want the text to be merged in the center or not, select Merge and Centre or Merge Cells.
If the combined values span two or more rows, widen the column and repeat the operation. This merging technique is simple to apply, however, it has a few drawbacks:
- You can only link cells in a single column with Justify.
- It only works for text; numerical data or formulas cannot be combined in this manner.
- If there are any blank cells between the cells to be combined, it will not operate.
Method 2: Combine numerous cells with data from any range (Merge Cells add-in)
Merge Cells for Excel is a special tool that allows you to merge two or more cells in Excel without losing data or using any extra “tricks.”
You can swiftly merge numerous cells holding any data type, including text, numbers, dates, and special symbols, using this add-in. You can also make use of any delimiter you like to divide the data, such as a comma, space, slash, or line break.
Configure the following settings to link cells exactly how you want them:
- Under “What to merge,” choose Cells into one.
- Under “Separate values using,” choose a delimiter.
- Choose the cell where you wish the result to appear: top-left, top-right, bottom-left, or bottom-right.
- Make sure the option to Merge all areas in the selection is chosen. If this option is not checked, the add-in will behave similarly to the Excel CONCATENATE function, combining data without merging cells.
Apart from combining all cells in the given range, this tool can also merge rows and columns; simply select the appropriate option from the “What to merge” drop-down list. This is how to merge two cells in Excel or numerous cells without losing data. Hopefully, this information has been able to add value to your day-to-day activities.