Function Problems

How to Rearrange Excel Table Data into a New Layout in WPS Spreadsheet

Huda QurayshiHuda Qurayshi Nov 13, 2026 870 views

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.

How to Rearrange Excel Table Data into a New Layout in WPS Spreadsheet
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.
Before you start

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.

Solution 1Recommended

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.

1
Select the target cell

Click an empty cell in your WPS Spreadsheet worksheet where you want the rearranged table to start.

2
Enter the dynamic array formula

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.

3
Confirm the formula

Press Enter and let WPS Spreadsheet spill the rearranged table into the adjacent cells.

Reshape a Table with TOCOL, WRAPROWS, and SORTBY in WPS Spreadsheet
Check your source range: Make sure array matches the exact source range and that enough blank cells are available for the spill range, otherwise you may see a #SPILL! error.
Formula-based layout fix

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. 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. 2. Select a blank spill area: Click an empty cell in the worksheet where the new layout can expand without overlapping existing data.
  3. 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. 4. Apply and review: Press Enter, review the rearranged table, and adjust the group, rank, or test parameters to match the desired layout.
Reshape large table layouts with TOCOL, WRAPROWS, SORTBY, and VSTACK formulasFull compatibility with Microsoft Excel .xlsx and .xls filesFree alternative that keeps the familiar spreadsheet formula workflowLightweight and easy-to-use interface with dynamic array spill ranges
microsoft office alternative - wps office

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.