How to Use FILTER to Split Master Excel Data by Supplier in WPS Office Spreadsheets
Question details
The user wants to extract all rows for a selected supplier from a master Excel table into separate worksheets without changing the source data, so the supplier sheets remain synchronized with the master table.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A master Excel table contains a Supplier column, and the user needs dedicated worksheets for individual suppliers that update automatically.
- Observed behavior
- The goal is for a formula to return all matching supplier rows on separate worksheets while the master table remains unchanged.
Make sure the master data is formatted as an Excel table with a Supplier column, and confirm that your WPS Office version supports dynamic array functions such as FILTER.
Use the FILTER Function with a Named Excel Table
This method gives you a live supplier split that updates automatically when the master table changes.
The FILTER function returns every column from the table for rows that match the supplier condition. Because the formula references a structured Excel table, WPS Spreadsheets can expand or contract the result automatically.
Select the master data range, go to the Insert tab, click Table, confirm the range has headers, and name the table Data from the Table Design tab.
Add a new worksheet, rename it to the supplier name such as ACME, and select cell A2 where the results should start.
Type =FILTER(Data,Data[Supplier]="ACME","") in A2 and press Enter. Replace ACME with the exact supplier name from the Supplier column.
Add, edit, or remove rows in the master table and switch back to the supplier worksheet to confirm the filtered rows update automatically.
Split Excel Data by Supplier with WPS Office Spreadsheets
Use WPS Office Spreadsheets to open your Excel workbook, apply the FILTER function to a named table, and create supplier-specific sheets that update automatically without changing the source data.
- 1. Download WPS Office: Go to the official WPS Office website, select Download WPS Office Free, and install the latest version for your device.
- 2. Open the master Excel workbook: Launch WPS Office Spreadsheets, click Menu > Open, and choose your Excel workbook containing the master supplier data.
- 3. Create a named table: Select the master data range, click Insert > Table, confirm the headers, and name the table Data in the Table Design tab.
- 4. Apply the FILTER formula: On a new supplier sheet, select A2 and enter =FILTER(Data,Data[Supplier]="ACME",""). Replace ACME with the supplier name.
- 5. Save and reuse: Press Ctrl+S or click Menu > Save to keep the workbook in an Excel-compatible format, then add more supplier sheets as needed.
Frequently Asked Questions
Why does my FILTER formula return a #SPILL! error in WPS Spreadsheets?
This means one or more cells in the output spill range already contain data. Clear the cells directly below and to the right of the formula, then press Enter again.
Do I need Microsoft 365 to use the FILTER function in WPS Office?
No. WPS Office Spreadsheets supports dynamic array functions such as FILTER in compatible versions, so you can use the same formula without a Microsoft 365 subscription.
Will the supplier sheets update when I add new rows to the master table?
Yes. Because the formula references the Excel table by name, WPS Spreadsheets recalculates the FILTER result automatically when rows are added, edited, or removed from the Data table.
Can I split multiple suppliers into separate sheets at once?
You can create one supplier sheet at a time and enter a FILTER formula with each supplier name. For a different supplier, use the same formula and replace the supplier name in the condition.
What should I do if a supplier name contains spaces or special characters?
Enclose the supplier name exactly as it appears in the Supplier column inside double quotation marks, for example =FILTER(Data,Data[Supplier]="Acme Corp","").
