Convert Multiple Excel Columns into Rows for a PivotTable with WPS Spreadsheets
Question details
The user needs to convert multiple Excel columns, such as registration and activity status columns, into row-based records so that the data can be summarized more easily in a PivotTable.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has status or survey data stored in separate columns and wants to reshape it into a long, unpivoted format for PivotTable reporting.
- Observed behavior
- The data currently has separate columns for 'Is Customer Registered?' and 'Is Customer Active?', which makes row-based aggregation difficult without transformation.
Open the workbook in WPS Spreadsheets and confirm that the columns you want to unpivot, such as Is Customer Registered? and Is Customer Active?, exist as separate columns and that each record has a shared identifier column so rows can be traced back after transformation.
Unpivot the registration and activity columns in Power Query
Use this method when you need to reshape multiple value columns into Attribute and Value rows before building a PivotTable.
Power Query unpivot converts selected columns into two columns: Attribute and Value. This creates one row for each selected column in every source row, which gives PivotTables a long-format table that is easier to group, count, and filter.
Click any cell inside the data range, go to the Data tab, and select From Range/Table. In WPS Spreadsheets, confirm the range and click OK or Create Table if prompted.
In Power Query Editor, hold Ctrl and click the 'Is Customer Registered?' and 'Is Customer Active?' column headers to highlight both columns.
Go to the Transform tab and click Unpivot Columns. The selected columns are combined into Attribute and Value columns, creating one row for each original column value.
Rename the Attribute and Value columns to meaningful names such as Registration Type and Registration Status if needed. Then click Home > Close & Load To, choose Table, and load the result into a new worksheet.
Select the loaded table, click Insert > PivotTable, and place the identifier field in Rows, the renamed Attribute column in Columns or Rows, and the Value column in Values as needed.
Reshape Excel Columns into PivotTable-Ready Rows with WPS Spreadsheets
Use WPS Spreadsheets to open the workbook, run Power Query, and unpivot the registration and activity columns into row-based records. After loading the transformed table back into the sheet, you can create a PivotTable directly from the long-format data.
- 1. Open the workbook: Launch WPS Spreadsheets and open the Excel workbook that contains the registration and activity columns.
- 2. Start Power Query: Select the data range, click the Data tab, and choose From Range/Table to open the Power Query Editor.
- 3. Unpivot the columns: Hold Ctrl and select 'Is Customer Registered?' and 'Is Customer Active?', then go to Transform > Unpivot Columns to create Attribute and Value rows.
- 4. Rename and load: Rename the Attribute and Value columns if needed, then click Home > Close & Load to return the transformed table to the worksheet.
- 5. Insert the PivotTable: Select the loaded table, click Insert > PivotTable, and arrange the identifier, Attribute, and Value fields to build the report.
Frequently Asked Questions
What does Unpivot Columns do in Power Query?
It converts the selected columns into two columns named Attribute and Value. Each original column becomes a row, so data is reshaped from a wide format to a long format that PivotTables can aggregate more easily.
Will my original data change when I use Power Query to unpivot?
No. The original worksheet remains intact. Power Query creates a query and loads the transformed result into a new table or connection, and you can refresh it when the source data changes.
Why do I see Attribute and Value columns after unpivoting?
Attribute and Value are the default Power Query column names. You can right-click each column header in Power Query, choose Rename, and use more meaningful names such as Registration Type and Registration Status before loading.
Can I create a PivotTable directly from the transformed Power Query result?
Yes. In Power Query, use Close & Load To and choose PivotTable Report, or load the result as a table and then click Insert > PivotTable in WPS Spreadsheets to build the report from the row-based records.
Does WPS Spreadsheets support this transformation for Excel files?
WPS Spreadsheets can open .xlsx and .xls files and provides Power Query tools in the Data tab in supported versions, so you can unpivot Excel columns and keep working with familiar Excel-compatible formats.
