How to Create a List in WPS Spreadsheets While Ignoring Blank Cells with Formulas
Question details
The user needs a formula-only list from one column in WPS Spreadsheets, excluding rows where a corresponding cell in a second column is blank.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has a source range and wants a compact list of values from column A only when the corresponding cell in column B is not empty, without manually filtering or using VBA.
- Observed behavior
- The resulting list should automatically skip blank-related rows and update when the source data changes.
Open the WPS Spreadsheets workbook and confirm which column contains the values to return and which column contains the blank or non-blank condition. Make sure the formula output area is empty because a dynamic array formula may spill automatically.
Use FILTER to Return Non-Blank Matches in WPS Spreadsheets
Best when your version of WPS Spreadsheets or Excel supports dynamic arrays; it creates the compact list with one formula and updates automatically.
The FILTER function returns all rows from A2:A25 where B2:B25 is not empty. Because it is a dynamic array formula, it spills results into the adjacent cells automatically.
Click the first cell where you want the new list to start, such as D2.
Type =FILTER(A2:A25,B2:B25<>"") and press Enter.
Check that the returned values appear in a continuous range and that rows with blanks in column B are excluded.
Use IFERROR, INDEX, SMALL and IF for Older WPS Spreadsheets or Excel Versions
Use this array formula when FILTER is not available or you need compatibility with legacy workbook versions.
Build a Formula-Only List in WPS Spreadsheets That Ignores Blank Cells
WPS Spreadsheets lets you create a compact, formula-only list from one column while skipping rows where a second column is blank. The formula updates automatically and supports Excel-compatible workbook formats.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, open the workbook, and locate the source columns: values in column A and the blank or non-blank check in column B.
- 2. Select the output area: Click the first cell where the compact list should start, such as D2, and make sure the spill area is clear if you use a dynamic array formula.
- 3. Enter the formula: Type =FILTER(A2:A25,B2:B25<>"") in modern WPS Spreadsheets, or use the IFERROR/INDEX/SMALL array formula for older versions, then confirm as required.
- 4. Adjust the source range: Change A2:A25 and B2:B25 to match the actual data range, then press Enter or Ctrl+Shift+Enter depending on the formula version.
- 5. Verify the list: Confirm the compact list excludes blank-related rows and updates when source values change.
Frequently Asked Questions
Can I use the FILTER formula in every version of WPS Spreadsheets?
FILTER is available in WPS Spreadsheets versions that support dynamic arrays. If the formula returns #NAME?, use the IFERROR/INDEX/SMALL array formula and confirm it with Ctrl+Shift+Enter.
Why does my formula return #NAME? or #VALUE! in WPS Spreadsheets?
This usually means the function is not available in your version or the array formula was entered as a normal formula. Check for FILTER support or select the cell, press F2, and then press Ctrl+Shift+Enter.
How do I adjust the formula if my data starts in a different row?
Replace $A$2:$A$25 and $B$2:$B$25 with your actual ranges, and update ROW($A$2:$A$25)-1 so the subtraction value is one less than your starting row number.
Can the formula update automatically when I add new data?
Yes, if your range includes the new rows or you convert the source data to a WPS Spreadsheets table. With dynamic array formulas, the returned list spills automatically.
Do I need a helper column or VBA to create this list?
No, the formula-only method works without helper columns or macros. Use FILTER or the IFERROR/INDEX/SMALL array formula to exclude blank cells.
