Data Import & Export

How to Transform, Sort, and Reorder Excel Table Data

Phi Hung VoPhi Hung Vo Nov 10, 2026 870 views

Question details

The user needs to move source data into a destination table while sorting it by an identifier and reordering the columns dynamically.

How to Transform, Sort, and Reorder Excel Table Data
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.
Before you start

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.

Solution 1Recommended

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.

1
Identify data ranges

Locate your source data range and select the top-left cell of the destination range where the transformed data will appear.

2
Apply CHOOSECOLS to reorder

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).

3
Nest within SORT

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).

4
Execute the formula

Press Enter. The dynamic array formula will automatically spill the transformed, sorted, and reordered data into the destination table.

Use Dynamic Array Functions
Automatic Updates: The destination table will automatically update whenever the source data is modified.
Advanced Data Handling in WPS Office

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. 1. Open your dataset: Launch WPS Spreadsheet and open your target workbook containing the source data.
  2. 2. Apply dynamic formulas: Input modern dynamic array formulas like SORT and CHOOSECOLS directly into your destination cell to instantly reorder columns.
  3. 3. Save your progress: Save your reorganized data seamlessly as an .xlsx file without losing any formatting or formula compatibility.
Fully compatible with Microsoft Excel file formats (.xlsx, .xlsm).Supports advanced dynamic array functions like SORT, XLOOKUP, and CHOOSECOLS.Built-in developer tools for running custom VBA macros.Free, lightweight, and features a familiar, easy-to-use interface.
microsoft office alternative - wps office

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.