Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to write a recursive LAMBDA function in Excel and Google Sheets
Topic:LAMBDA (reusable formulas)Excel & Google Sheets
=LAMBDA(n,IF(n<=1,1,n*LAMBDA(n,IF(n<=1,1,n*1))(n-1)))(5)Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1To create a named recursive LAMBDA in Excel, go to Formulas > Name Manager > New. Name it FACT, and in 'Refers to' enter: =LAMBDA(n,IF(n<=1,1,n*FACT(n-1))). Click OK.
- 2In any cell call it with =FACT(5) — the named LAMBDA calls itself recursively until n reaches 1.
- 3In Google Sheets, go to Data > Named functions > Add new function. Name it FACT, define the argument n, and enter IF(n<=1,1,n*FACT(n-1)) as the formula body.
- 4Inline (without naming), LAMBDA can only self-reference one level deep using nested LAMBDA calls. For true recursion, the named approach is required.
- 5Test with small values (n=5 gives 120, n=6 gives 720) and add a guard: IF(n>170,"overflow",IF(n<=1,1,n*FACT(n-1))) to prevent Excel overflow.
Tips
- Excel has no native recursion depth limit for LAMBDA, but it does have a 32,767 iteration cap on volatile recalculation chains — keep recursive depth practical (under a few hundred).
- Google Sheets Named Functions support recursion natively; inline LAMBDA recursion without naming is not supported in Sheets.
- For recursive tree traversal or path-finding, consider MAP and REDUCE as iterative alternatives before reaching for full recursion.
Need it for your exact data?
Describe your columns in plain English and get the precise formula for your sheet, with the right Excel or Sheets syntax.
Frequently asked
Can I use LAMBDA recursion without the Name Manager?
In Excel, true recursion requires a named LAMBDA because the function must reference itself by name. An inline LAMBDA has no name to recurse on. In Google Sheets, you must use Named Functions for the same reason.
What happens if the recursion has no base case?
Excel returns a #VALUE! or #CALC! error after hitting the recalculation limit. Always include an IF base case (e.g., IF(n<=1,1,...)) to terminate recursion.
Are there alternatives to LAMBDA recursion for iterative calculations?
Yes — REDUCE, SCAN, MAKEARRAY, and MAP cover most iterative patterns without true recursion and are generally faster and easier to debug.
More on LAMBDA (reusable formulas)
See all →Reference
LAMBDA
Defines a reusable custom function using named parameters and a formula body, letting you call it like any built-in function — no VBA or scripts needed.
How-toHow to use LAMBDA with MAP across a range
Use MAP with a LAMBDA to apply a custom formula to every cell in a range in Excel 365 and Google Sheets, returning a transformed array without helper columns.
How-toHow to use LAMBDA with REDUCE for cumulative math
Use REDUCE with a LAMBDA to fold a range into a single accumulated value in Excel 365 and Google Sheets — perfect for running totals, products, or custom aggregations.
How-toHow to use LAMBDA with SCAN for a running balance
Use SCAN with a LAMBDA to compute a running balance or cumulative total in Excel 365 and Google Sheets, returning one result per row without helper columns.
How-toHow to use BYROW and BYCOL to apply a LAMBDA per row or column
BYROW and BYCOL apply a LAMBDA to each row or column of a range in Excel 365 and Google Sheets, returning a per-row or per-column result without helper rows or columns.
How-toHow to use MAKEARRAY with LAMBDA
MAKEARRAY generates a 2-D array of any size in Excel 365 and Google Sheets by calling a LAMBDA with each row and column index, letting you build multiplication tables, calendars, or computed grids.