How to Merge Cells Without Losing Data
Last updated June 2026
Quick answer
Merge & Center keeps only the top-left value and discards the rest, so it always loses data. To keep everything, first combine the values into one cell with & or TEXTJOIN, paste the result back as values, then merge. Or skip merging entirely with Center Across Selection, which spans the look without touching the underlying cells.
Excel warns you with "Merging cells only keeps the upper-left value" for a reason: the merge throws away every other cell in the range. The fix is to move the data you care about into one cell before you merge, then merge the now-redundant range. Here is the safe sequence.
How do you merge cells and keep all the data?
Write a formula that combines the values
Pick an empty cell next to your data and join the values with
&, adding a space, comma, or line break as the separator. This reads the original cells, so nothing is lost yet.Or use TEXTJOIN for a whole range
For three or more cells,
TEXTJOINis cleaner: the first argument is the separator, the second (TRUE) skips blanks, and the third is the range. Drag it down a column to combine every row at once.Convert the formula to a fixed value
Select the formula cells, press
Ctrl+C, then Home → Paste → Paste Values (orCtrl+Alt+V, thenV). The combined text now lives in the cell with no formula behind it.Move the combined text into place
Cut the pasted value with
Ctrl+Xand paste it into the top-left cell of the range you intend to merge. Delete the helper column and the now-empty source cells.Merge the range
Select the cells, then on the Home tab click the dropdown arrow next to Merge & Center and pick Merge & Center or Merge Cells. Because only the top-left cell holds data, the warning no longer costs you anything.
If you only need cells to look merged across a row — like a title spanning several columns — use Center Across Selection instead. Select the range, press Ctrl+1, go to the Alignment tab, and set Horizontal to Center Across Selection. The text centers visually while every cell stays separate and selectable.
| Option | What happens to the data | Best for |
|---|---|---|
| Merge & Center | Keeps Mon only, deletes Tue and Wed, centers it across A1:C1 | A centered title where the other cells are empty |
| Merge Across | Merges row by row; still keeps only the left value in each row | Merging many rows at once without combining columns |
| Center Across Selection | Keeps all three values; centers Mon visually, no real merge | The merged look without breaking sorting, filtering, or copy-paste |
| Combine, then merge | Joins to Mon, Tue, Wed in one cell, then merges — nothing lost | When you genuinely need one merged cell holding every value |
Frequently asked questions
- Why do merged cells break sorting and copy-paste?
- A merged block isn't a single cell — Excel treats it as one large cell that can't line up with the unmerged rows around it. That mismatch triggers "This operation requires the merged cells to be identically sized" when you sort, filter, or paste across the range.
- How do I unmerge cells and split the data back out?
- Select the merged cell and click Merge & Center again to toggle it off — the value drops into the top-left cell. To split combined text into separate columns, use Data → Text to Columns and pick your separator, such as a comma or space.
- How do I find every merged cell in a worksheet?
- Press
Ctrl+F, click Options → Format → Alignment, tick Merge cells, then Find All. Excel lists every merged range so you can clean them up before sorting or building a pivot table from the data. - Is TEXTJOIN available in my version of Excel?
TEXTJOINships with Excel 2019, Excel 2021, and Microsoft 365, plus Excel on the web and Google Sheets. In Excel 2016 or earlier it returns a#NAME?error, so join values with the&operator orCONCATENATEinstead.