How to Return the First Nonblank Value from Multiple Excel Columns in WPS Spreadsheets
Question details
The user needs a formula that reviews a horizontal range, removes blank cells, and returns the first remaining value in column order.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A row contains values in multiple columns, and the first available value should be used ahead of later columns.
- Observed behavior
- The target range contains blank cells that must be ignored, so a direct cell reference cannot reliably return the first value.
Open the workbook in WPS Spreadsheets and identify the horizontal range to evaluate, such as B2:D2. Confirm that the blank cells are truly empty and not spaces or formulas returning "".
Use the FILTER function to return the first nonblank value
Use this when your version of WPS Spreadsheets or Microsoft 365 supports dynamic arrays and the FILTER function.
The FILTER function removes blank cells from the range. Placing the @ symbol before FILTER uses implicit intersection to return only the first item from the filtered result instead of spilling all values.
Click the cell where you want the first nonblank value to appear, for example E2.
Type =@FILTER(B2:D2,B2:D2<>"") into the formula bar or the selected cell.
Press Enter to confirm. WPS Spreadsheets displays the first nonblank value from B2:D2.
Use INDEX and MATCH as a compatibility alternative
Use this if the FILTER function is unavailable or you need a traditional lookup formula.
Return the first nonblank value with WPS Spreadsheets
WPS Spreadsheets supports Excel-compatible formulas such as FILTER, INDEX, and MATCH, so you can build the first-nonblank logic directly in your worksheet without switching to another spreadsheet app.
- 1. Open the workbook: Launch WPS Spreadsheets and open the Excel file that contains the columns to evaluate.
- 2. Select the target cell: Click the cell where you want the first nonblank result to appear.
- 3. Enter the formula: Type =@FILTER(B2:D2,B2:D2<>"") and adjust the range to match your data.
- 4. Confirm the result: Press Enter and verify that the displayed value is the first nonblank cell in the row.
- 5. Save the workbook: Save the file as .xlsx to keep the formula usable when the workbook is opened in Microsoft Excel.
Frequently Asked Questions
What does the @ symbol do in the FILTER formula?
The @ symbol uses implicit intersection to return only the first item from the filtered array. Without it, the formula may spill multiple nonblank values into adjacent cells.
Can I use this formula for a vertical column instead of a horizontal row?
Yes. Replace the horizontal range B2:D2 with a vertical range such as B2:B10. The FILTER formula and the INDEX/MATCH alternative work the same way as long as the range direction is consistent.
What happens if all cells in the range are blank?
The FILTER example returns an empty string when all cells in the range are blank, so no error is displayed. If you need a custom fallback, wrap the formula in IFERROR or test the range separately.
Is this formula compatible with Microsoft Excel?
The FILTER formula is compatible with Microsoft 365 and newer Excel versions. If the workbook may be opened in older Excel versions, use the INDEX and MATCH alternative or replace the formula before sharing.
Why does my formula display #SPILL! in WPS Spreadsheets?
#SPILL! usually appears when the formula tries to return multiple values but nearby cells are not empty. Adding the @ symbol before FILTER or clearing the adjacent cells resolves the spill behavior.
