Merged cells make sorting and filtering unreliable

Built!

Unmerge selected columns in one or more XLSX sheets and fill data-row blanks with the value above, creating a table that is easier to sort.

Current workflow

Most people unmerge cells one group at a time in Excel, then drag-copy the value down into the blank cells that appear — and repeat that for every merged group. Doing it "properly" with Excel's own features means memorizing a multi-step sequence: unmerge, F5 (Go To), select blank cells in Special, type = and point to the cell above, Ctrl+Enter, then paste back as values — miss one step and you start over. Power Query can do the conversion too, but it requires setup and learning. The reason all this matters is simple: status tables received from institutions or head offices often have department names and dates merged vertically across rows, and in that state a filter only shows the first row of each merged group while the rows below are treated as empty and silently dropped. Pivot tables break the same way.

Solution

Unmerge & Fill Cells

How to use

1. Upload the XLSX file with merged cells. 2. Choose the sheets, columns and start row, then check the filled values (for example, the department name) in the preview. 3. Download the result as an XLSX file, compare it with the original, and use it for filtering, sorting, and pivot tables.

Example

- Input: a status table with department names and dates merged vertically — a table where filtering shows only the first row of each group - Result: a 30-row table with merges removed and blanks filled with the value above — every row has a value, so sorting, filtering, and pivots all work

Open tool
View details
Limits
- Only the columns you choose are processed, and blank separator rows and formula-only rows are skipped. - Blanks that were supposed to stay empty can get filled with the value above. After converting, check the rows that originally had no value. - Formatting such as cell colors and borders may not carry over to the result. This is a value-focused conversion.
Cost type
Free

Multi-level headers are the main case this tool doesn't solve. A header that mixes horizontal and vertical merges needs to be flattened into a single header row as a separate job; this tool focuses on unmerging the data area and doesn't restructure the header itself. There's also a correctness risk to watch: if a blank cell was genuinely supposed to be empty, filling it with the value above can propagate a wrong value downward. A table that only "looks merged" through formatting is different from one where the value truly lives in a single cell, so compare the result against the original and check for rows that never had a value.

Support

I have this problem too · 0 people

Sign in to support or ask a question

Comments

Be the first to comment.

Additional information

Delete a post made without signing in