WPS Spreadsheet: Unmerge and Restructure Excel Data for PivotTables
Question details
The user needs to clean merged-cell data and restructure it into a table that can be analyzed with a PivotTable.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- A worksheet contains merged labels, blank cells, and possibly subtotal rows that prevent the data from being recognized as a suitable PivotTable source.
- Observed behavior
- Merged cells leave values only in the first row of each group, so records are incomplete and the source range is not normalized for PivotTable analysis.
Identify the columns containing merged labels and remove or exclude subtotal and Total rows from the source range. Confirm that the first row contains column headers and that each remaining row should represent one record.
Unmerge cells and fill repeated values down in WPS Spreadsheet
Use this method when the worksheet has grouped labels in merged cells and you need a clean, PivotTable-ready range without advanced transformation tools.
A PivotTable works best when every record occupies one row and every field has a value. After unmerging cells, copy each group label into the blank rows below it, then remove subtotal rows before creating the PivotTable.
Open the workbook in WPS Spreadsheet and drag across the complete data range, including the columns that contain merged labels.
Go to the Home tab, open the Merge and Center menu, and choose Unmerge Cells. The original value remains in the upper-left cell while the other cells become blank.
Select each affected column or the blank cells below the first value, then use Home > Fill > Down or press Ctrl+D to repeat the value through the group.
Filter the cleaned range and delete or exclude rows labeled Total, Subtotal, or other summaries so that only detail records remain.
Click any cell in the cleaned range, open the Insert tab, choose PivotTable, confirm the source range, and select the destination for the report.

Restructure the worksheet into a normalized table with helper columns
Use this option when the source layout contains repeated sections or several header levels that must be converted into one record per row.
Use Excel Power Query when the workbook requires repeatable unpivoting
Use this method in Microsoft 365 when the same merged or cross-tabbed report must be cleaned repeatedly; WPS Spreadsheet can open the resulting XLSX file afterward.
Clean merged Excel data in WPS Spreadsheet before building a PivotTable
WPS Spreadsheet lets you open an XLSX workbook, unmerge grouped cells, fill labels down, remove summary rows, and convert the result into a structured table for PivotTable analysis. Its familiar spreadsheet interface makes the cleanup process easy to follow without changing the original file format.
- 1. Open the workbook: Launch WPS Spreadsheet, choose Open, and select the Excel XLSX file containing the merged data.
- 2. Unmerge the source range: Select the affected cells, go to Home > Merge and Center, and choose Unmerge Cells.
- 3. Fill missing labels: Select the blank cells below each label and use Home > Fill > Down or press Ctrl+D.
- 4. Create a clean table: Remove Total and Subtotal rows, select the complete range, and choose Insert > Table.
- 5. Build the PivotTable: Click inside the table, choose Insert > PivotTable, and arrange the cleaned fields in Rows, Columns, Values, and Filters.

Frequently Asked Questions
Why do merged cells cause problems in a PivotTable?
A merged group label is usually stored only in the first cell, leaving the following rows blank. Unmerge the range and use Home > Fill > Down so every record contains its required label.
How can I fill blank cells with the value from the row above in WPS Spreadsheet?
Select the blank cells together with the source value above them, then choose Home > Fill > Down or press Ctrl+D. Check the selection first so values are not filled into unrelated columns.
Should Total and Subtotal rows be included in the PivotTable source?
No. Remove or exclude summary rows labeled Total or Subtotal before creating the PivotTable. Including them can double-count amounts and produce misleading results.
Can WPS Spreadsheet unpivot a cross-tab report?
WPS Spreadsheet can manually restructure a cross-tab by creating helper columns and converting the result to a table. For repeatable unpivot transformations, use Excel Power Query, then open the resulting XLSX file in WPS Spreadsheet.
How do I attach a workbook when asking for help with this issue?
Open the full text editor in the support or discussion form, then click the attachment icon and select the workbook file to upload it.




