How to Transpose Excel Table Data Between Sheets Automatically in WPS Office
Question details
The user needs to transfer stock references from one Excel table to another sheet and automatically populate new project columns without manually rebuilding lookup formulas or table structures.
- Product
- WPS Office Excel
- Device & OS
- not provided
- Scenario
- The user maintains stock references in one sheet and project records in another, and wants project columns to update automatically when stock or project data changes.
- Observed behavior
- A fixed table structure with normalized stock rows, linked formulas or Power Query, and a PivotTable for reporting is more reliable than adding columns dynamically.
Confirm both the source stock table and destination project sheet are in the same WPS Office workbook, or that your WPS Office version can connect to the source file. Identify the key stock reference column that will be used to link records.
Normalize the stock data and link project records by formula
Use this solution when you need a stable, low-maintenance structure where stock items are stored in rows and project sheets reference them by key.
Keep stock references in a flat, row-based table instead of adding a new column for every project. A fixed structure is easier for formulas, tables, and PivotTables to automate.
Link project records to stock rows with lookup formulas such as VLOOKUP, INDEX/MATCH, or XLOOKUP so project columns populate automatically.
In WPS Office Excel, select the stock data range and press Ctrl+T to create a table. Make sure each stock item is in its own row and each attribute is in its own column.
Create or open the destination sheet and add a column that stores the stock key, such as Stock ID or Item Code.
In the first project data cell, enter a formula such as =VLOOKUP(A2,StockTable,2,FALSE) or =INDEX(StockTable[Column2],MATCH(A2,StockTable[StockID],0)) and press Enter.
Drag the fill handle down the project table so each linked stock reference populates automatically from the normalized stock table.
Use Power Query to unpivot and load the data automatically
Use this solution when the source data is wide or unpivoted and you need to reshape it into rows for further analysis or automatic updates.
Use a PivotTable to create an expandable project report
Use this solution for reporting when you want the layout to expand as new stock or project records are added without manually inserting columns.
Automate Excel Table Transfers with WPS Office Excel
Use WPS Office Excel to structure stock records as normalized tables, link project sheets with lookup formulas, and expand reports with PivotTables. This approach creates an updateable workflow without manually inserting project columns, while keeping your workbook compatible with Microsoft Excel .xlsx files.
- 1. Download and open WPS Office Excel: Install WPS Office, open WPS Office Excel, and load the workbook that contains the stock and project sheets.
- 2. Convert stock data to a table: Select the stock data range and press Ctrl+T to create a normalized table with each item in a separate row.
- 3. Link project records with a lookup formula: On the project sheet, enter =VLOOKUP(A2,StockTable,2,FALSE) or =INDEX/MATCH in the first cell and fill it down the project rows.
- 4. Add a PivotTable report: Insert a PivotTable from the Insert tab and place the key, project, and value fields on a new sheet for an expandable report.
- 5. Refresh after data updates: After changing source data, use Data > Refresh All or right-click the output table and choose Refresh to update the transferred data.

Frequently Asked Questions
Can WPS Office Excel refresh transferred data automatically?
Yes. If you use formulas, they recalculate automatically when linked cells change. If you use Power Query or a PivotTable, use Data > Refresh All or right-click the output table and choose Refresh after source data updates.
Why should I store stock items in rows instead of adding project columns?
A fixed structure with stock items in rows is easier to automate with formulas, Power Query, and PivotTables. Adding new project columns often requires rebuilding lookups and is more difficult to maintain.
Which formula should I use to link project records to stock references?
Use VLOOKUP, INDEX/MATCH, or XLOOKUP with a key column such as Stock ID or Item Code. For example, =VLOOKUP(A2,StockTable,2,FALSE) returns the matching stock value in the project sheet.
How do I transfer wide columns to rows in WPS Office Excel?
Use Power Query to unpivot columns. In WPS Office Excel, go to Data > From Table/Range or Get Data, select the columns to convert, right-click, and choose Unpivot Columns. Then load the query to a new sheet.
What is the best way to create an expandable report from stock and project data?
Use a PivotTable based on the normalized data table. In WPS Office Excel, go to Insert > PivotTable, then drag the key field to Rows and the project or attribute fields to Columns or Values. Refresh the PivotTable after adding new records.




