How To Merge Two Columns In Excel Without Losing Data

The tutorial demonstrates different techniques to quickly merge ii cells in Excel and combine multiple cells row by row or cavalcade by column without losing information in Excel 365, Excel 2019, 2016, 2013, 2010 and lower.

In your Excel worksheets, you may often need to merge two or more cells into 1 large prison cell. For example, you may want to combine several cells for a better data presentation or structure. In other cases, there may exist too much content to be displayed in one cell, and you decide to merge it with adjacent bare cells.

Whatever the reason, combining cells in Excel is non every bit straightforward as it may seem. If at least ii cells you are trying to join contain data, the standard Excel Merge Cells characteristic will only keep the upper-left cell value and discard values in other cells.

Merely is at that place a style to merge cells in Excel without losing data? Of grade there is. And farther on in this tutorial, you will find a few solutions that piece of work in all versions of Excel 2016, Excel 2013, Excel 2010 and lower.

Excel'southward Merge & Heart characteristic
Merge ii or more cells without losing data
Shortcut to merge cells in Excel
How to find merged cells
How to unmerge cells
Alternatives to merging cells in Excel

Combine cells using Excel'southward Merge and Center characteristic

The fastest and easiest way to combine two or more than cells in Excel is to use the built-in Merge and Center option. The whole process takes merely two quick steps:

Select the contiguous cells you want to combine.
On the Habitation tab > Alignment grouping, click the Merge & Center

In this case, we have a list of fruits in jail cell A1 and nosotros desire to merge information technology with a couple of empty cells to the right (B2 and C2) to create a big cell that fits the entire list.
Combine cells in Excel using the Merge and Center feature

In one case you click Merge and Center, the selected cells will exist combined into one prison cell and the text is centered similar in the post-obit screenshot:
Merged and centered cells

Join Excel cells into one

Combine multiple cells into one cell

Merge Cells Wizard for Excel Read more than

Quickly merge cells without whatever formulas!

And keep all your data safe in Excel

Merge Cells Wizard for Excel Read more than

Other merging options in Excel

To admission a couple more merge options provided by Excel, click the little drib-down pointer side by side to the Merge & Center button and cull the pick you lot want from the drop-downwardly menu:
A few more merging options available in Excel

Merge Beyond - combine the selected cells in each row individually:
Merge Across - combine the selected cells in each row individually

Merge Cells - join the selected cells into a single cell without centering the text:
Merge Cells - join the selected cells into a single cell without centering the text

Tip. To modify the text alignment after merging, just select the merged cell and click the desired alignment in the Alignment group on the Domicile tab.

Excel's merging features - limitations and specificities

When using Excel's congenital-in features to combine cells, at that place are a few things to proceed in mind:

Make sure that all the data y'all desire to include in a merged cell is entered in the left-near cell of the selected range because but the content of the upper-left jail cell volition survive later on merging, information in all other cells volition be deleted. If y'all are looking to combine two or more than cells with data in them, bank check out How to merge cells without losing data.
If the Merge and Center button is greyed out, nearly likely the selected cells are in Edit manner. Press the Enter key to abolish the Edit mode, and and so effort to merge cells.
None of standard Excel merging options works for the cells inside an Excel table. Yous accept to convert a tabular array to a usual range first (right click the table and select Table > Convert to Range from the context menu), and so combine the cells.
It's not possible to sort a range containing both merged and unmerged cells.

How to merge cells in Excel without losing data

As already mentioned, the standard Excel merge features go on the content of the top-left cell just. And although Microsoft has made quite a lot of improvements in the recent versions of Excel, the Merge Cells functionality seems to have slipped out of their attention and this disquisitional limitation persists even in Excel 2013 and Excel 2016. Well, where there is no obvious way, there is a workaround :)

Method 1. Combine cells inside i column (Justify feature)

This is a quick and easy method of merging cells keeping all their content. Notwithstanding, it requires that all the cells to exist merged reside in one area in one column.

Select all the cells you lot desire to combine.
Make the column broad enough to fit the contents of all cells.
On the Domicile tab, in the Editing group, click Fill > Justify. This will move the contents of the selected cells to the top-most cell.
Click Merge and Center or Merge Cells, depending on whether you want the merged text to be centered or not.

If the combined values spread across two or more rows, make the column a bit wider and repeat the procedure.

This merging technique is easy to utilise, withal it does have a number of limitations:

Using Justify y'all tin can just join cells in a single column.
It works for text only, numerical values or formulas cannot be merged in this way.
It does non work if there are whatever blank cells in between the cells to be merged.

Method 2. Merge multiple cells with data in any range (Merge Cells add-in)

To be able to merge 2 or more cells in Excel without losing data and without extra "tricks", we created a special tool - Merge Cells for Excel.
The Merge Cells add-in for Excel

Using this add-in, you tin can chop-chop combine multiple cells containing any information types including text, numbers, dates and special symbols. As well, you can separate the values with whatsoever delimiter of your choosing such as a comma, space, slash or line break.

To bring together cells exactly the mode you want them, configure the post-obit options:

