How to Rearrange Excel Table Data into a New Layout in WPS Spreadsheet
Question details
The user needs to reshape a large Excel table into a new row and column layout when a simple Transpose does not produce the required arrangement, using Excel 365-style formulas such as TOCOL, WRAPROWS, SORTBY, and VSTACK.

- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- The user is working with a large table in a spreadsheet and needs to restructure it from one layout into a specific new layout, where Transpose alone does not create the required result.
- Observed behavior
- A formula-based layout transformation that arranges the values into the desired rows and columns.
Open the workbook in WPS Spreadsheet and confirm that your version supports Excel 365 dynamic array functions such as TOCOL and WRAPROWS. Identify the source range and the exact number of groups, ranks, or tests so you can adjust the formula parameters.
Reshape a Table with TOCOL, WRAPROWS, and SORTBY in WPS Spreadsheet
Use this formula when the source table contains grouped columns—for example 2 groups, 3 ranks, and 12 tests—and Transpose alone cannot produce the staggered target layout.
The LET function stores groupCount, rankCount, and testCount as named values, TOCOL reads the source range by columns, WRAPROWS wraps the values into rows, and SORTBY reorders those rows into the final layout.
Click an empty cell in your WPS Spreadsheet worksheet where you want the rearranged table to start.
Type =LET(groupCount,2,rankCount,3,testCount,12,listCols,TOCOL(array,,1),shapedArray,WRAPROWS(listCols,testCount*rankCount),sortArray,1+MOD(SEQUENCE(1,testCount*rankCount,0),testCount),SORTBY(shapedArray,sortArray)) and replace array with your source range.
Press Enter and let WPS Spreadsheet spill the rearranged table into the adjacent cells.

Stack Two Equal-Width Sections with VSTACK and TOROW
Use this shorter formula when the source table consists of two equal-width sections and you need to combine them into a stacked row layout.
Rearrange Table Layouts in WPS Spreadsheet with Excel-Compatible Formulas
Open your workbook in WPS Spreadsheet and use Excel 365-compatible dynamic array formulas—LET, TOCOL, WRAPROWS, SORTBY, and VSTACK—to reshape large tables into the exact row and column layout you need without manual copy-paste.
- 1. Open your workbook in WPS Spreadsheet: Launch WPS Office, click Spreadsheet, and open the Excel file that contains the table you need to rearrange.
- 2. Select a blank spill area: Click an empty cell in the worksheet where the new layout can expand without overlapping existing data.
- 3. Enter a reshaping formula: Type the LET/TOCOL/WRAPROWS/SORTBY or LET/VSTACK formula shown above and replace array with your actual source range.
- 4. Apply and review: Press Enter, review the rearranged table, and adjust the group, rank, or test parameters to match the desired layout.

Frequently Asked Questions
Does WPS Spreadsheet support Excel 365 formulas like TOCOL and WRAPROWS?
Yes, WPS Spreadsheet supports Excel-compatible dynamic array formulas in compatible versions. Open the workbook, click an empty cell, type the formula, and press Enter to spill the result. If a function is not recognized, update WPS Office or check your version's formula support.
Why is Transpose not enough for rearranging table data?
Transpose only rotates rows and columns, but it does not regroup, reorder, or wrap values into a different structure. Use WRAPROWS and SORTBY or VSTACK to create a new custom layout in WPS Spreadsheet.
What should I do if the formula returns a #SPILL! error?
Clear or move any data that occupies cells in the intended spill range. Then select the formula cell, press Enter again, and WPS Spreadsheet will spill the reshaped table into the empty area.
Can I adjust the formula for a different number of groups or tests?
Yes. In the LET formula, change groupCount, rankCount, and testCount to match your source layout, then press Enter. For two equal-width sections, use the VSTACK/TOROW version and set n to half the source columns.




