How to Filter Excel Data by Multiple Criteria and Copy Results to Another Sheet in WPS Office
Question details
The user needs to filter Excel data by multiple criteria, such as state, supplier, lender, and equipment type, and copy the matching rows to another sheet using Advanced Filter.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user manages structured records with fields including state, supplier, lender, and equipment type, and wants to extract matching subsets into a separate worksheet without altering the source data.
- Observed behavior
- The desired result is a reusable criteria range and Advanced Filter operation that copies only the rows matching the specified conditions to another sheet.
Prepare the source data with unique column headers and create a destination sheet where the filtered rows will be copied. Keep the criteria range in the same workbook and use exact header labels from the source data.
Use Advanced Filter with a Criteria Range to Copy Matching Rows
Use this method when you need multiple, reusable criteria and want extracted rows copied to a separate sheet without changing the source data.
Advanced Filter supports AND/OR conditions: values entered in the same criteria row must all be true, while values entered in different rows work as OR alternatives.
This method is suitable for filtering by fields such as state, supplier, lender, and equipment type and for rerunning the filter after updating criteria.
In the same workbook, type the same column headers as your source data, such as State, Supplier, Lender, and Equipment Type. Under each header, enter the conditions you want to match. Use the same row for AND conditions and different rows for OR conditions.
Click any cell inside the source data range, go to the Data tab, and click Advanced Filter.
In the Advanced Filter dialog, select Copy to another location. Confirm that the List range shows your source data range.
Click the Criteria range box and select the criteria range you created. Then click the Copy to box, select the destination sheet tab, and choose the top-left cell where the copied rows should appear.
Click OK. WPS Spreadsheets copies only the rows that meet the criteria to the destination location.
Filter Multiple Criteria and Copy Results in WPS Spreadsheets
WPS Spreadsheets lets you create a criteria range, run Advanced Filter from the Data tab, and copy only the matching rows to another worksheet—without writing formulas or macros.
- 1. Open WPS Office Spreadsheets: Launch WPS Office, open the workbook that contains the source Excel data, and make sure a blank destination sheet is available.
- 2. Create the criteria range: In the workbook, add the column headers to a new area and enter the conditions under each header. Keep AND conditions in the same row and OR conditions in separate rows.
- 3. Open Advanced Filter: Click a cell in the source data, go to the Data tab, and click Advanced Filter.
- 4. Set the filter options: Select Copy to another location, confirm the List range, select the Criteria range you created, and choose the destination top-left cell on another sheet.
- 5. Run and review the results: Click OK to copy the matching rows, then review the destination sheet to verify the filtered data.
Frequently Asked Questions
Where is Advanced Filter in WPS Spreadsheets?
Open your workbook, click the Data tab, and select Advanced Filter. You can then choose Copy to another location and specify the list range, criteria range, and destination.
How do I filter by more than one condition at the same time?
Create a criteria range with the same column headers as your data. To require all conditions, type them in the same row under the matching headers; to apply OR logic, place each alternative in a separate row.
Can I copy the filtered rows to a different worksheet?
Yes. In the Advanced Filter dialog, choose Copy to another location, click the Copy to box, then select the destination sheet tab and click the cell where you want the results to start.
How do I refresh the results after changing the criteria?
Advanced Filter copies static values, so it does not update automatically. Update the criteria range, open Data > Advanced Filter again, set the same destination, and click OK to replace the previous results.
What should I check if the filter returns no rows or wrong results?
Make sure the criteria headers exactly match the source data headers and that the criteria range does not include blank rows between the headers and conditions. Also confirm the List range covers all source data and the correct criteria range is selected.
