Combine a Reusable Text Block with Every Value in Excel Using Dynamic Formulas
Question details
The user needs a dynamic Microsoft 365 formula that combines a fixed reusable configuration template with each nonblank value in another column and spills one complete configuration block per value.
- Product
- Microsoft Excel (Microsoft 365)
- Device & OS
- not provided
- Scenario
- The user wants to generate repeated configuration blocks from a reusable text template and a column of input values without manually copying the block for each value.
- Observed behavior
- Each nonblank input value should produce one complete configuration block, with the value appended to the template's first line, and the results should spill dynamically.
Confirm that your Excel version has LET, LAMBDA, VSTACK, REDUCE, TAKE, and DROP functions. Define a named range or clear reference for the reusable text block, and make sure the input column has no blank cells between nonblank values.
Create a Dynamic Spill Formula with LET, LAMBDA, VSTACK, and REDUCE
Use this when your Excel version supports Microsoft 365 dynamic array functions and you want one formula that spills all configuration blocks automatically.
This method builds a reusable LAMBDA function inside LET. REDUCE iterates over only the nonblank input values, and VSTACK stacks each generated block into a single spilled range.
Select the fixed configuration template cells and create a named range such as template, or note the exact range reference. The first row is used as the line that receives each value.
Click the cell where the spill should begin, paste the formula =LET(updateBlock,LAMBDA(template,value,VSTACK(TRIM(TAKE(template,1))&value,DROP(template,1))),REDUCE(updateBlock(template,B1),$B$2:INDEX(B:B,COUNTA(B:B)),LAMBDA(a,v,VSTACK(a,updateBlock(template,v))))), and press Enter.
Replace template with the actual text block range and update B1, $B$2, and B:B if the input column is different. Confirm that the output area is empty and that the input column contains only one continuous set of nonblank values.
Check that each nonblank value creates one complete configuration block and that the results appear as a single spilled range without a #SPILL! error.
Prefer a Free Alternative for Everyday Spreadsheet Tasks? Try WPS Office
WPS Office gives you a free, lightweight spreadsheet application with familiar Excel-compatible features. While this specific LAMBDA/REDUCE spill formula targets Microsoft 365, WPS Spreadsheets is a practical choice for daily workbooks, reports, and data entry with strong Microsoft Office format compatibility.

Frequently Asked Questions
Does this formula work in all versions of Excel?
No, it requires Microsoft 365 versions that support LET, LAMBDA, VSTACK, REDUCE, TAKE, and DROP. In older Excel versions, use helper columns or check WPS Office for spreadsheet functions that match your version.
Why does the formula return a #SPILL! error?
The spill range is blocked by existing data. Clear all cells where the output should appear and make sure the input column has no blank cells between nonblank values.
How do I adjust the formula if the input values start in a different column?
Replace B1 with the first value cell and replace $B$2:INDEX(B:B,COUNTA(B:B)) with the corresponding range, such as $C$2:INDEX(C:C,COUNTA(C:C)), for column C.
What part of the formula appends each value to the template?
Inside LAMBDA, TRIM(TAKE(template,1))&value appends the current value to the first template line, while DROP(template,1) keeps the remaining template rows before VSTACK restacks the block.
Can I use this exact formula in WPS Spreadsheets?
This exact formula is designed for Microsoft 365 Excel. WPS Spreadsheets supports many Excel-compatible functions and workbook files, but check the supported function list in WPS Spreadsheets before relying on LAMBDA, REDUCE, or VSTACK.




