How to merge rows in Excel without losing data

The tutorial shows how to safely merge rows in Excel in 4 different ways: merge multiple rows without losing data, combine duplicate rows, repeatedly merge blocks of rows, and copy matching rows from another table based on one or more common columns.

Merging rows in Excel is one of the most common tasks that all of us need to perform every now and then. The problem is that Microsoft Excel does not provide a reliable tool to do this. For example, if you try to combine two or more rows using the built-in Merge & Center button, you will end up with the following error message:

"The selection contains multiple data values. Merging into one cell will keep the upper-left most data only."
The selection contains multiple data values. Merging into one cell will keep the upper-left most data only.

Clicking OK will merge the cells but only keep the value of the first cell, all other data will be gone. So, obviously we need a better solution. This article describes several methods that will let you merge multiple rows in Excel without losing any data.

How to merge rows in Excel without losing data

The task: you have a database where each row contains certain details such as product name, product key, customer name and so on. What we want is to combine all the rows related to a particular order like shown below:
Multiple rows to be merged into one.

There are two ways to achieve the desired result:

Merge multiple rows using formulas

To joint the values from several cells into one, you can use either the CONCATENATE function or concatenation operator (&). In Excel 2016 and higher, you can also use the CONCAT function. Any way, you supply cells as references and type the desired delimiters in-between.

Merge rows and separate the values with comma and space:

=CONCATENATE(A1,", ",A2,", ",A3)

=A1&", "&A2&", "&A3

Merge rows with spaces between the data:

=CONCATENATE(A1," ",A2," ",A3)

=A1&" "&A2&" "&A3

Combine rows and separate the values with commas without spaces:

=CONCATENATE(A1,A2,A3)

=A1&","&A2&","&A3

In practice, you may often need to concatenate more cells, so your real-life formula is likely to be a bit longer:

=CONCATENATE(A1,", ",A2,", ",A3,", ",A4,", ",A5,", ",A6,", ",A7,", ",A8)

CONCATINATE formula to combine values from multiple rows into a single cell.

Now you have several rows of data merged into one row. But your combined rows are formulas. To convert them to values, use the Paste Special feature as described in How to replace formulas with their values in Excel.

Combine rows in Excel with Merge Cells add-in

The Merge Cells add-in is a multi-purpose tool for joining cells in Excel that can merge individual cells as well as entire rows or columns. And most importantly, this tool keeps all the data even if the selection contains multiple values.

To merge two or more rows into one, here's what you need to do:

  1. Select the range of cells where you want to merge rows.
  2. Go to the Ablebits Data tab > Merge group, click the Merge Cells arrow, and then click Merge Rows into One.
    Merge multiple rows into one.
  3. This will open the Merge Cells dialog box with the preselected settings that work fine in most cases. In this example, we only change the separator from the default space to line break, as shown in the screenshot below:
    Specify how you want to merge rows and choose the delimiter.
  4. Click the Merge button and observe the perfectly merged rows of data separated with line breaks:
    Merged rows separated with line breaks

How to combine duplicate rows into one (keeping unique values only)

The task: you have some Excel database with a few thousand entries. The values in one column are essentially the same while data in other columns are different. Your goal is to combine data from duplicate rows based on a certain column, making a comma separated list. Additionally, you may want to merge unique values only, omitting duplicates and skipping empty cells.

The screenshot below shows what we are trying to achieve.
Combine data from duplicate rows into one row.

The prospect of finding and merging duplicate rows manually is definitely something you'd want to avoid. Meet the Merge Duplicates add-in that turns this time-consuming and cumbersome chore into a quick 4-steps process.

  1. Select the duplicate rows you want to merge and run the Merge Duplicates wizard by clicking its button on the ribbon.
    Run the Merge Duplicates tool.
  2. Make sure your table is selected correctly and click Next. It is wise to keep the Create a backup copy option checked, especially if you are using the add-in for the first time.
    Run the Merge Duplicates wizard.
  3. Select the key column to check for duplicates. In this example, we select the Customer column because we want to combine rows based on customer name.

    If you want to skip empty cells, be sure to select this option and click Next.
    Select the key column by which you want to combine duplicate rows.

  4. Choose the columns to merge. In this step, you select the columns whose data you want to combine data and specify the delimiter: semicolon, comma, space, line break, etc.

    Two additional options in the upper part of the window let you:

    • Delete duplicate values while combining the rows
    • Skip empty cells

    When done, click the Finish button.
    Select the columns whose data you want to combine.

