Analistable

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?

Row-merging tasks
You haveYou wantMethod
An address split over 4 rowsOne cell with the whole addressTEXTJOIN with line breaks
Short lines of text in one columnOne paragraphFill → Justify
Several rows per customerOne row per customer with totalsConsolidate (pivot, SUMIFS, Group By)
Two rows that describe the same recordOne complete rowTake 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.

Address lines merged into one cell
CellValue
A212 Rue Victor Hugo
A3Apt 3
A469002 Lyon
A5France
B2 =TEXTJOIN(CHAR(10),TRUE,A2:A5)12 Rue Victor Hugo ↵ Apt 3 ↵ 69002 Lyon ↵ France

Fill → Justify for text blocks

  1. Make the column wide enough to hold the combined text.
  2. Select the cells to join (one column, text only).
  3. 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))
Source rows
CustomerOrderAmount
Bakery Lune1001120
Hart & Co100280
Bakery Lune100460
Nordic Supply1005200
Hart & Co100740
Bakery Lune100930
Merged rows
CustomerTotalOrders
Bakery Lune2101001, 1004, 1009
Hart & Co1201002, 1007
Nordic Supply2001005

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

When merged rows look wrong
SymptomCauseFix
Line breaks don't showWrap Text is offTurn on Home → Wrap Text and widen the row
#VALUE! from TEXTJOINThe result is longer than 32,767 charactersJoin fewer rows or shorter values
A customer appears twiceTrailing spaces or different capitals in the keyClean the key with TRIM before grouping
Fill → Justify does nothingCells contain numbers or formulasPaste as values and convert to text first
Merged text disappears when rows are deletedThe cell still holds a formulaPaste 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.

Related guides