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 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 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 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 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 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.

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 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".
Any questions or suggestions?
Keith RandHow do i combine multiple rows into one row: Col 1 Col2 Col 3 Col4 Col 5 aaa 1234 777 aaa 761 1237 2121 What I want is aaa 761 1237 777 2121 We clean mail lists at our not for profit and the person may have the same name but different mail lists may have different bits of information. We need to consolidate on one line with the option to get the last line of data if the cell above is not empty Your example combines cells, I need to combine rows not cells. Thank you for your help.
December 15, 2019 at 01:06
Maria BalobanovaThank you for contacting us. It’s not quite clear now which value is in which cell. Could you please send an example in an Excel table and the result to want to get? Briefly, XLTools does allow combining data by rows. But let’s make sure it’s the right tool for your task. We've sent you an email.
December 16, 2019 at 12:26
How can we help?