In a moment, all the data from duplicate rows are merged into one row:
Data from duplicate rows are merged into one row

How to repeatedly merge blocks of rows into one row

The task: you have an Excel file with information about the recent orders and each order takes 3 lines: product name, customer name and date of purchase. You would like to merge every three rows into one, i.e. repeatedly merge the blocks of three rows.

The following image show what we are looking for:
Repeatedly merge blocks of three rows in Excel.

If there are only few entries to be combined, you can select each 3 rows and merge each block individually using the Merge Cells add-in. But if your worksheet contains hundreds or thousands of records, you will need a faster way:

  1. Add a helper column to your worksheet, column C in our example. Let's name it BlockID, or whatever name you like.
  2. Insert the following formula in C2 and then copy it down the column by dragging the fill handle:

    =INT((ROW(C2)-2)/3)

    Where:

    • C2 is the topmost cell in which you enter the formula
    • 2 is the row where the data starts
    • 3 is the number of rows to be combined in each block

    This formula adds a unique number to each block of rows, as shown in the screenshot:
    A unique number is added to each block of rows to be merged.
    How this formula works: The ROW function extracts the row number of the formula cell, from which you subtract the number of the row where your data start, so that the formula starts counting from zero. For example, our data start in the 2nd row, so we subtract 2. If your data start, say, in row 5, then you will have ROW(C5)-5. After that, you divide the above equation by the number of rows to be merged and use the INT function to round the result down to the nearest integer.

  3. Well, you've done the main part of the work. Now you just need to merge the rows based on the BlockID For this, we will be using the already familiar Merge Duplicates wizard that we utilized for combining duplicate rows:
    • In step 2, choose BlockID as the key column.
    • In step 3, select all the columns you want to merge and pick line break as the delimiter.

    Merging blocks of rows in Excel.
    In a moment, you will have the desired result:
    Every 3 rows are merged into one.

  4. Delete the Block ID column since you don't need it any longer and you are done! A funny thing is that we've needed 4 steps again, like in the two previous examples :)

How to merge matching rows from 2 Excel tables without copying / pasting

Task: you have two tables with a common column(s) and you need to merge matching rows from those two tables. The tables may be located in the same sheet, in two different spreadsheets or in two different workbooks.

For example, we have sales reports for January and February in two different worksheets and want to combine them into one. Mind you, each table may have a different number of rows and different order of products, therefore simple copy/pasting won't work.
Merge matching rows from two tables.

In this case, the Merge Two Tables add-in will work a treat:

  1. Select any cell in your main table and click the Merge Two Tables button on the Ablebits Data tab, in the Merge group:
    Run the Merge Two Tables tool.
    This will run the add-in with your main table preselected, so in the first step of the wizard you simply click Next.
  2. Select the second table, i.e. the lookup table containing the matching rows.
    Select the second table containing the matching rows.
  3. Choose one or more column columns that exist in both tables. The key columns should contain only unique values, like Product ID in our example.
    Choose one or more key columns.
  4. Optionally, select the columns to update in the main table. In our case, there are no such columns, so we just click Next.
  5. Choose the columns to add to the main table, Feb sales in our case.
    Choose the matching columns to add to the main table.
  6. In the final step, you can select additional options depending on how exactly you want to merge data, and click the Finish button. The screenshot below shows the default settings, that work just fine for us:
    Select additional options to merge your tables.

Allow the add-in a few seconds for processing and review the result:
Matching rows from two tables are merged.

How can I get these merging tools for Excel?

All of the add-ins discussed in this tutorial, plus 70+ other time-saving tools, are included in our Ultimate Suite for Excel. The add-ins work with all versions of Excel 2019, Excel 2016, Excel 2013, Excel 2010 and Excel 2007.

