WPS Spreadsheets: Filter Names and Numbers by Country to a New Sheet
Question details
The user needs to filter names and numbers by country and display the matching results on a separate spreadsheet without blank rows.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The source data is on Sheet1, with countries in column A, names in column C, and numbers in column E. The filtered results should appear on Sheet2.
- Observed behavior
- The goal is to return only rows where the Country value is USA and have the matching names and numbers spill automatically onto a new sheet.
Confirm that the source worksheet is named Sheet1 and that Country, Name, and Number data are stored in columns A, C, and E. Make sure the destination area on Sheet2 is empty so the FILTER results can spill into adjacent cells.
Use FILTER formulas on Sheet2 for USA names and numbers
Use this method when you want matching names and numbers to populate a new sheet automatically without inserting blank rows.
The FILTER function returns only the rows whose Country value matches USA. Because the formulas use full source columns, newly added matching records can be included automatically as the source range changes.
In WPS Spreadsheets, click the Sheet2 tab and select the cell where the filtered names should begin, such as A1.
Enter =FILTER(Sheet1!C:C,Sheet1!A:A="USA") in Sheet2, cell A1, and press Enter. The matching names will spill down automatically.
Select Sheet2, cell B1, and enter =FILTER(Sheet1!E:E,Sheet1!A:A="USA"). Press Enter to spill the corresponding numbers into column B.
Review the results on Sheet2 and keep the cells below and beside the formulas clear so WPS Spreadsheets can display every returned value.
Filter Country-Specific Data Easily in WPS Spreadsheets
WPS Spreadsheets lets you use the FILTER function to extract names and numbers by country onto a separate sheet. Its familiar spreadsheet interface supports dynamic spilled results and smooth editing of Excel-compatible workbooks.
- 1. Open your workbook: Launch WPS Office, choose Spreadsheets, and open the workbook containing the source data.
- 2. Create or select a new sheet: Click the plus button beside the sheet tabs or select an existing destination sheet such as Sheet2.
- 3. Enter the names formula: In the destination sheet, select A1 and enter =FILTER(Sheet1!C:C,Sheet1!A:A="USA").
- 4. Enter the numbers formula: Select B1 and enter =FILTER(Sheet1!E:E,Sheet1!A:A="USA") to display the matching numbers beside the names.
Frequently Asked Questions
Where should I enter the FILTER formulas in WPS Spreadsheets?
Enter the names formula on Sheet2 in A1 and the numbers formula in B1, or choose any two empty destination columns. Leave the surrounding cells empty for the results to spill.
Which columns do the formulas use for country, names, and numbers?
The formulas assume countries are in Sheet1 column A, names are in column C, and numbers are in column E. Change the column references if your data uses a different layout.
How do I filter a different country instead of USA?
Replace "USA" in both formulas with the required country, such as =FILTER(Sheet1!C:C,Sheet1!A:A="Canada"). Use the same country criterion in the names and numbers formulas.
Why are the filtered results not appearing?
Check that the source sheet is actually named Sheet1, the country text matches exactly, and the destination cells are empty. Existing values in the spill range can prevent the FILTER result from expanding.
