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 cells values and merge cells at the same time
- Combine cells by columns, by rows, or into a single cell
- Set any separator between combined values: space, pipe, new line, etc.
- Combine data using multiple different separators in one string
Before you begin, add the Combine Data tool to Excel
Combine Data is one of the 20+ features within XLTools Add-in for Excel. Works in Excel 2019, 2016, 2013, 2010, desktop Office 365.
Combine cells or merge cells in Excel – what is the difference
- 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.
How to merge cells into a single cell without losing data
- Select a range of adjacent cells to merge Click the Combine Data button on XLTools tab.
- Select Into a single cell.
- Set a suitable separator.
- Check the box Merge cells after combining cells data.
- 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.
- Click OK Done. The range is merged into a single cell, while all values are combined in this cell.
How to merge cells across rows without losing data (or merge columns)
- Select a range Click the Combine Data button on XLTools tab.
- Select By rows.
- Set a suitable separator.
- Check the box Merge cells after combining cells data.
- 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.
- Click OK Done. All cells are merged across rows, and all values are combined across rows.
How to merge cells within columns without losing data (or merge rows)
- Select a range Click the Combine Data button on XLTools tab.
- Select By columns.
- Set a suitable separator.
- Check the box Merge cells after combining cells data.
- 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
- Click OK Done. All cells within columns are merged, all values within columns are combined.
How to combine data into a single cell (without merging cells)
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:
- Select a range Click the Combine Data button on XLTools tab.
- Select Into a single cell.
- Set a suitable separator.
- 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.
- Click OK Done. All values are combined in a single cell.
How to combine Excel data across rows (or join multiple columns into one)
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:
- Select a range Click the Combine Data button on XLTools tab.
- Select By rows.
- Set a suitable separator.
- 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.
- Click OK Done. All values are combined across rows in the left-most cells.
How to combine Excel data within columns (or join multiple rows into one)
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:
- Select a range Click the Combine Data button on XLTools tab.
- Select By columns.
- Set a suitable separator.
- 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.
- Click OK Done. All values are combined within columns in the top-most cells.
Why skip empty cells when combining and merging cells
How to set a custom separator when combining and merging cells
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.
How to apply multiple different separators in one combined text string
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':
- Select the columns Click Combine Data.
- Select By rows.
- Set space as a separator.
- Check boxes Merge cells…, Clear contents…, Skip empty cells.
- OK Done, first and last names are combined into strings like "John Smith".
- Step 2. Merge the result with the column 'Order ID':
- Select the columns Click Combine Data.
- Select By rows.
- Set Other separator and type ": order #".
- Check boxes Merge cells…, Clear contents…, Skip empty cells.
- OK Done, all data are combined into strings like "John Smith: order #123".