Combine data and merge cells by rows, columns, or into a single cell without losing data

Merging columns, rows or multiple cells in Excel has a number of approaches. ‘Merge and Center’ gives a table a neat and user-friendly look, but keeps the contents only of the top left cell, deleting the rest. Using "&" operator or CONCATENATE function helps join cells' contents into one string, but doesn't merge cells and can be time consuming.

The Combine Data add-in helps merge cells in large table without losing data:

Combine Data is one of the 20+ features within XLTools Add-in for Excel. Works in Excel 2019, 2016, 2013, 2010, desktop Office 365.

Download XLTools for Excel

• When you merge multiple cells, you create one larger merged cell in their place.
• When you combine multiple cells, you join, or concatenate contents of these cells into one text string.

XLTools lets you both merge cells and combine data at the same time – in other words, the add-in lets you merge cells without losing data.

Note: the combined text string uses formatted values, e.g. 10%, 12-MAR-2015, $40, etc. If some cells contain formulas or references, the string will use their computed result.

1. Select a range of adjacent cells to merge Click the Combine Data button on XLTools tab.
2. Select Into a single cell.
3. Set a suitable separator.
4. Check the box Merge cells after combining cells data.
5. If necessary, check additional options:

   • Skip empty cells if there are any in your range, to avoid blank values in the resulting text string.
   • Wrap text to wrap text into multiple lines.
6. Click OK Done. The range is merged into a single cell, while all values are combined in this cell.

1. Select a range Click the Combine Data button on XLTools tab.
2. Select By rows.
3. Set a suitable separator.
4. Check the box Merge cells after combining cells data.
5. If necessary, check additional options:

   • Skip empty cells if there are any in your range, to avoid blank values in the resulting text string.
   • Wrap text to wrap text into multiple lines.
6. Click OK Done. All cells are merged across rows, and all values are combined across rows.

1. Select a range Click the Combine Data button on XLTools tab.
2. Select By columns.
3. Set a suitable separator.
4. Check the box Merge cells after combining cells data.
5. If necessary, check additional options:

   • Skip empty cells if there are any in your range, to avoid blank values in the resulting text strings.
   • Wrap text to wrap text into multiple
6. Click OK Done. All cells within columns are merged, all values within columns are combined.

Sometimes you may need to assemble values from multiple cells into one cell, without actually merging cells. Essentially, you'd want to copy values from each cell in the range and assemble them into one text string in single cell. You can do this in seconds with XLTools add-in:

1. Select a range Click the Combine Data button on XLTools tab.
2. Select Into a single cell.
3. Set a suitable separator.
4. If necessary, check additional options:

   • Clear the contents of cells after combining to remove values from source cells. Compare the result on the screenshot with this option off and on.
   • Skip empty cells if there are any in your range, to avoid blank values in the resulting text string.
   • Wrap text to wrap text into multiple lines.
5. Click OK Done. All values are combined in a single cell.

In some cases you may need to join multiple columns into one, without actually merging cells. Essentially, you'd want to combine values from these columns across rows. You can easily do this with the add-in:

1. Select a range Click the Combine Data button on XLTools tab.
2. Select By rows.
3. Set a suitable separator.
4. If necessary, check additional options:

   • Clear the contents of cells after combining to remove values from source cells. Compare the result on the screenshot with this option off and on.
   • Skip empty cells if there are any in your range, to avoid blank values in the resulting text string.
   • Wrap text to wrap text into multiple lines.
5. Click OK Done. All values are combined across rows in the left-most cells.

Sometimes you may need to join multiple rows into one, without actually merging cells. Essentially, you'd want to combine values from these cells by columns. You can easily do this with the add-in:

1. Select a range Click the Combine Data button on XLTools tab.
2. Select By columns.
3. Set a suitable separator.
4. If necessary, check additional options:

   • Clear the contents of cells after combining to remove values from source cells. Compare the result on the screenshot with this option off and on.
   • Skip empty cells if there are any in your range, to avoid blank values in the resulting text string.
   • Wrap text to wrap text into multiple lines.
5. Click OK Done. All values are combined within columns in the top-most cells.

If your source data contains empty cells, it's best to disregard them when combining and merging cells. This helps avoid blank values in the combined text strings. Compare the result with the Skip empty cells option off and on:

XLTools Combine Data add-in allows you to set any delimiter between values in a combined text string:

• Comma,
• Comma and space,
• Semicolon,
• Semicolon and space,
• Space,
• New line,
• Or, select Other and type in any custom separator you want, e.g. vertical pipe line.

Suppose, you have multiple columns, and you want to combine cells using different separators. Simply break the task into steps:

• Step 1. Merge columns 'First Name' with 'Last Name':

  1. Select the columns Click Combine Data.
  2. Select By rows.
  3. Set space as a separator.
  4. Check boxes Merge cells…, Clear contents…, Skip empty cells.
  5. OK Done, first and last names are combined into strings like "John Smith".
• Step 2. Merge the result with the column 'Order ID':

  1. Select the columns Click Combine Data.
  2. Select By rows.
  3. Set Other separator and type ": order #".
  4. Check boxes Merge cells…, Clear contents…, Skip empty cells.
  5. OK Done, all data are combined into strings like "John Smith: order #123".