Recursive LAMBDA in Excel: Examples That Work

Recursive LAMBDA formulas call themselves to repeat a calculation, such as finding a factorial or stepping through a list. In Microsoft 365 or Excel 2024, create a named LAMBDA in Formulas > Name Manager > New, then have its formula call that same name; for example, a named factorial function can return 120 for an input of 5.

What recursion means in Excel

Recursion is a way to solve a problem by reducing it to a smaller version of the same problem. A recursive function needs two parts: a base case that ends the process, and a recursive case that calls the function again with a changed input. Without a base case—or if the input never moves toward it—the calculation can continue until Excel reports an error.

LAMBDA lets you define a custom function using arguments and a calculation. For recursion, the function must have a name that it can refer to when calling itself. That is why a recursive LAMBDA is usually saved as a named formula rather than entered anonymously in a worksheet cell. The name acts as the function’s identity during the recursive calls.

Recursion is useful for explaining or implementing problems that naturally repeat. It is not automatically faster or simpler than ordinary Excel functions. For common tasks such as summing a range, Excel’s built-in SUM function is usually clearer and more efficient.

How to create a recursive named LAMBDA

In Excel for Microsoft 365 on Windows, open Formulas > Name Manager > New. Enter a name, such as FACTORIAL_R, and put the LAMBDA expression in the Refers to box. The function name inside the expression must match the name you assign. Set the scope to Workbook if you want to call the function from any worksheet, then select OK.

Choose a function name that does not conflict with an existing name or built-in function.
Write the base case first in the logic: it defines when the function stops calling itself.
Make the recursive call with an argument that gets closer to the base case.
Call the saved function from a worksheet cell and check a small, known example.

On a Mac, the formula syntax is the same; use the Formulas tab to create or manage the name. Menu labels and the name-management dialog can vary slightly by Excel build. LAMBDA is available in Microsoft 365 and Excel 2024; it is not supported in older perpetual versions such as Excel 2019. A workbook that uses LAMBDA may show #NAME? when opened in an unsupported version.

Example 1: Calculate a factorial

A factorial multiplies a positive integer by every positive integer below it: 5 factorial is 5 × 4 × 3 × 2 × 1, or 120. The base case is n less than or equal to 1, which returns 1. Otherwise, the function multiplies n by the factorial of n minus 1. For the named definition, the caller supplies a nonnegative integer as n; the definition does not read a worksheet cell directly.

In Name Manager, use the name FACTORIAL_R and this definition:

=LAMBDA(n,IF(n<=1,1,n*FACTORIAL_R(n-1)))

With the input value 5 in A2, enter this call in another cell:

=FACTORIAL_R(A2)

The result is 120: the calls reduce from 5 to 4, 3, 2, and then 1, where the base case returns 1. This example assumes the input is a nonnegative whole number. The formula does not validate that assumption; a decimal or negative input is outside its intended use.

Example 2: Add the integers from 1 through n

A recursive sum can add a number to the sum of all smaller positive integers. Its base case returns zero when n is zero or lower. For the named definition, the caller supplies a nonnegative integer as n:

=LAMBDA(n,IF(n<=0,0,n+SUM_TO_N(n-1)))

Save the definition under the name SUM_TO_N. With the input value 10 in A2, use this worksheet formula:

=SUM_TO_N(A2)

The result is 55, which is 10 + 9 + 8 + 7 + 6 + 5 + 4 + 3 + 2 + 1. This is a compact example of the recursive pattern, but it is not a recommendation to replace a simple arithmetic formula in a large workbook. For a formula that sums the integers from 1 to n, n × (n + 1) ÷ 2 is a more direct calculation.

Example 3: Traverse a vertical range

Recursion can also move through a range one row at a time. The following example assumes a nonempty vertical range of numeric values and an index that starts at 1. If the index is beyond the number of rows, it returns zero; otherwise, it adds the current item to the recursive sum of the remaining items.

Save this definition in Name Manager as SUM_RANGE_R:

=LAMBDA(items,idx,IF(idx>ROWS(items),0,INDEX(items,idx)+SUM_RANGE_R(items,idx+1)))

With numeric values 3, 5, 2, and 4 in A2:A5, call the named function with:

=SUM_RANGE_R(A2:A5,1)

The result is 14. Each call advances idx by one; after the fourth value, the next call sees an index greater than four and returns zero. This example is intentionally limited to a vertical range. For a normal range total, =SUM(A2:A5) is simpler, handles the ordinary summing task more efficiently, and is easier for another workbook user to recognize.

