by 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.
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:
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)
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:
- Select the range of cells where you want to merge rows.
- Go to the Ablebits Data tab > Merge group, click the Merge Cells arrow, and then click Merge Rows into One.
- 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:
- Click the Merge button and observe the perfectly merged rows of data 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.
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.
- Select the duplicate rows you want to merge and run the Merge Duplicates wizard by clicking its button on the ribbon.
- 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.
- 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.
- 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.
In a moment, all the 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:
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:
- Add a helper column to your worksheet, column C in our example. Let's name it BlockID, or whatever name you like.
- 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:
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. - 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.
In a moment, you will have the desired result:
- 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.
In this case, the Merge Two Tables add-in will work a treat:
- Select any cell in your main table and click the Merge Two Tables button on the Ablebits Data tab, in the Merge group:
This will run the add-in with your main table preselected, so in the first step of the wizard you simply click Next. - Select the second table, i.e. the lookup table containing the matching rows.
- 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.
- Optionally, select the columns to update in the main table. In our case, there are no such columns, so we just click Next.
- Choose the columns to add to the main table, Feb sales in our case.
- 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:
Allow the add-in a few seconds for processing and review the result:
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)
118 comments
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.
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.
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]
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.
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!
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!
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
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.
I HAVE BELOW DATA ,NEED TO COMBINED IN ONE ROW
03-Nov-2023 DI048061 Hegde & Hegde Pharmaceutica LLP 27AAHFH3649H1Z7 27-MAHARASHTRA 998551 2,165.84 RP1L2F 65.75 18.00 0.00 5.92 5.92
03-Nov-2023 DI048062 Hegde & Hegde Pharmaceutica LLP 27AAHFH3649H1Z7 27-MAHARASHTRA 998551 3,557.24 MP42FV 134.70 18.00 0.00 12.12 12.12
03-Nov-2023 DI048063 Hegde & Hegde Pharmaceutica LLP 27AAHFH3649H1Z7 27-MAHARASHTRA 998551 18,193.08 2283543167878 108.75 18.00 0.00 9.79 9.79
2283543167879 108.75 18.00 0.00 9.79 9.79
2283543167880 108.75 18.00 0.00 9.79 9.79
2283543167881 98.75 18.00 0.00 8.89 8.89
2283543167882 98.75 18.00 0.00 8.89 8.89
2283543167883 98.75 18.00 0.00 8.89 8.89
Have you tried the methods described in this blog post? If you are not satisfied, please let me know and I will try to help you.
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.
Hi! Depending on what you want to do, there may be different answers. Try using a pivot table to show data for any store for any period of time. You can also use the FILTER function to get sales data by store for a week. To simply calculate the sum of sales for the week, you can use the SUMIFS function.
I have this date in excel sheet more then 300 entry, i want only column b date merge two row in one row, how to do this please any suggestion.
Date Particulars
-----------------------------------------------------
03/04/2023 Surplus Margin Received
from SMTF
05/04/2023 Cash Margin Transferred
to SMTF
06/04/2023 Money PayoutS
06/04/2023 Surplus Margin Received
from SMTF
10/04/2023 BG DP BILL AMOUNT DEBITE
D FOR DP ID 120333001009
6354
10/04/2023 Surplus Margin Received
from SMTF
11/04/2023 Cash Margin Transferred
to SMTF
12/04/2023 BEING DP BILL AMT RECD F
ROM BROKING FOR DP CLIEN
T CODE N10205593 & BROK
CODE 10910089
12/04/2023 Cash Margin Transferred
to SMTF
13/04/2023 BEING DP BILL AMT RECD F
ROM BROKING FOR DP CLIEN
T CODE N10205593 & BROK
CODE 10910089
Hi! Have you tried the ways described in this blog post? If they don’t work for you, then please describe your task in detail, give an example of the desired result.
I have below data (Data Source) in sheet1.
C D E F G H I J
1 Depart Arrive Depart Arrive Airline Airline Total Duration Stopover
2 10:00 (BOM) 18:10 (DEL) 12:20 (DEL) + 1 05:30 (ICN) ABC AIR (AB 909) A2Z Air (2Z 668) 16h DEL5h 50m
3 11:45 (BOM) 23:00 (SIN) 19:50 (SIN) + 1 06:20 (ICN) RST Air (ST 406) A2Z AIR (2Z 752) 15h 05m SIN3h 10m
4 13:15 (BOM) 23:20 (KUL) 21:05 (KUL) + 1 06:50 (ICN) MNO Air (MO 275) KLN AIR (KN 672) 14h 05m KUL2h 15m
5 13:15 (BOM) 23:30 (KUL) 21:05 (KUL) + 1 07:10 (ICN) MNO Air (MO 275) MNO Air (MH 66) 14h 25m KUL2h 25m
In Sheet2 i have extracted data from Data Source File as below
A B C D E F G H I
1 Flt No Days of Ops FPW From To Dep Arr Transit Flying Time
2 AB 909 M-W-F-- 3 BOM DEL 10:00 12:20 5h 50m 16h
3 2Z 668 DEL ICN 18:10 05:30
Here's how i have extracted the data in sheet2
Column A Formula: MlD(Sheet1!G2,SEARCH("(",Sheet1!G2)+1,SEARCH(")",Sheet1!G2,SEARCH(",Sheet1!G2)+1)-SEARCH("(",Sheet1!G2)-1)
Column B Formula: =CONCATENATE(Sheet1!L2,Sheet1!M2,Sheet1!N2,Sheet1!O2,Sheet1!P2,Sheet1!Q2,Sheet1!R2,)
Column C Formula: =Sheet1!S2
Column D Formula =MlD(Sheet1!C2,8,3)
Column E Formula =MlD(Sheet1!E2,8,3)
Column F Formula: =LEFT(Sheet1!C2,5)
Column G Formula: =LEFT(Sheet1!E2,5)
Column H Formula: =RlGHT(Sheet1!J2,6)
Column I Formula: =RlGHT(Sheet1!I2,6)
My question is when i copy formula from 2nd and 3rd row in output file and paste in fourth row it gives result of 4th row data of data file i.e shhet1.
But I need the data of 3rd row of Data file i.e sheet1.
Is it possible to achieve this.
Regards/Irfan
Hi! Copying a formula changes the relative row and column references. I recommend reading this manual: Relative and absolute cell reference: why use $ in Excel formula.
Thanks Alexander for your reply
Kindly give me an example how to use reference in this case.
Again i would like to clarify when i copy formula from 2nd and 3rd row in sheet2 and paste in fourth and fifth row it gives result of 4th row data of data file i.e sheet1.
But I need the data of 3rd row of Data file i.e sheet1. (When I copy 2 & 3 row and paste in 4 & 5 row row reference should move only 1 row instead of two)
for your reference data as below for your understanding
I have below data (Data Source) in sheet1.
C | D | E | F | G | H | I | J
1 | Depart | Arrive | Depart | Arrive | Airline | Airline | Total Duration | Stop Over
2 | 10:00 (BOM) | 18:10 (DEL) |12:20 (DEL) + 1 | 05:30 (ICN) | ABC AIR (AB 909) |A2Z Air (2Z 668) | 16h | DEL5h 50m
3 |11:45 (BOM) | 23:00 (SIN) | 19:50 (SIN) + 1 | 06:20 (ICN) | RST Air (ST 406) | A2Z AIR (2Z 752) | 15h 05m | SIN3h 10m
4 | 13:15 (BOM) | 23:20 (KUL) | 21:05 (KUL) + 1 | 06:50 (ICN) | MNO Air (MO 275) | KLN AIR (KN 672) | 14h 05m | KUL2h 15m
5 | 13:15 (BOM) | 23:30 (KUL) |21:05 (KUL) + 1 | 07:10 (ICN) | MNO Air (MO 275) | MNO Air (MH 66) | 14h 25m | KUL2h 25m
In Sheet2 i have extracted data from Data Source File as below
A | B | C | D | E | F | G | H | I
1 | Flt No | Days of Ops | FPW | From | To | Dep | Arr | Transit | Flying Time
2 | AB 909 | M-W-F-- | 3 | BOM | DEL | 10:00 | 12:20 | 5h 50m | 16h
3 | 2Z 668 | | | DEL | ICN | 18:10 | 05:30
Here's how i have extracted the data in sheet2
Column A Formula: MlD(Sheet1!G2,SEARCH("(",Sheet1!G2)+1,SEARCH(")",Sheet1!G2,SEARCH(",Sheet1!G2)+1)-SEARCH("(",Sheet1!G2)-1)
Column B Formula: =CONCATENATE(Sheet1!L2,Sheet1!M2,Sheet1!N2,Sheet1!O2,Sheet1!P2,Sheet1!Q2,Sheet1!R2,)
Column C Formula: =Sheet1!S2
Column D Formula =MlD(Sheet1!C2,8,3)
Column E Formula =MlD(Sheet1!E2,8,3)
Column F Formula: =LEFT(Sheet1!C2,5)
Column G Formula: =LEFT(Sheet1!E2,5)
Column H Formula: =RlGHT(Sheet1!J2,6)
Column I Formula: =RlGHT(Sheet1!I2,6)
Hi! If you copy a formula from row 2 to row 4, it will reference row 4, not row 3 as you want. This is how copying works in Excel.