Unmerge cells in every sheet and in many Excel files at once

On one sheet, unmerging takes a few clicks. With twelve monthly sheets, or thirty branch files, some of the job is covered by Excel's own features and some is not.

Hullspark ・ Updated 2026-10-24

Short answer

For sheets with the same layout, group the sheets and unmerge once. The change lands in the same cells on every sheet in the group＊2.
For many files, there is no point-and-click way to apply the same unmerge to all of them (macros are a separate route, not covered here). Power Query's folder combine expects files of the same type and structure＊3.
After unmerging, the value stays only in the top-left cell＊1. The steps for filling the blanks are on another page.

What Excel does

Built-in method by situation
Situation	Built-in method	Condition
One sheet	Merge & Center drop-down, then Unmerge Cells＊1	Select the range
Several sheets, same layout	Group the sheets, then do the same thing＊2	Same data structure
Many files	Power Query: Folder, then Combine files＊3	Same file type and structure, including columns. Source files stay merged; it reads the values into one new table

Unmerge many sheets at once

Save a copy of the file first. While sheets are grouped, every change lands on all of them＊2.
Click the first sheet tab, hold Shift and click the last one for a run of sheets. For scattered sheets, hold Ctrl and click each tab. For every sheet, right-click a tab and choose Select All Sheets＊2. The workbook name in the title changes to show the group＊2.
Select the range with merged cells. On the Home tab, open the Merge & Center drop-down and choose Unmerge Cells＊1.
Ungroup when done. Right-click a tab and choose Ungroup Sheets, or click a tab outside the group＊2.

If you forget to ungroup, your next edit lands on every sheet. I have not tried filling the blanks while sheets are grouped; the blank cells sit in different places on different sheets.

What is missing

Anything across files. Grouping is a feature of the sheets inside one workbook＊2. With thirty files you open thirty files.
Tables whose merges differ from sheet to sheet. The operation lands in the same cell positions＊2, so cells in other positions stay merged.
What comes after unmerging. Filling blanks and joining header rows are separate jobs. With Power Query's folder combine, you add steps such as Fill Down after combining＊3 ＊4.

For one file a month, Excel's own features are enough. When several files in the same layout arrive every month, the number of manual passes becomes the problem.

An app I make

Table Flattener by Hullspark is a Windows app that turns Excel files with merged cells into one-record-per-row tables and saves them as new files. What follows is what I checked in its code, for the many-files case.

Drop files, or a whole folder. When you choose a folder, you decide whether subfolders are included.
It can convert every sheet in a workbook. Hidden sheets are included only if you ask.
The first file's settings go to files with the same layout. A file with a different layout goes back to automatic detection and is marked in the list.
Each output is the original name plus _data.xlsx. The original is not changed, and an existing file is never overwritten (a number is added).
You can add file name and sheet name columns, and write CSV in UTF-8 or Shift-JIS.
It works on a PC without Excel and makes no network connection.

What it does not do: it does not merge many files into one table. Its estimate of the layout can be wrong, and it has been tested on sample tables I generated, not yet on a range of real Excel files. Formulas, charts and formatting are not carried over; only values are written. It does not produce the original workbook with only the merges removed; the output is a new flat table. Each output workbook has a conversion report sheet. In Shift-JIS CSV, characters that encoding cannot hold become "?", and the app shows how many.

View on the Microsoft Store

FAQ

I grouped the sheets and unmerged, and some sheets came out wrong.

Microsoft says grouping works best when the sheets have identical data structures＊2. If the merges sit in different places on some sheets, ungroup those sheets and fix them one at a time. Microsoft says to hold Ctrl and click the grouped tabs you want to ungroup＊2.

I selected all sheets, then moved to another sheet and the group was gone.

That is documented behavior. When all sheets are selected, browsing between them ungroups them. If you select only some of the sheets, you can browse without ungrouping＊2.

Does the app combine many files into one table?

No. It writes one output file per input file, named like the original with _data added, and never overwrites an existing file (it adds a number). To get one table, turn on the optional file name and sheet name columns and feed the outputs to Power Query's folder combine.

What if one file has a different layout?

The first file's settings are used for files of the same layout. A file judged to have a different layout goes back to automatic detection and is marked "different layout" in the list. Check its preview before you save.

Does it read old .xls files or password-protected files?

No. Save an .xls as .xlsx in Excel first, and remove the password before dropping a protected file in. It reads .xlsx, .xlsm, .xltx and .xltm.

Not checked

The Excel steps come from Microsoft's articles; I did not try them in Excel. I did not try Fill Down while sheets are grouped. The app was tested on sample tables I generated. I did not try other companies' apps and do not name any. Checked 2026-10-24.

Microsoft and Excel are trademarks of Microsoft Corporation. This page and the app are not affiliated with, made by or endorsed by Microsoft.

Sources

＊1 Microsoft Support: Merge and unmerge cells in Excel: https://support.microsoft.com/en-us/office/merge-and-unmerge-cells-in-excel-5cbd15d5-9375-4540-907f-c673a93fcedf
＊2 Microsoft Support: Group worksheets: https://support.microsoft.com/en-us/excel/group-worksheets
＊3 Microsoft Learn: Combine files overview (Power Query): https://learn.microsoft.com/en-us/power-query/combine-files-overview
＊4 Microsoft Learn: Fill values in a column (Power Query): https://learn.microsoft.com/en-us/power-query/fill-values-column

Questions: Support or support@hullspark.com. Privacy policy