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)

42 comments

  1. I have two rows of data with multiple columns. The data in row A has some of the same information in certain columns as in row B but there are blank columns in each where there is information in one but not the other column. I want to blend/merge all the columns into 1 row. The entire Excel has multiple sets of 2 rows that need to be done. I also have a column with different information in row A than in row B. I don't know of an easy way to put that one piece of data into a new column except to cut and paste.

    1. Hello Dana!
      Your question is not very clear because in Excel A and B are columns, not rows. If your phrase “merge all the columns into 1 row” means that you want to merge values from two columns, then try using this guide: How to merge two columns in Excel without losing data.
      I recommend paying attention to Merge cells tool. It can combine columns and save the result to the left or to the right column. The tool is included in the Ultimate Suite for Excel and can be used in a free trial to see how it works.
      If this is not what you wanted, describe the problem in more detail, provide me with an example of the source data and the expected result.

  2. Hi, I bought Ablebits, and used it for a merge which worked great IF I had rows with a common column factor like email.

    Here's the task I have now; it's an excel file exported out of Quickbooks.

    It only lists client name ONCE, (and the columns are blank to the right), then BELOW, "blanks" in the name column, name is not repeated.

    I want to end up with simply rows with "the client name", and the rows that had been below, "data from each row, each column", merged, in essence then having no blank rows. Purpose, so I can import into a CRM. Here's what the spreadsheet looks like:

    NAME (blank) (blank)
    (blank) [bought a house] [red]
    (blank) [bought a plane] [blue]
    Totals for NAME (blank) (Amount)
    New NAME Then same pattern repeated

    I need to merge, but since there is no data on the same row as NAME, then no repeating name in the rows below for "who it is", just data in the columns to the right... I'm having trouble seeing how to end up with simple rows, and "all data jammed into a cell" from the rows below the row which has the person's name (and that "all data merged" cell, ON the "name row", no blank rows... so it looks like a database in essence).

    If you can visualize an accounting report from Quickbooks that has 'items bought" in columns (then a row showing the summary total), then a new name, well that is what this spreadsheet is. THE PATTERN IS NOT CONSISTENT, re: "how many rows are below a name", how many items purchased let's say.

    I don't mind "all data in rows below the name row" being jammed together in one cell, as I'll be doing a keyword search function, it doesn't have to look nice.

    But how do I accomplish this (again I have Ablebits), so it ends up like this:

    Name1 [column with merge of the rows that had been below this name] [same, with rows merged]
    Name2 [column with merge of the rows that had been below this name] [same, with rows merged]
    Name3 [column with merge of the rows that had been below this name] [same, with rows merged]

    1. Hello Frank! If I understand correctly, you need to fill the blank cells with the name that is written above in the column. To do this, use the Fill Blank Cells tool. Fill Blank Cells automatically fills the blank cells in a column with the value from the cell above or below it.
      You can then merge rows with the same names in a column without losing information.
      I hope it’ll be helpful. If something is still unclear, please feel free to ask.

      1. THANK YOU Alexander!
        That is Exactly what I needed, and it worked perfectly.
        I had no idea there was a Fill Blank Cells tool.
        Thanks for the Fast Help, you guys are great!

  3. This tool was extremely easy to use and merged my list exactly how I needed it to be. Feels too good to be true, at a price of $0. Thank you Ablebits!

  4. Hi, I have the following data, that I would like to merge, but sum up the quantity. I would really appreciate your help please.

    Customer Project Name Reference Staff Quantity
    Red Window A2163 John 0.5
    Red Window A2163 John 0.5
    Red Window A2163 John 3.75
    Red Window A2163 Bruce 1
    Red Window A2163 Bruce 2.25
    Red Window A2163 James 3.75
    Red Door A2174 Amy 1
    Red Door A2174 Amy 0.75
    Red Door A2174 Amy 2.25
    Red Door A2174 Amy 0.5
    Red Door A2174 Bruce 1.5
    Red Door A2174 Bruce 2.25
    Red Door A2174 James 3
    Red Door A2174 James 6.25
    Blue Window A3009 Bruce 2.25
    Blue Window A3009 Bruce 3
    Blue Window A3009 John 0.75
    Blue Door A2946 Jane 4
    Blue Door A2946 Jane 2.25
    Blue Door A2946 Jane 1.75
    Blue Door A2946 Amy 6

    Just to clarify - If the customer, Project Name, Reference & staff are the same, then merge and total up the quantity.
    So the duplicate lines, would merge, with the quantity added together, the desired result would be as follows:
    Red Window A2163 John 4.75
    Red Window A2163 Bruce 3.25
    Red Window A2163 James 3.75
    Red Door A2174 Amy 4.5
    Red Door A2174 Bruce 3.75
    Red Door A2174 James 9.25
    Blue Window A3009 Bruce 5.25
    Blue Window A3009 John 0.75
    Blue Door A2946 Jane 8
    Blue Door A2946 Amy 6

    1. Hi! You can get the desired result in a couple of clicks without using formulas using Remove Duplicates tool. Specify that the duplicates are in the first four columns, and you want to summarise the fifth column. It is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and check how it works for free.
      Another way is to use subtotals in Excel. This article may be helpful: How to add, use and remove Subtotals in Excel.
      In addition, you can use a pivot table to get totals for multiple metrics. Read more: How to make and use Pivot Table in Excel.
      You can also calculate the sum of multiple criteria using the SUMIFS function. Look for the example formulas here: Excel SUMIFS and SUMIF with multiple criteria – formula examples.

  5. Goodday

    question; i have an excel spreadsheet with daily sales of three mths and nine stores ,i will like to place this document into one sheet for nine stores so i can be able to see what what the stores sold by week.

  7. I have the following group of data in excel:
    Location
    ID
    Name
    Grade
    Subject
    Cred Term
    Doc #
    Eff Beg
    Eff end

    Some ID's have multiple lines as they have multiple certifications. I would like to merge all certifications into one line by ID.

    I currently have Ablebits software.

    1. Hi!
      Use Ablebits Data - Merge Duplicates.
      Step 1. Select your table.
      Step 2. Choose key columns - select ID column
      Step 3. Choose columns with the values to merge - select all other cloumns or only some of the columns whose data needs to be merged
      You can also read the detailed instructions for the Merge Duplicates tool here: How to combine duplicate rows in Excel.
      I hope I answered your question. If something is still unclear, please feel free to ask.

  8. Hi!! Thank you for helping people find solutions! How do I merge duplicate rows (of customers) info into one row with the customers info in multiple columns?

    Like Customer one came into the store on dates (data in each row in separate columns) so each customer only has 1 row instead of 5 rows.

    My end goal is to have Customer 1 in one row with all of their visit dates in one row. Right now, there Visit dates are on their own row (but in different columns).

    Thx!!

    Kelley

  9. Hi,

    I have an excel sheet data dump available where there is following information :-
    Column A - Name of Organization
    Column B - License
    Column C - Address

    In some cases, the address in column C is in one row and in some cases multiple rows (for example, 4 or 5). And when address is in multiple rows, the name of the organization and license has been merged, meaning,

    Column A
    Cells A1 to A4 are merged

    Column B
    Cells B1 to B4 are merged

    Column C
    C1 - 1st line of address,
    C2 - 2nd line of address
    C3 - 3rd line...
    C4 - 4th line of address.

    Now manually using the merge cell by row helps with individually merging C1 to C4 cells. However, is there a way I can apply that logic on the entire excel sheet in one attempt ? Thank you for your help.

    Regards,
    Jai

  10. Hi. re: "How to combine duplicate rows into one (keeping unique values only)" section of this article, does Ablebits have a tool that will do the inverse/opposite of this? i.e. delimit the merged values and repeat/duplicate the respective key column value for each of the delimited values?

    1. To elaborate a little more, what I am trying to achieve is similar to what is described here:
      http s://stackoverflow.com/questions/44655553/vba-excel-with-thousands-of-rows-how-to-transpose-variable-length-columns-to

      But instead of a macro, I am wondering if Ablebits offers a tool that does the same?

      1. Hello!
        We have a tool that can solve your task in a couple of clicks - first you use the tool Merge Cells - Merge Columns Into One. You are merge the Property1-Property6 values in one column by separating them with a semicolon, for example. Then you use Split Text - Split by Characters - Split to Rows and get the desired result.
        It is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and check how it works for free.

  11. is there a short way l could merge rows with even gaps into one row. l am using a sheet with several line items which have different row gaps.

    1. Hello,

      the simplest and quickest way of combining the rows is using our add-ins: Merge Cells and Combine Rows Wizard.
      You can see how they work in the article above and you can also download and install their fully-functional versions to try them out.

  12. Dear Irina,

    I need to merge multiple rows in the same column with one another so that you have all text in one column, and row. Can you please help?

    Kind regards
    Jan Swart

    1. Dear Jan,

      we have just a perfect solution for you - Merge Cells Wizard. With its help, you'll be able to combine multiple cells into one cell/row/column and use any separator to your liking.

      You can download and install the trial version of the add-in by using this direct link.
      Please find the instructions on how to work with the add-in on its help page.

      Hope you'll find the add-in useful!

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

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

  15. Hi,
    Is there a way to use the combine rows and have the program actually write out all the columns instead of entering data in a column separated by semicolons or commas? I want the duplicate data actually expanded across columns:
    Sample
    A B 32 25 Y N
    A C 32 22 N Y
    Combined
    A B C 32 32 35 22 Y N N Y

    I love the program, it is fantastic but I need to expand the results and not sure how to do that.

    Thanks,

    Gene

    1. Hello Gene,

      If you have simple data (without commas), the following solution might work for you:

      1 Run Combine Rows Wizard to merge the data and choose a semicolon as the separator.

      2 Save the resulting worksheet as a .csv file. For more details, please see this article:
      https://www.ablebits.com/office-addins-blog/convert-excel-csv/

      3 Open the .csv file in Notepad and replace all semicolons with commas. Save the file.

      4 Open the .csv file in Excel, see here for full details:
      https://www.ablebits.com/office-addins-blog/convert-csv-excel/

  16. Hi there, how to I undo the merged cells?

    1. Hello Sumen,

      It depends on how the cells were merged. If you have just merged them, you can use the standard Excel Undo feature (Ctrl+Z). Our merging add-ins create a backup copy of your worksheet before merging the data. In other cases, I'm afraid, Undo is not possible.

  17. I am having the list as mentioned below and i want the result which is mentioned.
    please help by provinding the solution as mentioned in name coloum is change day by day i expecting the formula from where he automaticaly give me the result name of the customer which is present in name column.

    Name code amount
    Abc 123 2
    DEF 456 3
    GHF 458 4
    KLM 741 5
    NMR 351 6
    DEF 456 7
    KLM 741 8
    MNP 3425 9
    NMR 351 10
    MNP 3425 11
    JKL 7538 12
    Abc 123 13
    DEF 456 14
    GHF 458 15
    GHF 458 16
    DEF 456 17
    DEN 753 18

    I want the Result to be like mentioned below by using the formaulas

    Name Code Amount
    Abc 123 15
    GHF 458 35
    DEF 456 41
    KLM 741 13
    NMR 351 16
    MNP 3425 20
    JKL 7538 12
    DEN 753 18

    1. I believe the following solutions will work:

      Solution 1:
      - Sort your table by the Name column.
      - Go to Data > Outline and click on Subtotal.
      - Specify the following settings:
      At each change in: Name
      Use function: Sum
      Add subtotal to: Amount
      - Click OK and then press Number 2 in the upper left-hand corner of your worksheet to get the sum by name.
      For more information about using subtotals in Microsoft Excel, please see:
      https://www.ablebits.com/office-addins-blog/subtotal-excel-insert-use-remove/

      Solution 2:
      - Insert a pivot table (select your table and go to Insert > Tables > PivotTable).
      - Choose to insert the PivotTable report onto a new worksheet and click OK.
      - In the PivotTable Fields pane, drag and drop the Names column to the Rows section and the Amount column to the Values section.
      You will get the same result.

      Solution 3:
      - Copy your table to another worksheet
      - Go to Data > Remove duplicates to delete duplicated names.
      - Type the following formula in the Amount column:
      =SUMIF('V3 - source'!A2:A18,A2,'V3 - source'!C2:C18)
      Where 'V3 - source'!A2:A18 is the reference to the Names column of your source data, 'V3 - source'!C2:C18 – the reference to the Amount column.

      You can download the workbook with all 3 solutions using the below link:
      https://cdn.ablebits.com/_img-blog/_comments/jhon%20-%20119.235.57.75%20-%20sumif.xlsx

  18. I am having a 5000 customer in Column "A" and In column "A' most of the customer is repeated Now i want the formulas from which i will check the names of the customer which is present in this column.

    1. Hello John,

      There exist a number of solutions depending on what exactly result you are trying to achieve.

      If you want to highlight all duplicates in a column, please see this article:
      https://www.ablebits.com/office-addins-blog/how-to-highlight-duplicates-excel/

      To highlight duplicate entries between 2 columns, see the "Compare 2 columns for duplicates" example on this page:
      https://www.ablebits.com/office-addins-blog/excel-conditional-formatting-formulas/

      If you want to merge the duplicate rows into one, check out the Combine Duplicated Rows add-in:
      https://www.ablebits.com/excel-combine-rows/index.php

  19. Thank you so much for this!! I was pulling my hair out trying to merge address data into one cell so that it would be more usable in a Mail merge in Microsoft Office. You saved my day!!! Thank you, thank you, thank you!!!

  20. let's say I have 2 rows of the exact same data, except for ONE cell in each column.

    For an example, let's make the headers: a. Customer Name, b. Customer ID, c. Customer Address, and d. Amount Paid.

    Now rows 1 & 2 are the exact same customer, except in row 1 column d the dollar amount is $500, and in row 2 the dollar amount is $300.

    Is there a way to quickly consolidate into a single line, with the dollar amount in column d the sum of the previous entries? (i.e. $800)?

    1. Hello Harold,

      You can do this in 2 ways:

      1. Using Subtotal. Sort your table by Customer ID and then apply subtotal in this way: At each change in "Customer ID", use function "Sum", add subtotal to column "Amount Paid". Please see this article for more details: Using Subtotals in Microsoft Excel

      2. Using a pivot table.

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 :)