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
(Note: the video may not reflect the latest upgrades. Please use the guide below.)

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.

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.

How to merge cells into a single cell without losing data

  1. Select a range of adjacent cells to merge Click theCombine Databutton on XLTools tab.
  2. SelectInto a single cell.
  3. Set a suitable separator.
  4. Check the boxMerge cells after combining cells data.
  5. If necessary, check additional options:

    • Skip empty cellsif there are any in your range, to avoid blank values in the resulting text string.
    • Wrap textto 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.
How to merge cells into a single cell without losing data

How to merge cells across rows without losing data (or merge columns)

  1. Select a range Click theCombine Databutton on XLTools tab.
  2. SelectBy rows.
  3. Set a suitable separator.
  4. Check the boxMerge cells after combining cells data.
  5. If necessary, check additional options:

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

How to merge cells within columns without losing data (or merge rows)

  1. Select a range Click theCombine Databutton on XLTools tab.
  2. SelectBy columns.
  3. Set a suitable separator.
  4. Check the boxMerge cells after combining cells data.
  5. If necessary, check additional options:

    • Skip empty cellsif there are any in your range, to avoid blank values in the resulting text strings.
    • Wrap textto wrap text into multiple
  6. Click OK Done. All cells within columns are merged, all values within columns are combined.
How to merge Excel rows column by column

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:

  1. Select a range Click theCombine Databutton on XLTools tab.
  2. SelectInto a single cell.
  3. Set a suitable separator.
  4. If necessary, check additional options:

    • Clear the contents of cells after combiningto remove values from source cells. Compare the result on the screenshot with this option off and on.
    • Skip empty cellsif there are any in your range, to avoid blank values in the resulting text string.
    • Wrap textto wrap text into multiple lines.
  5. Click OK Done. All values are combined in a single cell.
How to combine data into 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:

  1. Select a range Click theCombine Databutton on XLTools tab.
  2. SelectBy rows.
  3. Set a suitable separator.
  4. If necessary, check additional options:

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

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:

  1. Select a range Click theCombine Databutton on XLTools tab.
  2. SelectBy columns.
  3. Set a suitable separator.
  4. If necessary, check additional options:

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

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 theSkip empty cellsoption off and on:

How to 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, selectOtherand 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':

    1. Select the columns ClickCombine Data.
    2. SelectBy rows.
    3. Set space as a separator.
    4. Check boxesMerge 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 ClickCombine Data.
    2. SelectBy rows.
    3. SetOtherseparator and type ": order #".
    4. Check boxesMerge cells…,Clear contents…,Skip empty cells.
    5. OK Done, all data are combined into strings like "John Smith: order #123".
How to combine cells data using different separators
Any questions or suggestions?
close window
How can we help?