How to Dynamically Filter, Sort, and Reorder Excel Table Columns
Question details
Extract selected columns from a table, filter out blank values, reorder the columns, and sort the result dynamically.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Reorganizing data from a larger dataset by displaying only specific columns, removing empty records, and automatically sorting them without manual copying.
- Observed behavior
- The user requires a dynamic array formula combination to achieve custom column extraction, filtering, and sorting in one continuous operation.
Ensure you are using a modern version of your spreadsheet software that supports dynamic array functions like SORT, FILTER, and CHOOSECOLS.
Use SORT, FILTER, and CHOOSECOLS Functions
Combine three dynamic array functions to extract your desired columns, filter out blank rows, and sort the remaining data.
This method is highly efficient because it leverages nested dynamic arrays. CHOOSECOLS pulls out specific columns and places them in your preferred order, FILTER removes rows based on empty values, and SORT organizes the final output.
Select the cell where you want your new dynamic table to start. Note the name or range of your source data (e.g., 'DataTable') and the index number of the column you want to check for blanks.
Type the core formula to extract columns and filter blanks: =FILTER(CHOOSECOLS(DataTable,5,3,1), CHOOSECOLS(DataTable,5)<>""). This extracts columns 5, 3, and 1, but ignores rows where column 5 is blank.
Enclose your formula within the SORT function to order the results alphabetically or numerically: =SORT(FILTER(CHOOSECOLS(DataTable,5,3,1), CHOOSECOLS(DataTable,5)<>"")). Press Enter to populate the dynamic table.
Use LET and GROUPBY Functions
An alternative approach for organizing data using LET to define variables and GROUPBY to aggregate and sort.
Process Dynamic Data Efficiently with WPS Spreadsheet
WPS Office fully supports dynamic array functions like FILTER, SORT, and CHOOSECOLS, allowing you to manipulate complex tables seamlessly and for free.
- 1. Open WPS Spreadsheet: Launch WPS Office and open your .xlsx workbook containing the source table.
- 2. Select the target cell: Click the empty cell where you want the dynamically sorted and filtered array to spill into.
- 3. Enter the dynamic formula: Input your nested formula, such as =SORT(FILTER(CHOOSECOLS(DataTable,5,3,1),CHOOSECOLS(DataTable,5)<>"")), and press Enter to instantly see your reordered table.
Frequently Asked Questions
What does the CHOOSECOLS function do?
The CHOOSECOLS function returns specific columns from an array or a range based on the column index numbers you provide. It allows you to both extract and completely reorder columns from your original dataset.
Why am I getting a #CALC! error when using the FILTER function?
The #CALC! error usually appears if the FILTER function evaluates to an empty array, meaning no rows meet your specified condition (e.g., all rows are blank in the checked column). You can add the optional [if_empty] argument in the FILTER function to display a custom message instead of an error.
Are dynamic array functions available in older spreadsheet versions?
No, dynamic array functions like SORT, FILTER, and CHOOSECOLS require modern spreadsheet software. They will not work in legacy versions like Excel 2016 or 2019. You must use Microsoft 365, Excel 2021, or compatible modern alternatives like WPS Office.