Hopefully, you can now merge rows in your Excel sheets exactly the way you want them. If you have not found a solution for your specific task, just leave a comment and we will try to figure out a way together. Thank you for reading!

Available downloads

Ultimate Suite 14-day fully-functional version (.exe file)

Table of contents

118 comments

  1. Hai Irina,
    can u please help me?
    I have alpbhabets in column A and Assigned values on column B.If I merged some albhabetic letters in C.I need to display the corresponding mergerd values in column D.Is this Poosible?If Yes How?
    A B C D
    a 1 ac 13 ..................?
    b 2
    c 3
    Thank you

  2. Is there a function in Excel which allows a user simply combine two sheets finding the unique data identifier in both sheets and automatically add the necessary number of additional columns so that all of the data in both sheets would be displayed on the line where the unique identifier is found on each sheet? Seems like there would be a function in Excel to do this easily and automatically without entering any formula.

  3. Irina,
    I have two rows, 3 columns with Col A Name, Col B expiration date and Col C email address.
    Row 1has the name and email address
    Row 2 has the name and expiration date

    I need to merge, combine, sort; to obtain the name, expiration date and email address into one row of data
    Example:
    row 1 Fred fred@fred
    row 2 Fred 12/1/2017

    Need row Fred 12/1/2017 fred@fred

  4. hello dear madam
    i have a question
    i have a column in excel with many name
    and i want to know that how can i marge the same name in column

    thanks

  5. I have a list of contacts to merge. Some rows are for the same person, and have different data than the other rows. One may have an email address, the other the phone number. Sometimes both rows have the same data.

    The prior comments are all interesting, but did not have this answer.

    Is there a way to merge the rows and retain the best data? I would think I need a VBA macro which allows a dialog box for me to choose the better of the two data points if there is competing data for a column.

    This may not be a strictly Excel solution, but do you have any advice for this very common scenario?

    Thank you

  6. If this add-in actually does what it says, I'll most probably purchase it, but I tried the trial version to combine rows and delete duplicates in them but it did not work. I went exactly according to the tutorial above but still it didn't work. After pressing finish, a message comes which says 0 Rows combined, no duplicates found, I don't know what is the problem. Can someone help me with it?

    • Hello Vikram,
      I'm sorry to hear that. Would it be possible for you to describe your task and send a sample spreadsheet to support@ablebits.com? Please also include a link to the post and your comment number. We'll look into this issue.

  7. how can I have Ablebits in my Excel?

  8. We need to combine the data in excel as follows:

    Original Data Expected Data
    Name value Name value Combine value for particular Row value
    aaaaa 1701 aaaaa 1701 1701,1668,1332,1474
    aaaaa 1668 aaaaa 1668 1701,1668,1332,1474
    aaaaa 1332 aaaaa 1332 1701,1668,1332,1474
    aaaaa 1474 aaaaa 1474 1701,1668,1332,1474
    bbbb 1224 bbbb 1224 1224,1758,1512,1812,1382,1234
    bbbb 1758 bbbb 1758 1224,1758,1512,1812,1382,1234
    bbbb 1512 bbbb 1512 1224,1758,1512,1812,1382,1234
    bbbb 1812 bbbb 1812 1224,1758,1512,1812,1382,1234
    bbbb 1382 bbbb 1382 1224,1758,1512,1812,1382,1234
    bbbb 1234 bbbb 1234 1224,1758,1512,1812,1382,1234
    dddd 1641 dddd 1641 1641,1537,1589,1216,1703,1277,1197,1894,1847
    dddd 1537 dddd 1537 1641,1537,1589,1216,1703,1277,1197,1894,1847
    dddd 1589 dddd 1589 1641,1537,1589,1216,1703,1277,1197,1894,1847
    dddd 1216 dddd 1216 1641,1537,1589,1216,1703,1277,1197,1894,1847
    dddd 1703 dddd 1703 1641,1537,1589,1216,1703,1277,1197,1894,1847
    dddd 1277 dddd 1277 1641,1537,1589,1216,1703,1277,1197,1894,1847
    dddd 1197 dddd 1197 1641,1537,1589,1216,1703,1277,1197,1894,1847
    dddd 1894 dddd 1894 1641,1537,1589,1216,1703,1277,1197,1894,1847
    dddd 1847 dddd 1847 1641,1537,1589,1216,1703,1277,1197,1894,1847

  9. Hi I am interesting in doing exactly the example given in "Merge matching rows from 2 Excel worksheets without copying / pasting" (https://www.ablebits.com/office-addins-blog/merge-rows-excel/#merge-matching-rows). to be brief: Product, Jan sales on 1 sheet and Product, Feb sales on another sheet.
    I download the "merge two tables" function but I do not ended up with Product, Jan sales, Feb sales as described. Step4 is actually different. So I am wondering if you guys have updated this function and more important How to do the example because it is exactly what I am looking for?
    Thanks a lot for any help

    • Hello Emmanuelle,

      Thank you for contacting us.

      You are right, we slightly changed the add-in, though it has the same functionality. The new version lets you choose the columns to update on step 4, and the columns to add on step 5. If you don't want to update any columns and your task is to add "Feb sales" to your main table, do not select anything on step 4 and click Next:
      Step 4 lets you select columns to update

      Once you get to step 5, select Feb Sales:
      Select the columns to add

      Choose the necessary additional options on the last step and click Finish to get the desired result.

  10. Hi :)
    I've read the part "Repeatedly merge blocks of rows into one row"
    How can you make it work if you don't have the first row in your example?
    So if you don't have this first row:
    | Order no. | Order details | BlockID |

    Thanks
    Adam

    • !!!!! And I have only one column which I'd like to be merged this way: Merging 1-18, Merging 19-36, Merging 37-54. (and do not delete the text: "blablabla".

      So I'd like to merge every 18 rows. (and do not delete the text: "blablabla".

      From this:

      _________________
      1.___blablabla___
      2._______________
      3._______________
      4._______________
      5._______________
      6._______________
      7._______________
      8._______________
      9._______________
      10.______________
      11.______________
      12.______________
      13.______________
      14.______________
      15.______________
      16.______________
      17.______________
      18.______________
      19.__blablabla___
      20.______________
      21.______________
      22.______________
      23.______________
      24.______________
      25.______________
      26.______________
      27.______________
      28.______________
      29.______________
      30.______________
      31.______________
      32.______________
      33.______________
      34.______________
      35.______________
      36.______________
      37.__blablabla___
      38.______________
      39.______________
      40.______________
      41.______________
      42.______________
      43.______________
      44.______________
      45.______________
      46.______________
      47.______________
      48.______________
      49.______________
      50.______________
      51.______________
      52.______________
      53.______________
      54.______________

      It should be like this:

      _________________
      1. |
      2. |
      3. |
      4. |
      5. |
      6. |
      7. |
      8. |
      9. blablabla |
      10. |
      11. |
      12. |
      13. |
      14. |
      15. |
      16. |
      17. |
      18.______________|
      19. |
      20. |
      21. |
      22. |
      23. |
      24. |
      25. |
      26. |
      27. |
      28. blablabla |
      29. |
      30. |
      31. |
      32. |
      33. |
      34. |
      35. |
      36.______________|
      37. |
      38. |
      39. |
      40. |
      41. |
      42. |
      43. |
      44. |
      45. |
      46. blablabla |
      47. |
      48. |
      49. |
      50. |
      51. |
      52. |
      53. |
      54.______________|
      55. |
      56. |
      57. |

      and so on...

      • Hello Adam,
        Thank you for sending the example to us.
        Since formulas work with values, you can merge every n cells only with the help of VBA. One of our developers wrote the code for you, please follow these steps:
        - Open your Excel file and select the column with the values you'd like to merge (column A in your sample file);
        - Press Alt+F11 on your keyboard;
        - Double-click the sheet name in the list and paste this code:

        Sub merge()
        Dim reg As Range
        Dim start As Range
        Dim startVal As String
        Dim size As Integer

        Set reg = Application.Selection
        size = reg.Cells.Count
        startVal = reg.Cells(size, 1).Value
        Set start = reg.Cells(size, 1)
        For i = size To 1 Step -1
        If startVal <> reg.Cells(i, 1).Value Then
        Application.ActiveSheet.Cells.Range(reg.Cells(i, 1), start).merge
        If i <> 1 Then
        Set start = reg.Cells(i - 1, 1)
        startVal = reg.Cells(i - 1, 1).Value
        End If
        End If
        Next
        End Sub

        - Click Run.

        This should do the trick.

  11. Hi,
    I want to merge 3 cells into 2. Please tell me if it is possible.

    Thank You

  12. nice

  13. Pls suggest how do I concatenate for 5000 cells ?

  14. I have 3 rows as AB0U-RFW-010-9012-01 AB0U-RFW-010-9012-02, AB0U-RFW-010-9012-03, AB0U-RFW-010-9012-04

    i want only one entry to show AB0U-RFW-010-9012-01~04 can you help how to getthis

  15. how i can change the data 03 or 4 row to one

  16. Hi plz help me for moving data to other file without loss

  17. Bonjour! I hope you will be able to help me! I have an Excel sheet containing column A, listing natural medicinal substances. Then columns B to AA have headers indicating the possible medicinal uses of said substances. There are 26 uses and I put "X"s in the column(s) that correspond to each substance's uses.
    To make a long story short, at first I had one excel spreadsheet for each use of my substances, and I now want to combine all the data in one larger spreadsheet. BUT, since each substance has multiple uses, I end up with, for example, 4 rows for "Tansy" (Ex. A 21, 22, 23, 24), and in each row, an X appears in the column corresponding to one use of the herb (Ex:B:21, C:22, J:23 and Y:24). What I would like to do is to combine the 4 Tansy rows into one (ex. row 21), where I would have B:32, C:21, J:21 and Y:21.
    I hope I managed to be clear, and I thank you very much in advance for any help you could provide in letting me know is your Wizards could solve my problem! IM

    • Hello Isabelle,
      It sounds like you can use Combine Rows Wizard for your task:
      - Select all your data on step 1 and choose to create a backup copy just in case;
      - Select column A with the substances on step 2;
      - Use the top check-box to select all columns with the uses on step 3, and select the options to "Delete duplicate values" and "Skip empty cells". As you have the same "X"s in all cells, it doesn't matter what delimiter you leave in the field.
      - Click Finish
      If you have any difficulties, please send a sample worksheet to support@ablebits.com, we'll do our best to assist you.

  18. Can anyone help me out on how to repeatedly merge blocks of uneven rows into one row.

    I have very large data with uneven blocks of row.

    I have something like the table below
    Name Reported Date+"" Summary*"" Notes""
    Istifanus 5/4/2015 8:08 2-IT Incident Report - Hardware - Dear Team,
    abcd
    efgh
    kjil
    Jane 5/4/2015 8:17 2-IT Incident Report - Hardware - Good morning,
    port
    Newman
    Anderson
    Caleb 5/4/2015 8:27 2-IT Incident Report - Hardware - Stevedores

    I want to merge the Notes into one cell against the other contents of the first row.

    Thank you for your help

  19. Dear All, Am trying to merge multiple rows in a particular column without affecting the contents of other columns.

    I'll appreciate if anyone have an idea on how to go about this

  20. Firstly, great article and very well explained!

    Secondly, I receive client databases in excel files. However, while we want to receive the data like this:

    Column 1 Column 2 Column 3 Column 4
    John Smith 10 Smith St John@gmail.com

    Instead we get the data like this:

    Column 1 Column 2 Column 3 Column 4
    John Smith 10 Smith St John@gmail.com Nothing Nothing Nothing

    or this:

    Column 1 Column 2 Column 3 Column 4
    John,Smith,10 Smith St,John@gmail.com Nothing Nothing Nothing

    Is there a way to write up a script or some way to reformat the data to get the format we want - which is the first example?

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)