Example 4: Calculate Fibonacci numbers

In the common Fibonacci sequence, each number after the first two is the sum of the two before it: F0 is 0, F1 is 1, and F2 is 1. The recursive definition uses those first two values as base cases. For the named definition, the caller supplies a nonnegative integer n:

=LAMBDA(n,IF(n<2,n,FIB_R(n-1)+FIB_R(n-2)))

Save it as FIB_R. With 10 in A2, call it using:

=FIB_R(A2)

The result is 55, the value at position 10 when the sequence begins at position 0. This formula illustrates recursion, but it recalculates many earlier values more than once. The repeated work grows quickly as n increases, so this simple version is best for small inputs and learning—not for building a large sequence. A loop-like iterative design or a precomputed table is more suitable when many Fibonacci values are needed.

Example 5: Find a greatest common divisor

The Euclidean algorithm finds the greatest common divisor by repeatedly replacing a pair of numbers with the smaller number and the remainder. When the second number reaches zero, the first number is the answer. This named definition expects nonnegative integers a and b:

=LAMBDA(a,b,IF(b=0,a,GCD_R(b,MOD(a,b))))

Save it as GCD_R. If the values 48 and 18 are in A2 and B2, call the function with:

=GCD_R(A2,B2)

The result is 6. The calculation proceeds from (48, 18) to (18, 12), then (12, 6), then (6, 0). This is a useful example because each recursive step reduces the problem substantially. For ordinary worksheet use, Excel also has the built-in GCD function, so a custom recursive version is mainly valuable when learning how the algorithm works or adapting the pattern.

Common errors and ways to troubleshoot

A recursive formula is easiest to debug by checking its stopping condition and its progress separately. Start with the smallest valid input, then try a slightly larger one. If the first case works but later cases fail, inspect how the recursive call changes its arguments.

#NAME? Often means the function name was not saved, the call uses a different spelling, or the Excel version does not support LAMBDA. Check the name in Name Manager and confirm the workbook is open in a supported version.
#NUM! Can indicate that recursion has exceeded Excel’s calculation limits. Reduce the input or redesign the calculation; Excel does not promise unlimited recursive calls.
#VALUE! Can result from supplying text or an incompatible argument where the function expects a number or range of numbers. Check the input type and the function’s stated assumptions.
A calculation that never seems to finish may have a recursive call that does not approach its base case, or an algorithm that repeats too much work. Verify that each call changes the input in the intended direction.

Also distinguish defining a LAMBDA from calling it. A named definition belongs in Name Manager, while a worksheet formula such as =FACTORIAL_R(A2) invokes it. A LAMBDA written in a cell without being called is not the same as a completed calculation.

When recursion is—and is not—the right choice

Use recursion when the problem has a natural repeating structure and a clear stopping condition, or when a recursive definition makes a specialized calculation easier to understand. The greatest-common-divisor example is a good fit because every step reduces the remaining problem. A factorial is useful as a teaching example, though an existing Excel function may be preferable for routine work.

For standard worksheet operations, prefer built-in functions when they express the intent directly. SUM is preferable to a recursive range sum; GCD is preferable to a custom GCD_R function. Built-in functions are generally easier to audit and less likely to encounter recursion limits. For repeated calculations over many rows, consider a helper column, a dynamic-array formula where appropriate, or a nonrecursive design. Recursive LAMBDAs are custom functions, not VBA macros, and creating one does not require enabling macros.

Frequently Asked Questions

Can a LAMBDA function call itself in Excel?
Yes. Save the LAMBDA as a named function, then refer to that same name inside its definition. The name must match exactly, and the recursive logic needs a base case to stop.

Why does my recursive LAMBDA return #NAME?
Check that the name exists in Name Manager, that the spelling and scope are correct, and that your Excel version supports LAMBDA. The feature is available in Microsoft 365 and Excel 2024.

Does a recursive LAMBDA need a base case?
Yes. A base case returns a result without making another recursive call. Without one, or if the input never reaches it, Excel can run into its recursion or calculation limits.

Is a recursive LAMBDA better than SUM or GCD?
Usually not for those standard tasks. SUM and GCD are clearer choices for ordinary worksheet calculations. Recursive examples are useful for learning the pattern or for a problem that genuinely benefits from recursive logic.

Do recursive LAMBDA formulas work in Excel for Mac?
The formula syntax is the same in supported Excel for Mac versions. The steps for managing a named formula may look different from the Windows Name Manager, but the function still needs a saved name to call itself.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.