Business & Productivity · Guide
How to merge cells in Excel without losing data
Merge and Center keeps only the top-left value and quietly deletes the rest. Here is how to merge safely, how to combine text properly, and the alternative that breaks nothing.
The Nextversity teamBusiness & Productivity schoolUpdated August 10, 20265 min read
On this page
The short answer
Select the cells and click Merge and Center on the Home tab. But read this first: merging keeps only the top-left value and deletes everything else, after a warning most people click straight through.
If you want to keep the contents, combine them into one cell first, then merge. And if you are only merging to center a title across some columns, there is a better option that does not create a merged cell at all.
Merge without losing anything
Say A2 holds Jane and B2 holds Smith, and you want Jane Smith in a merged cell.
- In a spare cell, enter
=A2&" "&B2. That is the ampersand method, and the" "is the space between them. - For more cells or a cleaner formula, use
=TEXTJOIN(" ",TRUE,A2:D2). The TRUE tells it to skip blanks, which saves you from double spaces. - Copy the result, then Paste Special > Values over the top. The text is now real, not a formula pointing at cells you are about to wipe.
- Now merge, and only the value you built survives, which is the one you wanted.
The order is the whole trick. Merge first and the data is gone before you have combined it.
The three merge options
The arrow next to the button has more than one behavior:
- Merge and Center: one cell, contents centered. The usual pick for a title.
- Merge Across: merges each selected row separately, so five rows become five merged cells rather than one big one.
- Merge Cells: merges without changing the alignment, which matters when you want a left-aligned label.
The alternative that breaks nothing
Most merges exist to center a heading over a few columns. You can do that without merging anything:
- Select the cells the title should span.
- Press Ctrl+1 to open Format Cells.
- On the Alignment tab, set Horizontal to Center Across Selection.
Visually identical. Nothing is merged, so sorting, filtering, copying and pasting all keep working. If you take one thing from this article, take this one.
Merged cells look tidy and behave badly. Center Across Selection looks the same and behaves itself.
Why merged cells cause trouble
It is worth knowing what you are trading away:
- Sorting and filtering stop working reliably, because rows no longer have a consistent shape.
- Pivot tables refuse merged header rows outright.
- Copy and paste produces errors like "we cannot do that to a merged cell" when the shapes do not match.
- Formulas referencing a merged block only see the top-left cell.
- Keyboard navigation jumps unpredictably, which slows down anyone using the sheet after you.
None of that matters for a printed cover page. All of it matters in a working data sheet. Microsoft's Excel support has the current behavior if you want the official version, and Google Sheets has the same trade-off for the same reasons.
A simple rule
Merge in presentation areas: title blocks, printed reports, headers above a report that nobody sorts. Never merge inside a range of data that anyone will sort, filter, pivot or import.
Keep your data in a plain rectangle with one header row and no merges. Build the pretty layout on a separate sheet that reads from it. That single habit prevents most spreadsheet frustration, and it is the thing that separates a workbook that survives handover from one that gets rebuilt.
Where to go next
If you are merging cells to lay out a report by hand every month, the real answer is a pivot table and a chart that refresh in two clicks. The Excel certificate covers that, the introduction course covers the ground before it, and the whole Business & Productivity school is open on one subscription.
You do not need a course to merge cells. You might want one to stop needing to.
Questions people ask
How do I merge cells in Excel?
Select the cells, then click Merge and Center on the Home tab, or open the arrow next to it for Merge Across and Merge Cells. Only the value in the top-left cell survives, so combine the text first if you need to keep it.
How do I merge cells without losing data?
Combine the values into one cell first with TEXTJOIN or the ampersand, paste the result as values, then merge. Merging never keeps more than the top-left value, whatever order you do it in.
Why is merge greyed out in Excel?
You are almost certainly inside an Excel table. Tables do not allow merged cells. Convert to a range from the Table Design tab if you really need the merge, or use Center Across Selection instead.
How do I unmerge cells?
Select the merged cell and click Merge and Center again, which toggles it off. The value stays in the top-left cell and the others come back empty.
Why do merged cells break sorting and filtering?
Sorting needs every row to have the same shape. A merged cell spans rows or columns, so Excel cannot work out where a row starts and ends, and it refuses or scrambles the result. This is why merged cells are best kept out of data ranges.