Automatically Populate a Smaller Excel Table from YES Rows in WPS Office
Question details
Create a smaller Excel table that automatically pulls only rows where a data-validation field equals YES and returns selected columns from a larger source table.
- Product
- WPS Office Spreadsheets (Excel-compatible)
- Device & OS
- not provided
- Scenario
- The user maintains a large source table and needs a filtered summary table that updates when the YES/NO validation values change.
- Observed behavior
- The result table should show only YES rows and only the chosen columns, without manually copying or filtering every time.
Make sure the source table has a consistent data-validation column containing YES/NO values and that the columns you want to return are named or positioned correctly. If using Power Query, open the workbook in WPS Office Spreadsheets or Excel and confirm the source range is formatted as an Excel Table.
Use Power Query to filter YES rows and keep selected columns
Best when you want a refreshable query result that updates with one click after source data changes.
Power Query lets you transform the large source table without altering the original data. The query filters only rows where the data-validation column equals YES, then removes all columns except the ones you need.
After you load the result table to a new worksheet, you can refresh it whenever the source changes.
Select any cell inside the large source table, go to the Data tab, and click From Table/Range or Get Data > From Table/Range.
In the Power Query Editor, click the filter arrow on the data-validation column, select Text Filters > Equals, enter YES, and confirm.
Hold Ctrl and select the columns you want to keep, such as B, F, G, N, O, and P, right-click a selected header, and choose Remove Other Columns.
Click Close & Load To, choose Table and a new worksheet, then click OK to create the smaller table.
Right-click the result table and choose Refresh, or use Data > Refresh All after updating the source validation values.
Use the FILTER function for an automatically updating result
Use this method when your Excel version supports dynamic array formulas and you want the smaller table to update immediately without manual refresh.
Create a YES-Filtered Summary Table in WPS Office Spreadsheets
Use WPS Office Spreadsheets to load the source table into Power Query, filter YES rows, keep only the required columns, and output a smaller table that refreshes on demand. If your version supports dynamic arrays, you can also use the FILTER function for automatic updates.
- 1. Open the source workbook in WPS Office Spreadsheets: Launch WPS Office, open the Excel file containing the large source table, and click a cell inside the table.
- 2. Create a query or formula-based filter: Use Data > From Table/Range to open Power Query, or enter a FILTER formula in a blank worksheet if dynamic arrays are supported.
- 3. Keep YES rows and selected columns: In Power Query, filter the validation column to YES and choose Remove Other Columns. In a formula, wrap FILTER with CHOOSECOLS to select columns B, F, G, N, O, and P.
- 4. Load or confirm the result table: Load the query to a new worksheet and refresh it when needed, or press Enter after a formula to let the results spill automatically.
Frequently Asked Questions
Can I automatically update the smaller table when new YES rows are added?
Yes. With Power Query, refresh the result table or use Data > Refresh All after changing the source. With the FILTER formula in Microsoft 365, Excel 2021, or a WPS version that supports dynamic arrays, the result updates immediately.
How do I return only columns B, F, G, N, O, and P?
In Power Query, select those columns in the Power Query Editor, right-click a selected header, and choose Remove Other Columns. In a formula, use CHOOSECOLS with the column indexes 2,6,7,14,15,16, or use the actual column names.
What if my WPS Office version does not show From Table/Range in the Data tab?
Use a dynamic array formula such as FILTER and CHOOSECOLS if supported, or create the result table with conventional filtering and copy selected columns. Updating to the latest WPS Office version can also provide broader Excel-compatible features.
Why does my FILTER formula return #NAME? or #SPILL?
#NAME? means FILTER is not available in your current version. #SPILL! occurs when cells in the output range are not empty; clear the blocking cells so the array can spill.
Can this work when the data-validation column contains YES or blank values instead of YES/NO?
Yes. Set the filter condition to YES; blank or other values are excluded. If blanks should be treated differently, add another condition in Power Query or adjust the FILTER formula.
