How do I merge two cells and keep all data?
The tutorial demonstrates different techniques to quickly merge two cells in Excel and combine multiple cells row by row or column by column without losing data in Excel 365, Excel 2021, 2019, 2016, 2013, 2010 and lower. Show
In your Excel worksheets, you may often need to merge two or more cells into one large cell. For example, you may want to combine several cells for a better data presentation or structure. In other cases, there may be too much content to be displayed in one cell, and you decide to merge it with adjacent blank cells. Whatever the reason, combining cells in Excel is not as straightforward as it may seem. If at least two cells you are trying to join contain data, the standard Excel Merge Cells feature will only keep the upper-left cell value and discard values in other cells. But is there a way to merge cells in Excel without losing data? Of course there is. And further on in this tutorial, you will find a few solutions that work in all versions of Excel 2016, Excel 2013, Excel 2010 and lower. Combine cells using Excel's Merge and Center featureThe fastest and easiest way to combine two or more cells in Excel is to use the built-in Merge and Center option. The whole process takes only 2 quick steps:
In this example, we have a list of fruits in cell A1 and we want to merge it with a couple of empty cells to the right (B2 and C2) to create a large cell that fits the entire list. Once you click Merge and Center,
the selected cells will be combined into one cell and the text is centered like in the following screenshot: Other merging options in ExcelTo access a couple more merge options provided by Excel, click the little drop-down arrow next to the Merge & Center button and
choose the option you want from the drop-down menu: Merge Across - combine the selected cells in each row individually: Merge Cells - join the selected cells into a single cell without centering the text: Tip. To change the text alignment after merging, simply select the merged cell and click the desired alignment in the Alignment group on the Home tab. Excel's merging features - limitations and specificitiesWhen using Excel's built-in features to combine cells, there are a few things to keep in mind:
How to merge cells in Excel without losing dataAs already mentioned, the standard Excel merge features keep the content of the top-left cell only. 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 critical limitation persists even in Excel 2013 and Excel 2016. Well, where there is no obvious way, there is a workaround :) Method 1. Combine cells within one column (Justify feature)This is a quick and easy method of merging cells keeping all their content. However, it requires that all the cells to be merged reside in one area in one column.
If the combined values spread across two or more rows, make the column a bit wider and repeat the process. This merging technique is easy to use, however it does have a number of limitations:
Method 2. Merge multiple cells with data in any range (Merge Cells add-in)To be able to merge two or more cells in Excel without losing
data and without extra "tricks", we created a special tool - Merge Cells for Excel. Using this add-in, you can quickly combine multiple cells containing any data types including text, numbers, dates and special symbols. Also, you can separate the values with any delimiter of your choosing such as a comma, space, slash or line break. To join cells exactly the way you want them, configure the following options:
Apart from joining all cells in the selected range, this tool can also merge rows and combine columns, you just have to select the corresponding option in the "What to merge"
drop-down list. To give the Merge Cells add-in a try, you are welcome to download the evaluation version for Excel 2016 - 365. Method 3. Use CONCATENATE or CONCAT function to combine two or multiple cellsUsers who feel more comfortable with Excel formulas, may like this way to combine cells in Excel. You can employ the CONCATENATE function or the & operator to join the cells' values first, and then merge the cells if needed. In Excel 2016 - Excel 365, you can also use the CONCAT function for the same purpose. The detailed steps follow below. Supposing you want to combine two cells in your Excel sheet, A2 and B2, and both cells have data in them. Not to lose the value in the second cell during merging, concatenate the two cells by using either of the following formulas:
The formula, however, inserts the concatenated values in another cell. If you do need to merge two cells with the original data, A2 and B2 in this example, then a few extra steps are required:
In a similar manner, you can merge multiple cells in Excel, the CONCATENATE formula will be just a little bit longer in this case. An advantage of this approach is that you can separate values with different delimiters within a single formula, for example:
You can find more formula examples in the following tutorials:
Shortcut for merging cells in ExcelIf you merge cells in your Excel worksheets on a regular basis, you may find useful the following Merge Cells shortcut.
At first sight, the merge shortcut seems a bit long-winded, but with a little practice you may find this way to combine cells faster than clicking the Merge and Center button with the mouse. How to quickly find merged cellsTo find merged cells in your Excel sheet, perform the following steps:
How to unmerge cells in ExcelIf you changed your mind immediately after merging cells, you can quickly unmerge them by pressing the shortcut Ctrl + Z or clicking the Undo button on the Quick Access Toolbar. To split the previously merged cell, select that cell and click Merge & Center, or click the little arrow next to Merge & Center, and select Unmerge Cells: After unmerging the cells, the entire contents will appear in the top-left cell. For more information on how to quickly unmerge cells in Excel, read this article. Alternatives to merging cells in ExcelIt goes without saying that merged cells can help present the information in your Excel worksheets in a better and more meaningful way… but they spawn numerous side-effects that you may not be even aware of. Here are just a few examples:
So, my advice would be to think twice before merging cells in Excel and do this only when really needed for presentation or similar purposes, e.g. to center the table title across the table. If you want to combine cells somewhere in the middle of your Excel sheet, you may consider using the Center Across Selection feature as an alternative:
In terms of look, the result is indistinguishable from the merged cell: To prove that we did not really merge two cells, we can select each one individually: This is how you can combine two cells in Excel or merge multiple cells without losing data. Hopefully, this information has proved useful for your day-to-day tasks. I thank you for reading and hope to see on our blog next week. You may also be interested inCan I merge cells in Excel and keep all data?With the Merge Cells add-in, you can combine data from several cells using any separator you like (e.g. space, comma, carriage return or line break). You can join values row by row, column by column or merge data from the selected cells into one without losing it.
How do you merge cells without losing data?How to merge cells in Excel without losing data. Select all the cells you want to combine.. Make the column wide enough to fit the contents of all cells.. On the Home tab, in the Editing group, click Fill > Justify. ... . Click Merge and Center or Merge Cells, depending on whether you want the merged text to be centered or not.. How do I merge two cells in Excel and keep both data?Merge Cells in Excel Without Losing the Data
If you don't want any separator, you can simply leave it out and use the formula =CONCATENATE(A1,B1). Alternatively, you can use any other separator such as comma or semi-colon. This result of the CONCATENATE function is in a different cell (in C1).
How do you merge cells but keep all data sheets?How to merge cells in Google Sheets without losing the data?. Choose what to combine: columns, rows, or cells.. Pick the delimiter to separate values.. Decide where to place the results.. Tick off the additional options.. Click Merge.. |