Blog
Merge and Unmerge Cells in Excel Without Losing Data
To merge cells in Excel, select the range and choose Home > Merge & Center; to undo it, choose Unmerge Cells from the same menu. Merging makes a title or a group label look tidy, but it changes the grid underneath. A sheet with merged cells won't sort, filters drop rows, and SUMIF quietly misses quantities. Unmerging brings the grid back but leaves blanks where the values used to appear, which on a 300-row report means a lot of retyping.
Here's how each merge option works, how to find and undo every merge on a sheet, how to fill the blanks in seconds, and how to get the same look without merging at all.
The four merge options and their shortcuts
Select the cells, then click the arrow next to Merge & Center in the Alignment group on the Home tab.

| Option | What it does | Keyboard |
|---|---|---|
| Merge & Center | Joins the cells into one and centers the text | Alt, H, M, C |
| Merge Across | Merges each selected row separately | Alt, H, M, A |
| Merge Cells | Joins the cells into one, keeps the alignment | Alt, H, M, M |
| Unmerge Cells | Splits a merged cell back into its original cells | Alt, H, M, U |
Merge Across is the one people overlook. Select B2:E5 and it creates four separate merged rows, one per row, instead of one big block. That's useful for a wide notes column inside a table.
If more than one cell in the range holds a value, Excel warns you before merging. Only the value in the upper-left cell is kept and the others are deleted. Unmerging later does not bring them back, so if you need both values, combine them in another column first with =A2&" "&B2 or TEXTJOIN.
Why merged cells break sorting, filtering and totals
A merged block looks like it spans several rows, but the value lives only in its upper-left cell. If "East DC" is merged across A2:A4, then A3 and A4 are empty. Click the merged block and the Name Box shows A2. That single fact causes three problems.
Sorting stops. Try to sort a range with merged cells of different sizes and Excel says "To do this, all the merged cells need to be the same size." Microsoft's article on finding merged cells puts it bluntly: Excel doesn't sort data in a column that contains merged cells.
Filters show only the first row. Filter column A for "East DC" and only row 2 appears, because rows 3 and 4 are blank.
Totals come up short. SUMIF(A:A,"East DC",E:E) adds only the first row's quantity. A pivot table puts the other rows under "(blank)."
Find every merged cell on a sheet
When several people have edited a file, the first job is finding where the merges are. The method Microsoft documents uses Find with a format condition:
- Press Ctrl + F and click Options.
- Click Format, go to the Alignment tab, tick Merge cells and click OK.
- Leave "Find what" empty and click Find All.
The list at the bottom shows every merged range. Press Ctrl + A inside that list to select them all on the sheet, then click Unmerge Cells once. If the whole sheet can be unmerged, select everything with Ctrl + A and click Unmerge Cells.

Fill the blanks after unmerging
After unmerging, the group label sits in the first row of each group and the rows below are empty. To fill each blank with the value above it:
- Select the label column, for example A3:A301.
- Press Ctrl + G, click Special, choose Blanks and click OK. Only the empty cells stay selected. Microsoft's guide to finding and selecting cells that meet specific conditions lists Blanks among the Go To Special options.
- Type
=and press the Up arrow once, so the formula points at the cell just above. - Press Ctrl + Enter, not Enter. Every selected blank gets its own formula pointing to the cell above it.
- Copy the column and paste it back as values (Ctrl + Alt + V, then Values).
Skip step 5 and the formulas will scramble the labels the first time someone sorts. Don't use this on columns that are supposed to have blanks, such as a notes column.
Center Across Selection: the same look, no merge
If all you want is a centered title, you don't need to merge. Select the title cell and the cells across the width of the table, press Ctrl + 1, go to Alignment, set Horizontal to Center Across Selection and click OK.

The title looks centered, but each cell stays independent. Selecting a column no longer drags in its neighbors, and sorting and filtering work normally. There's no vertical equivalent, so for group labels the clean approach is to put the label on every row. If repeated labels look busy, use conditional formatting to grey out a label that matches the one above it.
Worked example: from merged report to sortable data
At the end of each month, Ridgeline Manufacturing's logistics team sends Inventory_by_Warehouse_Oct.xlsx to purchasing. The title is merged across A1:H1, and the warehouse name in column A is merged down across each warehouse's items: East DC, West DC and Central. The buyer wants to sort by on-hand quantity to see what to reorder, and Excel refuses.
Before touching anything, she records the total on-hand quantity with =SUM(E3:E301): 12,480 units across 299 rows. Find with the Merge cells format turns up one title merge, three warehouse merges and five merges in the Notes column. She selects them all from the Find All list and unmerges. In column A only, she uses Go To Special > Blanks, = plus Up arrow, and Ctrl + Enter, then pastes the column as values. The Notes column stays as it is, because each note belongs to one item. The title gets Center Across Selection.
The check afterwards: still 299 rows, still 12,480 units. Sorting by on-hand quantity works immediately, and a pivot table now splits the stock cleanly: East DC 5,210, West DC 4,030, Central 3,240. The logistics team switched its template so the warehouse name goes on every row from now on.
👉 How to Combine Two Columns in Excel Without Losing Data
Common mistakes with merged cells
Merging cells that hold data. Click OK on the warning and everything but the upper-left value is gone. Undo straight away with Ctrl + Z.
Paste fails on a merged range. Excel refuses to paste when the merge layout of the source and destination differ. Unmerge the destination first, or paste values only.
The merge buttons are grey. You can't merge cells inside an Excel table (Ctrl + T). Merge only outside the table, or use Center Across Selection.
Selecting one column selects several. With a title merged across A1:H1, clicking the column C header selects A through H. Inserted columns and width changes then hit the wrong range.
AutoFit ignores merged cells. Double-clicking a row border doesn't resize a merged cell with wrapped text. Set the row height manually or unmerge.
Windows, Mac and Excel for the web
Excel for Microsoft 365 and Excel 2016 and later on Windows share the same menu and shortcuts. Excel for Mac has the same Merge & Center menu on the Home tab, and Format Cells opens with Command + 1. In Excel for the web, merge and unmerge from the Home tab; to tell whether a cell is merged, select it and see if the Merge & Center button appears highlighted.
FAQ
Q. I unmerged the cells but the other values are missing. Can I get them back?
No. Excel deleted them when the cells were merged. For a repeated label, refill the blanks with Go To Special > Blanks and Ctrl + Enter. For anything else, go back to an earlier version of the file.
Q. Can I number merged cells of different sizes?
Yes. Select the merged cells in the number column, type =MAX($A$2:A2)+1 (with a text header in A2), and press Ctrl + Enter. Each block gets the next number regardless of its size.
Q. Can I build a pivot table from a sheet with merged cells?
You can, but merged rows land under "(blank)" and the totals are wrong. Unmerge and fill the blanks first.
Q. Is merging ever fine?
On a final printout that nobody will sort or calculate from, yes. If the same file is also a data source, use Center Across Selection for titles and repeat labels on every row.
How inline AI cleans up merged reports
Unmerging is one click; finding every merge, filling the right columns and checking the totals afterwards is what takes the time. inline AI finds the merged ranges in the open file, unmerges them, fills the columns that need repeating values, and shows the totals before and after so you can see nothing moved. With inventory running in ARP, warehouse stock can be totaled any time, without rebuilding a spreadsheet each month.
Hand your documents and your company’s work to inline AI.
Keep a file open, say what you need, and AI edits it or drafts a new one. Add the company AI OS and AI runs the rest of the work, ERP included.
Book a demo



