by Svetlana Cheusheva, updated on
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."
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.
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:
There are two ways to achieve the desired result:
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)
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.
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:
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.
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.
If you want to skip empty cells, be sure to select this option and click Next.
Two additional options in the upper part of the window let you:
When done, click the Finish button.
In a moment, all the data from duplicate rows are merged 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:
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:
=INT((ROW(C2)-2)/3)
Where:
This formula adds a unique number to each block of rows, as shown in the screenshot:
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.
In a moment, you will have the desired result:
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.
In this case, the Merge Two Tables add-in will work a treat:
Allow the add-in a few seconds for processing and review the result:
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!
Ultimate Suite 14-day fully-functional version (.exe file)
Table of contents