How to Unmerge Cells in Excel without Losing Data

The Problem with Excel's Merged Cells

When you unmerge a cell in Excel — through Format → Unmerge or Home → Merge & Center → Unmerge — Excel keeps the value only in the top-left cell of the original merged range. Every other cell becomes blank.

This is normal, expected, and the same behaviour every spreadsheet tool follows. The problem isn't the unmerge itself — it's what comes next:

• Sorting rearranges rows, but blank cells get scattered between values that should belong together. Your report becomes incoherent.
• Filtering treats blanks as a separate (Blanks) category — so a filter for “North” misses every row where the merge originally spanned several rows.
• Pivot Tables group rows by their values; blanks form their own group and inflate the totals.
• VLOOKUP / XLOOKUP / INDEX-MATCHlookups fail silently because the row's key cell is empty — and you don't notice until the numbers don't reconcile.

The Manual Fix: 10 Steps with “Go To Special”

Filling the blanks left over from an unmerge is something you do once and remember forever. Here's the canonical sequence:

1. Select your data— the column or range that contains the cells you've just unmerged.
2. Open Home → Find & Select → Go To Special (or press Ctrl + G, then click Special).
3. In the dialog, choose Blanks and click OK. Every empty cell in your selection is now selected.
4. Without clicking anywhere else, type =.
5. Press the ↑ (Up Arrow) key. The active cell now contains a formula referencing the cell directly above it.
6. Press Ctrl + Enter. This fills every selected blank with the formula, copying the cell-above value into each empty slot.
7. Reselect your full data range.
8. Copy it (Ctrl + C).
9. Paste Special → Values (Ctrl + Alt + V, then V, then Enter). This replaces the formulas with literal values so they survive sorting.
10. Save the file.

That works — but it takes a minute or two per column, you have to remember the exact sequence, and one wrong keystroke (accidentally clicking somewhere mid-process) silently breaks everything.

Where ExcelCleansing Fits

ExcelCleansing is intentionally conservative about your data. When the tool unmerges cells it does notauto-fill the blanks with the merged value. The original value stays exactly where it was — same as Excel's native unmerge — and freed cells stay empty. Your data isn't changed; it's just no longer visually merged.

That leaves the manual Go-To-Special step above as your decision: do it if you want filled blanks for sorting and pivots, skip it if you don't.

What ExcelCleansing does do automatically — across every sheet, in one click — is everything else that messy spreadsheets need:

• Removes empty rows and columns that bloat the file (the kind ERP exports leave scattered around the data).
• Re-aligns misplaced headers — when a label sits one column off from the data it describes, the tool slides it back into place.
• Drops “ghost” columnsthat look styled and present but are actually >90% empty (a common PDF-to-Excel conversion artefact).
• Strips invisible “ghost” characters (non-breaking spaces, zero-width characters, control codes) that silently break VLOOKUP and TRIM.
• Auto-fits column widths so numbers stop displaying as ### and preserves all formatting (bold, colours, borders, number formats, dates) through the round-trip.

For most real-world files — accounting exports, ERP reports, shared workbooks, PDF roundtrips — the unmerge itself is one of the smaller problems. The mess aroundthe merges is what costs hours of manual cleanup. That's what the tool removes.

What the unmerge step itself does

For the curious: ExcelCleansing's unmerge step walks every merged range in every sheet, breaks the merge, and leaves the data exactly where it was — origin keeps its value, every other cell in the range stays empty. No fill, no propagation, no surprises.

Cell styling, number formats, formulas, and date types are all preserved through the round-trip via a deep clone. The cleaned file looks visually identical to the original, minus the merges (and minus the other mess listed above).

Everything happens entirely in your browser. The file never leaves your machine — no upload, no account, no telemetry. You can verify this for yourself by opening DevTools → Network and watching for zero outbound file traffic.