Merge Rows Keep Data? Excel & Sheets Explained

When you merge cells in Excel or Google Sheets, the default behavior keeps only the upper-left (or top-left) data and discards everything else. If you want to keep all the data from the merged rows, you must either combine it into one cell before merging, or use a formula to concatenate the values—there is no built-in “merge and keep all” button.

What Happens When You Merge Rows

Merging cells takes two or more adjacent cells and turns them into a single cell that spans the same area. In both Microsoft Excel and Google Sheets, the merge action applies to the formatting and the cell reference, but the underlying data is not automatically combined. The only value that survives is the one in the first cell of the selection—the top-left cell for a rectangular range, or the top cell for a vertical merge. All other values are deleted without warning.

For example, if you have “John” in A1 and “Smith” in A2, merging A1:A2 will leave only “John” in the merged cell. “Smith” is gone. This is a common source of data loss, especially when people merge rows to create a cleaner header or a combined label.

Why the Merge Feature Works This Way

The design is intentional. A merged cell is a single cell that happens to occupy multiple rows or columns. A cell can only hold one value, so the software must decide which of the original values to keep. Rather than asking for confirmation or trying to concatenate automatically, both Excel and Google Sheets simply keep the top-left value and discard the rest. This behavior is consistent with how spreadsheets handle other operations that collapse multiple cells into one—like deleting a row or pasting over a range.

There is also a technical reason: if merging automatically combined text, the result would be unpredictable. Would it add a space? A comma? What about numbers—should they be added or listed? The developers chose the simplest, most predictable behavior, leaving the combining step to the user.

How to Keep All Data When Merging Rows

There are three reliable methods to preserve all the data from multiple rows into a single merged cell. Each works in both Excel and Google Sheets.

• Use a formula to concatenate first: In a helper column, enter a formula like =A1&" "&A2 (Excel) or =A1&" "&A2 (Sheets) to join the values with a space. Then copy the result and paste it as a value, and finally merge the original cells. The merged cell will show the combined text.
• Use TEXTJOIN (Excel 2016+ and Google Sheets): =TEXTJOIN(" ",TRUE,A1:A10) joins a range with a delimiter (the space) and ignores empty cells. This is the cleanest method for more than two rows.
• Copy to a text editor and back: Copy the column of values, paste into Notepad or a text editor, replace line breaks with spaces, then copy that single line and paste it into the merged cell. This works when you don’t want to write a formula.

After using any of these, you can safely merge the original cells because the combined value is already in the top cell (or you paste it into the merged cell afterward).

Merge vs. Center Across Selection

If your goal is simply to make text appear centered across multiple rows without losing data, consider “Center Across Selection” instead of merging. In Excel, select the range, open Format Cells (Ctrl+1), go to the Alignment tab, and choose “Center Across Selection” from the Horizontal dropdown. In Google Sheets, the equivalent is not as direct, but you can use a similar effect by merging a single row and then adjusting row heights.

Center Across Selection does not merge the cells—it only changes the display. All data in every cell remains intact. This is the safest option for headers where you want the visual effect of merging without the data loss.

Common Problems and Mistakes

• Losing data without noticing: The warning dialog in Excel says “Merging cells keeps only the upper-left value and discards other values.” Many users click OK without reading it. Always read the prompt.
• Merging after data is already lost: If you’ve already merged and lost data, use Ctrl+Z (undo) immediately. If you’ve saved and closed the file, recovery is difficult without a backup or version history.
• Sorting and filtering issues: Merged cells break sorting and filtering. A merged cell in a column can cause the sort to fail or produce incorrect results. Avoid merging cells that are part of a data table.
• Copy-paste problems: Copying a merged range and pasting it can create unexpected results, especially if the paste area has different dimensions. Always paste into a single cell to avoid spreading the merge.

Alternatives to Merging Rows

If you frequently need to combine data from multiple rows, consider these alternatives that don’t destroy data:

• Keep rows separate and use a grouped header: Use a separate row above the data with a label that spans the width visually (not by merging) or simply repeat the label.
• Use a Pivot Table: To summarize data from multiple rows into one, a pivot table can aggregate values without merging or losing anything.
• Use Power Query (Excel) or the QUERY function (Sheets): These can combine and reshape data dynamically, giving you a clean output without altering the original.
• Just leave the rows unmerged: Often, a simple header row with the same value repeated in each row is easier to work with than a merged cell.

How to Undo a Merge and Recover Data

If you’ve merged cells and lost data, the only reliable recovery is the Undo command (Ctrl+Z) or closing the file without saving. If you’ve already saved, check for version history:

• Excel: File > Info > Version History (or Manage Workbook) to see previous versions.
• Google Sheets: File > Version history > See version history. You can restore an earlier version.

If no history exists, the data is gone. This is why it’s critical to save a backup before merging, or to use the concatenation methods described above.

Frequently Asked Questions

Can I merge rows without losing data in Excel?

No, the built-in merge always keeps only the top-left value. To keep all data, you must first combine the values using a formula like =A1&" "&A2 or TEXTJOIN, then merge. There is no setting that changes this default.

Does Google Sheets merge rows differently?

No, Google Sheets behaves the same way. Merging cells keeps the top-left value and discards the rest. The same workarounds apply.

What does “Merge and Center” do to data?

“Merge and Center” is just a merge with the alignment set to center. It still keeps only the top-left value. The data loss is identical to a regular merge.

Can I merge rows but keep the data in a new column?

Yes, you can create a new column with a formula that concatenates the values from the rows you want to merge, then hide or delete the original rows. This preserves the data in a single cell without using the merge feature.

Is there a way to merge rows without losing formatting?

Merging preserves the formatting of the top-left cell only. If you need formatting from other cells, you’ll have to reapply it after merging. Using “Center Across Selection” preserves all formatting because it doesn’t merge.

Why does Excel warn me about data loss when merging?

The warning exists because the merge operation deletes all values except the top-left one. It’s a safety measure to make you aware that data will be lost. Always read and heed the warning.

Related

• Pentagon Plan to Disrupt Islamic State’s Internet
• Flexible Work: Where Future Leaders Miss Out
• Recover Word Document Saved Over: 6 Ways