How to merge rows in Excel without losing data
By the Analistable team · Updated · 5 min read
Excel's Merge Cells keeps only the top value, so merge the contents instead. To put the text of several rows into one cell, use =TEXTJOIN(CHAR(10), TRUE, A2:A6) and turn on Wrap Text. For a column of short text lines, Home → Fill → Justify joins them into the top cell. If the rows share a key (the same customer on several rows), consolidate them with a pivot table, SUMIFS or Power Query Group By.
Part of our guide: How to consolidate data in Excel
What kind of row merge do you need?
| You have | You want | Method |
|---|---|---|
| An address split over 4 rows | One cell with the whole address | TEXTJOIN with line breaks |
| Short lines of text in one column | One paragraph | Fill → Justify |
| Several rows per customer | One row per customer with totals | Consolidate (pivot, SUMIFS, Group By) |
| Two rows that describe the same record | One complete row | Take the non-empty value per column |
Join several rows into one cell
=TEXTJOIN(CHAR(10), TRUE, A2:A5)CHAR(10) is a line break; turn on Home → Wrap Text to see the lines. Use ", " instead for a comma-separated list. TRUE skips empty cells. Then copy the cell and Paste Special → Values before deleting the original rows.
| Cell | Value |
|---|---|
| A2 | 12 Rue Victor Hugo |
| A3 | Apt 3 |
| A4 | 69002 Lyon |
| A5 | France |
| B2 =TEXTJOIN(CHAR(10),TRUE,A2:A5) | 12 Rue Victor Hugo ↵ Apt 3 ↵ 69002 Lyon ↵ France |
Fill → Justify for text blocks
- Make the column wide enough to hold the combined text.
- Select the cells to join (one column, text only).
- Choose Home → Fill → Justify.
Excel joins the text into as few cells as the column width allows, starting at the top. It's quick for notes or descriptions pasted across several rows. It works on text only (not numbers or formulas) and on up to 255 characters per cell.
Merge two partial rows into one complete row
When two rows describe the same record with different columns filled in, take the first non-blank value in each column. For rows 2 and 3:
=IF(B2<>"", B2, B3)For many records, Power Query does it per key: Group By the ID with All Rows, then for each column use List.First(List.RemoveNulls([Column])).
Consolidate rows that share a key
One row per customer with totals is a consolidation: use a pivot table, =SUMIFS() next to a =UNIQUE() list, or Power Query Group By. To keep the text from those rows too, use TEXTJOIN with FILTER. Full walkthrough: combining duplicate rows in Excel.
Merging rows *across sheets* (stacking) is a different task — see combining Excel sheets into one.
Worked example: one row per customer with their orders listed
Orders sits in A1:C7 with Customer, Order and Amount. You want one row per customer with the total and the order numbers in one cell. In Microsoft 365 or Excel 2021, put =UNIQUE(A2:A7) in E2, then:
F2: =SUMIFS($C$2:$C$7, $A$2:$A$7, E2)
G2: =TEXTJOIN(", ", TRUE, FILTER($B$2:$B$7, $A$2:$A$7=E2))| Customer | Order | Amount |
|---|---|---|
| Bakery Lune | 1001 | 120 |
| Hart & Co | 1002 | 80 |
| Bakery Lune | 1004 | 60 |
| Nordic Supply | 1005 | 200 |
| Hart & Co | 1007 | 40 |
| Bakery Lune | 1009 | 30 |
| Customer | Total | Orders |
|---|---|---|
| Bakery Lune | 210 | 1001, 1004, 1009 |
| Hart & Co | 120 | 1002, 1007 |
| Nordic Supply | 200 | 1005 |
Six rows became three, and the totals (210 + 120 + 200 = 530) match the sum of the Amount column, so nothing was lost.
Excel 2019 and older
UNIQUE and FILTER are missing in Excel 2019 and earlier, and TEXTJOIN only arrived in Excel 2019. In those versions, get the list of customers with Data → Remove Duplicates on a copy of the column, total with SUMIFS, and use Power Query for the text: Group By Customer with the All Rows operation, then add a custom column with Text.Combine(List.Transform([Rows][Order], Text.From), ", "). Power Query is also the best route when the merge has to be repeated on each month's export, because you only press Refresh.
Merging rows in Google Sheets
The same formulas work in Google Sheets: =TEXTJOIN(CHAR(10), TRUE, A2:A5) joins rows into one cell, and =TEXTJOIN(", ", TRUE, FILTER(B2:B7, A2:A7=E2)) lists the matches for one key. Google Sheets has no Fill → Justify. Combining text from matched rows with TEXTJOIN covers more variations.
Troubleshooting
| Symptom | Cause | Fix |
|---|---|---|
| Line breaks don't show | Wrap Text is off | Turn on Home → Wrap Text and widen the row |
| #VALUE! from TEXTJOIN | The result is longer than 32,767 characters | Join fewer rows or shorter values |
| A customer appears twice | Trailing spaces or different capitals in the key | Clean the key with TRIM before grouping |
| Fill → Justify does nothing | Cells contain numbers or formulas | Paste as values and convert to text first |
| Merged text disappears when rows are deleted | The cell still holds a formula | Paste Special → Values before deleting |
Check before you delete the original rows
- Count: the number of merged rows should equal
=COUNTA(UNIQUE(A2:A7)), the number of distinct keys. - Totals: the sum of the merged Total column should equal the sum of the original Amount column.
- Spot-check one key with several rows, such as Bakery Lune, by filtering the source and reading the values.
- Keep a copy of the original sheet until the merged version has been used once without problems.
If the rows come from two files rather than one sheet, stack them first; merging two spreadsheets and removing duplicates shows how.
When not to merge rows
If the rows will be analysed later — counted, filtered or pivoted — keep them as separate rows and summarise in a report instead. A cell holding "1001, 1004, 1009" is easy to read but hard to count or match against another file.
Frequently asked questions
- How do I merge two rows in Excel without losing data?
- Join their contents with a formula such as =TEXTJOIN(" ", TRUE, A2:A3) or =IF(B2<>"", B2, B3) per column, paste the result as values, then delete the extra row.
- Why does Merge Cells delete my data?
- Excel keeps only the upper-left value when merging cells. Combine the contents with a formula first.
- How do I combine rows with the same ID?
- Use a pivot table or SUMIFS for numbers, and TEXTJOIN with FILTER (or Power Query Group By) for text.