How to Unstack Two-Column Data into Records in WPS Office Excel
Question details
The user needs to convert a stacked two-column field-and-value list into a structured table where each field name becomes a column header and each record occupies one row.
- Product
- WPS Office Excel
- Device & OS
- not provided
- Scenario
- The user imported or pasted a list that stores field names and values in two columns and wants a normalized table for analysis, reporting, or further export.
- Observed behavior
- The source list is stacked as field-value pairs; the desired state is a cross-tab table with field names as columns and one row per record.
Confirm that the two-column source data repeats the same field labels in a consistent order for each record. Identify the first field label, such as "Name", so you can create a record counter.
Add a record counter and pivot field names with Power Query
Use this method when you have hundreds or thousands of stacked rows and want a refreshable, repeatable transformation.
This approach matches the accepted solution for large two-column lists: a helper column marks each record, then Power Query pivots the field-name column into a structured table.
In cell C2, enter =COUNTIF($A$2:A2,"Name") and fill down to number every source row by record.
Select the helper, field, and value columns, click the Data tab, and choose From Table/Range.
In the Power Query Editor, select the field-name column, click Transform > Pivot Column, and set the Values Column to the value column.
Click Close & Load to place the new records table in a worksheet. Refresh the query after adding new source rows.
Use a built-in PivotTable instead of Power Query
Choose this method if you prefer a formula-and-PivotTable workflow and do not want to use Power Query.
Unstack Two-Column Data into Records in WPS Office Excel
WPS Office Excel provides a direct way to convert a stacked field-and-value list into a records table. Add a record counter, pivot the field column with Power Query or PivotTable, and load or refresh the result while keeping full Microsoft Excel .xlsx compatibility.
- 1. Open your workbook in WPS Office Excel: Launch WPS Office Excel and open the workbook that contains the two-column field-and-value data.
- 2. Add the record counter: In the cell next to the first data row, enter =COUNTIF($A$2:A2,"Name") and fill the formula down to mark each record.
- 3. Load the data into Power Query: Select the helper, field, and value columns, click Data > From Table/Range, and open the Power Query Editor.
- 4. Pivot the field-name column: Select the field-name column, click Transform > Pivot Column, and choose the value column as the Values Column.
- 5. Load and refresh the result: Click Close & Load to output the records table. Refresh the query whenever new source rows are added.
Frequently Asked Questions
Can I unstack the data without Power Query?
Yes. Use a helper column with COUNTIF and then create a PivotTable with the helper column in Rows, the field-name column in Columns, and the value column in Values. This is built into WPS Office Excel and avoids Power Query.
What if the field labels are not repeated in the same order?
Standardize the field column first. The record counter depends on a consistent starting field label such as "Name". If field labels vary, use Power Query to clean and standardize them before pivoting.
How do I add new rows after the transformation?
Append the new field-value rows to the source list. If you used Power Query, click Data > Refresh All. If you used a PivotTable, right-click the PivotTable and choose Refresh.
Will this method work with thousands of rows?
Yes. Power Query is especially reliable for thousands of rows because it does not require manual copy and transpose actions. Add the helper column, pivot the field column, and load the result.
Is the output compatible with Microsoft Excel?
Yes. WPS Office Excel saves files in the .xlsx format, so the PivotTable or transformed table can be opened and edited in Microsoft Excel.
