How to Transpose Filled Cells into New Rows in WPS Office Spreadsheet
Question details
The user wants to transform a table in Excel 365 so that each populated cell in multiple columns creates a separate row, while the first-column value is repeated.

- Product
- WPS Office Spreadsheet
- Device & OS
- not provided
- Scenario
- A source table has repeated row identifiers in the first column and filled values scattered across multiple columns. The user needs a normalized list with one record per filled cell.
- Observed behavior
- The current table keeps multiple filled values side-by-side in each row instead of converting each filled cell into its own row.
Before transforming, identify the key column that must repeat for every new row and confirm whether blank cells should be dropped from the result. Remove confidential data if you plan to test the transformation on a sample table.
Unpivot Filled Columns with Power Query in WPS Office Spreadsheet
Use this method when you want a repeatable, refreshable transformation of a crosstab-style table into new rows.
Power Query, labeled Data Query in some WPS Office versions, normalizes the table by turning selected columns into attribute/value pairs. The original key column can be kept and repeated automatically for each populated cell.
Click any cell in the source table, then go to the Data tab and choose From Table/Range. Confirm the table range and enable My table has headers if needed, then click OK.
In the Power Query Editor, select the column whose original value must repeat for each new row, such as Name, ID, or Product Code.
Right-click the selected key column and choose Unpivot Other Columns. This converts every filled cell in the other columns into a new row under an Attribute and Value layout.
If blank cells remain, click the filter arrow on the Value column, clear (Blank), and click OK.
Click Home > Close & Load in the Power Query Editor to place the new rows into a worksheet.

Use a Helper-Column Method for Small Tables Without Power Query
Use this approach for one-time conversions or when Power Query is not available in your WPS Office version.
Convert Filled Cells to New Rows with WPS Office Spreadsheet
WPS Office Spreadsheet gives you Power Query/Data Query tools to unpivot a crosstab table, so each filled cell becomes its own row while the key column repeats automatically. It works with Excel 365 files and saves back to .xlsx without losing format compatibility.
- 1. Open the workbook in WPS Office Spreadsheet: Launch WPS Office, click Menu > Open, and select the Excel 365 workbook that contains the table.
- 2. Load the source data into Power Query: Click inside the table, go to the Data tab, and choose From Table/Range to open the Power Query Editor.
- 3. Unpivot the filled columns: Select the repeated key column, right-click it, and choose Unpivot Other Columns. Filter out blank values from the Value column if they appear.
- 4. Close & Load the result: Click Home > Close & Load to send the normalized rows to a new worksheet.
- 5. Save the workbook as .xlsx: Press Ctrl+S or click Menu > Save As and choose Excel Workbook (*.xlsx) to keep the result compatible with Excel 365.

Frequently Asked Questions
Does WPS Office include Power Query for unpivoting columns?
Yes, recent WPS Office versions include Power Query/Data Query on the Data tab. Select the source range, click Data > From Table/Range, then right-click the key column and choose Unpivot Other Columns.
Will unpivoting create rows for empty cells?
Normally no. Power Query unpivots non-blank values. If blanks still appear, click the filter arrow on the Value column and clear (Blank) before loading.
Can I keep the original first-column value repeated in every new row?
Yes. In the Power Query Editor, select only the first-column key column, then right-click and choose Unpivot Other Columns. The key value is repeated automatically for each filled cell in that row.
Does this work when the source table has merged cells?
Merged cells can cause errors. Unmerge them and fill down the key column first so every source row has the repeated value before loading into Power Query.
Will the WPS result still open in Excel 365?
Yes. Save the output as a standard Excel Workbook (*.xlsx), and Excel 365 can open it without losing the normalized rows or formatting.




