WPS Spreadsheets: Fill Blank Cells Down and Up with LAMBDA and SCAN
Question details
The user needs a dynamic-array formula that fills blank cells with nearby nonblank values across one or more columns.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A worksheet contains blank cells that should inherit the most recent nonblank value, similar to Power Query Fill Down or Fill Up.
- Observed behavior
- Blank cells remain empty until a LAMBDA and SCAN formula is applied to propagate values through the selected data range.
Confirm that your WPS Spreadsheets version supports dynamic-array functions such as LET, LAMBDA, SCAN, and, where available, BYCOL. Identify the source range and decide whether blank cells should inherit the previous value, the next value, or both.
Fill blanks down in one column with LET and SCAN
Use this formula when each blank cell should receive the most recent nonblank value above it in a single column.
The formula initializes SCAN with the first nonblank value in the range, then replaces each blank with the previous accumulated value. Existing nonblank values remain unchanged.
Click the first cell where you want the filled results to appear, such as B2, leaving the original source range in column A unchanged.
Enter =LET(initial,TAKE(TOCOL(A2:A100,3),1),SCAN(initial,A2:A100,LAMBDA(previous,current,IF(current="",previous,current)))) and press Enter.
Check the spilled array below the formula cell. Blank entries in A2:A100 should display the most recent nonblank value above them.
Apply the fill-down logic across multiple columns
Use this approach when several columns need the same fill-down treatment and your WPS version supports BYCOL or an equivalent column iterator.
Fill gaps using a helper formula when advanced functions are unavailable
Use a helper column when your WPS version does not support LAMBDA, SCAN, or dynamic-array formulas.
Fill Blank Cells Across Columns with WPS Spreadsheets Formulas
WPS Spreadsheets can complete this workflow with LET, LAMBDA, SCAN, and supported dynamic-array functions. Create a single-column fill function, apply it to your data range, and keep the results compatible with common Excel workbook formats.
- 1. Open the workbook: Launch WPS Office, choose Spreadsheets, and open the workbook containing the blank cells.
- 2. Check formula support: Select an empty cell and test a supported function such as =LET(x,1,x). Confirm that the formula calculates before building the full solution.
- 3. Enter the fill-down formula: Click the output cell and enter =LET(initial,TAKE(TOCOL(A2:A100,3),1),SCAN(initial,A2:A100,LAMBDA(previous,current,IF(current="",previous,current)))).
- 4. Apply the logic to multiple columns: For supported versions, enter =BYCOL(A2:D100,LAMBDA(column,LET(initial,TAKE(TOCOL(column,3),1),SCAN(initial,column,LAMBDA(previous,current,IF(current="",previous,current)))))) in the output area.
Frequently Asked Questions
How do I fill blank cells with the previous value in WPS Spreadsheets?
Select an output cell and enter =LET(initial,TAKE(TOCOL(A2:A100,3),1),SCAN(initial,A2:A100,LAMBDA(previous,current,IF(current="",previous,current)))). Change A2:A100 to your source range.
Can I fill blanks across several columns at once?
If your WPS version supports BYCOL and dynamic arrays, apply the single-column logic with =BYCOL(A2:D100,FILLDOWN), where FILLDOWN is the reusable LAMBDA. Otherwise, enter the single-column formula separately for each column.
What does SCAN do in the fill-down formula?
SCAN processes the range sequentially and carries forward an accumulated value. The LAMBDA keeps the current cell when it is nonblank and uses the previous accumulated value when the current cell is blank.
How can I fill blanks upward instead of downward?
Reverse the range, apply the fill-down logic, and reverse the result again. In practice, a formula using SORTBY or an equivalent reverse-order method can perform the upward pass before restoring the original row order.
Why does my WPS formula return an unsupported-function error?
Your WPS version may not support LET, LAMBDA, SCAN, TOCOL, or BYCOL. Use the helper-column method with =IF(A2<>"",A2,B1), copy it down, and paste the results as values.