Cull Cells into one under "What to merge".
Select the delimiter under "Carve up values with".
Specify the cell where you lot want to place the event: peak-left, elevation-right, bottom-left or bottom-correct.
Make sure the Merge all areas in the option choice is selected. If this box is not checked, the add-in will work similar the Excel CONCATENATE function, i.e. combine the values without merging the cells.

Combine two or more cells without losing data with the Merge Cells add-in

Apart from joining all cells in the selected range, this tool can as well merge rows and combine columns, you lot just have to select the respective option in the "What to merge" drop-down list.
Merging rows or columns in Excel

To requite the Merge Cells add together-in a try, you lot are welcome to download the evaluation version that works with Excel 2016, 2013, 2010, 2007 and 2003.

Method three. Utilise the CONCATENATE part to combine two or multiple cells

Users who feel more comfortable with Excel formulas, may like this fashion to combine cells in Excel. Y'all tin employ the CONCATENATE office or Excel & operator to join the cells' values showtime, and so merge the cells if needed. The detailed steps follow below.

Supposing you want to combine two cells in your Excel canvass, A2 and B2, and both cells have information in them. Non to lose the value in the 2nd jail cell during merging, concatenate the 2 cells by using either of the following formulas:

=CONCATENATE(A2,", ",B2)

=A2&", "&B2

Use the CONCATENATE function to combine the cells' values

The formula, still, inserts the concatenated values in another cell. If you lot do need to merge 2 cells with the original data, A2 and B2 in this example, and so a few extra steps are required:

Copy the cell with the CONCATENATE formula (D2).
Paste the copied value in the top-left jail cell of the range you want to merge (A2). To do this, right click the cell and select Paste Special > Values from the context menu.
Select the cells that you desire to join (A2 and B2) and click Merge and Middle.

In a like manner, you can merge multiple cells in Excel, the CONCATENATE formula will be just a little bit longer in this instance. An advantage of this approach is that y'all can divide values with different delimiters within a unmarried formula, for example:

=CONCATENATE(A2, ": ", B2, ", ", C2)
Combine the values of multiple cells using the CONCATENATE function.

You can observe more formula examples in the following tutorial - CONCATENATE in Excel: combine text strings, cells and columns.

Shortcut for merging cells in Excel

If yous merge cells in your Excel worksheets on a regular footing, you may notice useful the following Merge Cells shortcut.

Select the cells you want to merge.
Press the Alt key that provides access the commands on the Excel ribbon and hold it until an overlay appears.
Striking H to select the Home tab.
Press One thousand to switch to Merge & Center.
Printing one of the following keys:
- C to merge and middle the selected cells
- A to merge cells in each private row
- Thousand to merge cells without centering

Shortcut to merge cells in Excel

At kickoff sight, the merge shortcut seems a bit long-winded, but with a picayune do you may notice this manner to combine cells faster than clicking the Merge and Center button with the mouse.

How to quickly observe merged cells

To find merged cells in your Excel sail, perform the following steps:

Press Ctrl + F to open the Detect and Replace dialog, or click Find & Select > Discover.
On the Find tab, click Options > Format.
On the Alignment tab, select the Merge cells box under Text command, and click OK.
Finally, click either Find Next to select the next merged jail cell, or Find All to find all merged cells on the sail. If you choose the latter, Microsoft Excel will display a list of all establish merged cells and let you lot navigate between them by selecting 1 of the merged cells in this list:

How to unmerge cells in Excel

If you changed your mind immediately after merging cells, you can rapidly unmerge them by pressing the shortcut Ctrl + Z or clicking the Disengage button on the Quick Access Toolbar.

To split the previously merged prison cell, select that cell and click Merge & Center, or click the little pointer next to Merge & Center, and select Unmerge Cells:

Unmerging cells in Excel

Afterwards unmerging the cells, the entire contents volition appear in the top-left prison cell.

Alternatives to merging cells in Excel

Information technology goes without saying that merged cells can help present the information in your Excel worksheets in a meliorate and more than meaningful way… merely they spawn numerous side-effects that you lot may not be even enlightened of. Here are just a few examples:

You tin can't sort a column with merged cells.
Neither AutoFill nor Fill Flash feature works if a range of cells to be filled contains merged cells.
Yous cannot plow a range containing at to the lowest degree ane merged jail cell into a total-fledged Excel table, let alone a pivot table.

And so, my advice would be to think twice before merging cells in Excel and practise this only when really needed for presentation or similar purposes, e.g. to center the table title beyond the table.

If you want to combine cells somewhere in the middle of your Excel sheet, you may consider using the Heart Across Option characteristic as an alternative:

Select the cells that you'd like to bring together, B4 and C4 in this example.
Press Ctrl + one to open the Format Cells
Switch to the Alignment tab and select the Center Beyond Option choice from the Horizontal drop-down list, and and then click OK.

Center Across Selection instead of merging cells

In terms of look, the result is indistinguishable from the merged cell:
The Center Across Selection feature in action

To prove that we did not really merge two cells, we tin select each one individually:
The text is displayed across two cells, but the cells are not merged.

This is how you can combine ii cells in Excel or merge multiple cells without losing data. Hopefully, this information has proved useful for your 24-hour interval-to-day tasks. I thank you for reading and hope to see on our blog next week.