Pivot Table Issues

How to Fix an Unsuitable PivotTable Layout in WPS Office Spreadsheets

Aamir Naveed Akram Nov 13, 2026 869 views

Question details

The user needs a reliable way to reshape tabular data that is not normalized enough for a PivotTable so the report displays correct groups and values.

How to Fix an Unsuitable PivotTable Layout in WPS Office Spreadsheets

Product: WPS Office Spreadsheets
Device & OS: not provided
Scenario: The user is working with a tabular arrangement or cross-tab report that does not fit a normal PivotTable layout and wants the best alternative before creating the report.
Observed behavior: The PivotTable layout is unsuitable because the source contains repeated column groups, merged rows, or non-normalized fields.

Before you start

Check whether the source range has unique column headers and one row per record. Identify repeated value groups such as month or region columns and any blank or merged rows that must be flattened before you insert a PivotTable.

Solution 1Recommended

Flatten the source data into a one-row-per-record table in WPS Spreadsheets

Use this when you want to keep the report in WPS Office and the existing layout has repeating column groups such as months, regions, or product categories.

A PivotTable works best when each column is a field and each row is a single record. Cross-tab reports with values spread across multiple columns usually need to be converted into a flat table first.

Open the source workbook and insert a clean sheet

In WPS Office Spreadsheets, open the workbook, click the plus icon next to the sheet tab, and name the new sheet FlatData.

Create normalized column headers

Enter unique field names in row 1, for example Region, Product, Month, and Sales Amount.

Move each repeated value to its own row

Copy each cross-tab value into the matching field row so that every row describes one record only, leaving no blank rows inside the table.

Convert the range into a table

Select the data, click Home > Format as Table, choose a style, and confirm the range. This helps WPS update the PivotTable when the source changes.

Create the PivotTable from the flat table

Click anywhere in the table, go to Insert > PivotTable, choose a location, and drag fields into the row, column, and values areas.

Flatten the source data into a one-row-per-record table in WPS Spreadsheets

Expected result: The PivotTable now groups records correctly because every column contains one field and every row contains one record.

Solution 2

Use Power Query in Microsoft Excel to unpivot columns before loading a PivotTable

Use this when the source data is already in Microsoft 365 Excel and you want an automatic transform instead of manually flattening the range.

Solution 3

Use dynamic-array formulas in Excel when a formula-based report is more suitable

Use this when you need a formula-driven result instead of a traditional PivotTable, such as for a dashboard or non-contiguous summary.

WPS PivotTable data preparation

Reshape your source data and build a reliable PivotTable in WPS Office

WPS Office Spreadsheets includes the table and PivotTable tools you need to flatten a non-normalized report and rebuild a reliable summary. You can open Excel files, edit the source range, and create the PivotTable without leaving WPS.

1. Open the workbook in WPS Office: Launch WPS Office Spreadsheets and open the .xlsx or .xls file that contains the unsuitable layout.
2. Flatten the source range: Use a new sheet, unique headers, and one row per record to reshape the range into a normalized table.
3. Format as Table: Select the cleaned range and click Home > Format as Table so the source updates are recognized automatically.
4. Insert the PivotTable: Go to Insert > PivotTable, choose the FlatData range, and place the report on a new sheet.
5. Arrange the fields: Drag the row, column, and value fields into the PivotTable pane to get the intended summary.

Flatten non-normalized reports with free spreadsheet toolsCreate PivotTables from cleaned tables in WPS OfficeOpen and save .xlsx and .xls files with Microsoft Excel compatibilityUse a familiar ribbon interface that reduces the Excel-to-WPS learning curveFinish the task without paying for Microsoft 365

microsoft office alternative - wps office

Frequently Asked Questions

Why does my PivotTable not use my existing layout correctly?

A PivotTable expects a flat data table. If the existing layout is a cross-tab, has merged heading cells, or contains blank rows and subtotal rows, the report will be unsuitable. Flatten the layout in WPS Spreadsheets first so each row is one record and each column is one field.

Can WPS Spreadsheets automatically unpivot columns like Excel Power Query?

WPS Spreadsheets does not have a dedicated Power Query unpivot tool in every version. You can manually reshape the range or use formulas. If you use Microsoft 365 Excel, select the table and choose Data > From Table/Range, then Transform > Unpivot Other Columns.

How do I normalize source data for a PivotTable in WPS Office?

Create a new sheet, add unique column headers such as Region, Product, Month, and Sales Amount, and move each repeated value into its own row. Then select the range and click Home > Format as Table before inserting the PivotTable.

What is the best alternative when source data is not suitable for a PivotTable?

The best alternative is usually to flatten the source into one row per record and one column per field, then build the PivotTable from that table. If the layout requires a formula-based summary, use dynamic arrays in Excel or supported formulas in WPS Spreadsheets.

Does WPS Office support LAMBDA for reshaping PivotTable source data?

LAMBDA is a Microsoft 365 function and may not be available in every WPS Office version. For formula-based results in WPS Spreadsheets, use supported functions such as SUMIFS, INDEX, MATCH, UNIQUE, or SORT according to your version.

Excel & Data Problems