How to Stack and Sort Data from Multiple Excel Worksheets in WPS Office
Question details
The user wants to combine matching ranges from several worksheets, exclude rows that have blank keys, and sort the combined data with dynamic-array formulas.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A workbook contains multiple worksheets with matching ranges that need to be consolidated into one sorted list.
- Observed behavior
- Manual copying and sorting would be time-consuming; the user needs a single dynamic formula to stack, filter, and sort the data.
Verify that all source worksheets use the same layout and range size, and confirm that your version of WPS Office Spreadsheets supports dynamic array functions such as VSTACK, FILTER, SORT, and LET.
Stack, Filter, and Sort Data with VSTACK, FILTER, and SORT in WPS Office Spreadsheets
Use this method when you need one dynamic array that consolidates identical ranges from multiple worksheets, removes blank rows, and returns a sorted result.
VSTACK accepts a 3D reference such as Sheet1:Sheet3!A1:F100 to append the same range from each sheet. FILTER removes rows where the first column is empty, and SORT orders the filtered array automatically.
Open the workbook in WPS Office Spreadsheets and click the cell where you want the stacked and sorted list to begin.
Type =LET(stacked,VSTACK(Sheet1:Sheet3!A1:F100),filtered,FILTER(stacked,TAKE(stacked,,1)<>""),SORT(filtered)) and press Enter. Adjust the sheet names and range to match your workbook.
If the formula returns a #SPILL! error, clear the cells that block the output range so the dynamic array can spill correctly.
Use WPS Office Spreadsheets to Stack and Sort Multiple Worksheet Data
WPS Office Spreadsheets supports VSTACK, FILTER, SORT, and LET, so you can consolidate and sort data from multiple worksheets without copying manually. It is free, lightweight, and compatible with Excel files and formula workflows.
- 1. Open your workbook: In WPS Office Spreadsheets, open the workbook that contains the worksheets you want to combine.
- 2. Select the output cell: Click the cell where the stacked and sorted list should begin.
- 3. Enter the formula: Type =LET(stacked,VSTACK(Sheet1:Sheet3!A1:F100),filtered,FILTER(stacked,TAKE(stacked,,1)<>""),SORT(filtered)), adjust the sheet names and range, and press Enter.
- 4. Review the spilled array: Ensure the output area is clear so the dynamic array can spill, then edit the sort column or range if needed.
Frequently Asked Questions
Can I use a 3D reference directly in SORT?
No, SORT does not accept a 3D reference such as Sheet1:Sheet3!A1:F100. Use VSTACK to stack the sheets first, then wrap the result in SORT.
How do I remove blank rows from the stacked data?
Wrap VSTACK in FILTER and use TAKE(stacked,,1)<>"" to keep only rows where the first column is not empty.
What should I do if the formula returns a #SPILL! error?
Clear the cells to the right and below the formula that are inside the expected spill range, because dynamic arrays need an empty spill area.
Can I sort by a different column?
Yes. Replace SORT(filtered) with SORT(filtered,2,1) or another column index to sort by the column you want.
Does this work in WPS Office Spreadsheets?
Yes. WPS Office Spreadsheets supports dynamic array functions such as VSTACK, FILTER, SORT, and LET, and saves workbooks in Excel-compatible .xlsx format.
