How to Filter and Sort Excel Table Data into Multiple Sheets in WPS Spreadsheets
Question details
The user needs to split an Excel table of grant applications into separate groups, such as due within 90 days and submitted but awaiting a result, and sort each group by a relevant date.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- Managing grant applications in a structured Excel table and creating separate sheets for different applicant groups.
- Observed behavior
- The user wants to separate records by conditions and arrange each resulting group in date order without manually copying rows.
Ensure the source data is formatted as an Excel Table with unique column headers and that date columns are recognized as dates, not text. Identify the exact column names you will reference in FILTER and SORTBY formulas.
Use FILTER and SORTBY to split and sort Excel table data into multiple sheets
Best for dynamic results that update automatically whenever the source table changes.
This method uses structured references to keep formulas readable. Each output sheet can reference the source table, and WPS Spreadsheets recalculates the results after source data changes.
Open the workbook in WPS Spreadsheets, click the plus (+) icon next to the sheet tabs, and name the new sheet "Upcoming Grants".
Select the first output cell, for example A1, and enter =SORTBY(FILTER(Table1,Table1[Due Date]<=TODAY()+90),FILTER(Table1[Due Date],Table1[Due Date]<=TODAY()+90),1). Press Enter.
Add another sheet for submitted applications awaiting a result, for example "Awaiting Result".
In A1 of the new sheet, enter =SORTBY(FILTER(Table1,(Table1[Status]="Submitted")*(Table1[Result]="")),FILTER(Table1[Submission Date],(Table1[Status]="Submitted")*(Table1[Result]="")),1). Press Enter.
Replace Table1, Due Date, Status, Result and Submission Date with the actual names from your workbook. Add or remove groups by changing the criteria in each formula.
Create filtered copies with AutoFilter and sort manually
Use this when you prefer a manual one-time snapshot and do not need automatic updates.
Create dynamic grouped sheets in WPS Spreadsheets
Use WPS Spreadsheets to filter an Excel Table into multiple sheets with FILTER and SORTBY formulas, so each grant group sorts by its relevant date and updates automatically as the source table changes.
- 1. Open your workbook in WPS Spreadsheets: Launch WPS Office, open your Excel workbook, and confirm the source data is in an Excel Table.
- 2. Add destination sheets: Click the plus (+) icon near the sheet tabs and create sheets such as "Due Within 90 Days" and "Awaiting Result".
- 3. Enter dynamic FILTER and SORTBY formulas: In each destination sheet, enter the FILTER and SORTBY formulas that reference your table columns and date criteria.
- 4. Check and save: Verify that each group appears in the correct date order, then save the workbook in .xlsx format to keep Excel compatibility.
Frequently Asked Questions
Can FILTER and SORTBY be used with Excel Table structured references in WPS Spreadsheets?
Yes, WPS Spreadsheets supports FILTER and SORTBY, including structured references like Table1[Due Date], as long as the data is formatted as an Excel Table.
Why does my formula return #SPILL?
The destination range is not empty or the formula cannot spill. Clear the cells below and to the right of the formula cell so the filtered results have room.
How do I sort the filtered results by submission date instead of due date?
Replace both FILTER(Table1[Due Date],...) arguments in SORTBY with FILTER(Table1[Submission Date],...) and use the same criteria, then set the sort order to 1 for oldest first or -1 for newest first.
Can I add a third group for another condition?
Yes, create another sheet and use a SORTBY(FILTER(Table1,condition),FILTER(Table1[Relevant Date],condition),1) formula with the additional condition.
Will the separate sheets update automatically?
Yes, if you use FILTER and SORTBY formulas referencing the Excel Table, WPS Spreadsheets recalculates them when the source data changes.
