How to Transform, Sort, and Reorder Excel Table Data
Question details
The user needs to move source data into a destination table while sorting it by an identifier and reordering the columns dynamically.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Reorganizing source table data into a structured destination table with specific column arrangements for better data analysis.
- Observed behavior
- Data is successfully transformed, sorted by ID, and reordered by column using automated methods like formulas, macros, or queries.
Ensure your source data is formatted as a standard Table and that you have a clear understanding of your target column order before applying automated transformations.
Use Dynamic Array Functions
Best for fixed table locations where data updates automatically without needing to manually refresh or run a script.
Modern spreadsheet functions like SORT, CHOOSECOLS, and XLOOKUP allow you to dynamically extract and rearrange data. This method relies on the calculation engine to automatically spill the results into adjacent cells.
Locate your source data range and select the top-left cell of the destination range where the transformed data will appear.
Type the CHOOSECOLS function to rearrange the column order. For example, if you want the third column to appear before the second, input =CHOOSECOLS(A2:C10, 1, 3, 2).
Wrap the formula inside a SORT function to order the data by the identifier column. The updated formula will look like =SORT(CHOOSECOLS(A2:C10, 1, 3, 2), 1, 1).
Press Enter. The dynamic array formula will automatically spill the transformed, sorted, and reordered data into the destination table.

Apply a VBA Macro for Button-Based Workflows
Ideal for workflows requiring user interaction to select specific source and destination ranges dynamically on the fly.
Transform and Reorder using Power Query
The most robust method for handling large datasets that require repeatable transformation steps and periodic refreshing.
Easily Transform and Sort Tables with WPS Spreadsheet
WPS Spreadsheet supports dynamic array formulas, advanced VBA macro compatibility, and powerful data sorting tools, allowing you to transform and reorder complex tables effortlessly.
- 1. Open your dataset: Launch WPS Spreadsheet and open your target workbook containing the source data.
- 2. Apply dynamic formulas: Input modern dynamic array formulas like SORT and CHOOSECOLS directly into your destination cell to instantly reorder columns.
- 3. Save your progress: Save your reorganized data seamlessly as an .xlsx file without losing any formatting or formula compatibility.

Frequently Asked Questions
Can I reorder columns without using formulas or macros?
Yes. You can manually hold the Shift key while clicking and dragging the edge of a selected column header to reorder columns directly in the spreadsheet grid.
Why doesn't the Power Query result table update immediately when source data changes?
Power Query tables require a manual refresh to pull in new data. You must right-click anywhere inside the result table and click 'Refresh', or go to the Data tab and click 'Refresh All'.
Do dynamic array functions work in older spreadsheet software?
No, functions like SORT, CHOOSECOLS, and HSTACK are only available in modern spreadsheet applications like Microsoft 365, Excel 2021, and WPS Office. For older versions, you will need to use VBA or Power Query to achieve similar results.




