LAMBDA Functions

The LAMBDA function allows users to create custom and reusable functions in Excel, assign them easily recognizable names, and use them throughout the workbook just like built-in Excel functions.

You can encapsulate frequently used formulas into custom functions and define them into Excel’s Name Manager. When using them, simply call the defined name directly, eliminating the need for repeated copy-pasting, thereby reducing the risk of errors. LAMBDA functions do not rely on VBA, macros, or JavaScript, which significantly improves the reusability and maintainability of formulas.

Syntax

The syntax for the LAMBDA function is =LAMBDA([parameter1, parameter2, …,], calculation), with parameters as follows:

Parameter	Description
Parameter	[Optional] A value that you want to pass to the function, such as a cell reference, string or number. You can enter up to 253 parameters. This argument is optional.
Calculation	[Required] The formula you want to execute and return as the result of the function. It must be the last argument and it must return a result. This argument is required.

The following example demonstrates how to use the Name Manager in DsExcel to customize a LAMBDA formula that converts Fahrenheit temperatures to Celsius in bulk.

// Create a workbook.
Workbook workbook = new Workbook();
IWorksheet sheet = workbook.getWorksheets().get(0);

// Set data.
Object[][] data = {
        { "Date","Fahrenheit" },
        { "2025-06-01", 95},
        { "2025-06-02", 95},
        { "2025-06-03", 96.8},
        { "2025-06-04", 86},
        { "2025-06-05", 82.4},
        { "2025-06-06", 89.6},
        { "2025-06-07", 86},
        { "2025-06-08", 87.8}
};
sheet.getRange("B3:C11").setValue(data);
ITable table = sheet.getTables().add(sheet.getRange("B3:C11"), true);
ITable table1 = sheet.getTables().add(sheet.getRange("E3:E11"), true);
sheet.getRange("B3:B11").setNumberFormat("yyyy-mm-dd");
table.convertToRange();
table1.convertToRange();

// Define the LAMBDA name ToCelsius, implementing the formula for converting Fahrenheit to Celsius.
workbook.getNames().add("ToCelsius", "=LAMBDA(temp, (5/9) * (temp-32))");

// Insert the formula in the range E4:E11 to convert the Fahrenheit values in column C to Celsius.
sheet.getRange("E4:E11").setFormula2("ToCelsius(C4)");
sheet.getRange("E3").setValue("=ToCelsius(C4:C11)");
sheet.getRange("B:E").autoFit();

// Save the Excel file.
workbook.save("LAMBDAToCelsius.xlsx");

The output is shown in the figure below:

Note: setFormula2 enables you to set formulas that support dynamic array functions and the latest Excel formula features, while remaining fully compatible with Formula. For more information, see Dynamic Array Formulas.

LAMBDA functions can reference themselves when assigned to a defined name, enabling recursive calculations. In DsExcel Java, the maximum recursion depth for LAMBDA is 256 levels. The following example demonstrates how to create a custom LAMBDA function in Name Manager to calculate the factorial of a number.

// Create a workbook.
Workbook workbook = new Workbook();
IWorksheet sheet = workbook.getWorksheets().get(0);
sheet.setName("FactorialLambda");

// Set data.
sheet.getRange("A1").setValue("n");
sheet.getRange("A2").setValue(1);
sheet.getRange("A3").setValue(2);
sheet.getRange("A4").setValue(3);
sheet.getRange("A5").setValue(4);
sheet.getRange("A6").setValue(5);
sheet.getRange("A7").setValue(6);
sheet.getColumns().get(1).setColumnWidth(10);
sheet.getColumns().get(0).setHorizontalAlignment(HorizontalAlignment.Right);
sheet.getColumns().get(1).setHorizontalAlignment(HorizontalAlignment.Right);
sheet.getRange("A1:B1").getFont().setBold(true);

// Add a defined name “Factorial” to implement the factorial of a number.
workbook.getNames().add("Factorial", "=LAMBDA(n, IF(n<=1, 1, n*Factorial(n-1)))");
sheet.getRange("B1").setValue("=Factorial(A2:A7)");

// Insert formula in B2:B7 to calculate the factorial of the numbers in column A.
sheet.getRange("B2:B7").setFormula2("Factorial(A2)");
sheet.getColumns().get(1).autoFit();

// Save the Excel file.
workbook.save("LAMBDAFactorial.xlsx");

The output is shown in the figure below:

You can reference a defined name added in one worksheet from other worksheets, or even from other workbooks. The following example demonstrates how to reference a defined name in a formula on a different worksheet after defining it on a worksheet.

// Create a workbook.
Workbook workbook = new Workbook();
IWorksheet sheet1 = workbook.getWorksheets().get(0);

// Add a defined name Test, whose content is ABS.
sheet1.getNames().add("Test", "=ABS");

// Add a second worksheet.
IWorksheet sheet2 = workbook.getWorksheets().add();

// Set a formula in cell B2 of Sheet2 to reference the defined name Test added in Sheet1.
sheet2.getRange("B2").setFormula2("=Sheet1!Test(-2)");
sheet2.getRange("B1").setFormula2("=FORMULATEXT(B2)");
sheet2.getRange("A1").setValue("Formula Text:");
sheet2.getRange("A2").setValue("Formula Result:");
sheet2.getColumns().get(1).autoFit();
sheet2.getColumns().get(0).autoFit();
sheet2.getRange("A1:A2").getFont().setBold(true);

// Save the Excel file.
workbook.save("LAMBDACrossWorksheet.xlsx");

The output is shown in the figure below:

Note: If a defined name has the same name as a built-in Excel function (for example, sheet.Names.Add("SUM", "ABS");), referencing it directly across worksheets using =Sheet1!SUM(-2) will result in an error. In this case, you need to use double parentheses to explicitly reference the defined name (for example, =((Sheet1!SUM))(-2)) to avoid conflicts with the built-in function.

Formula Invocation

The LAMBDA function can not only be named and reused, but also be immediately invoked within a formula. You can define a LAMBDA function in a cell formula and immediately invoke it with actual arguments, enabling you to quickly implement one-off calculation logic without having to predefine the function in the Name Manager. In scenarios involving complex formulas or nested calculations, temporary usage of custom functions can enhance calculation flexibility.

The basic syntax for immediate invocation of LAMBDA is: =LAMBDA(parameter1, parameter2, ..., calculation)(argument1, argument2, ...). After defining the LAMBDA function, simply pass arguments in parentheses immediately after and it will be executed. For example, if the cell formula is set to =LAMBDA(x, x+1)(3), it will immediately return the result 4.

Eta-reduced LAMBDA

Eta-reduced LAMBDA (Eta LAMBDA) is a syntactic simplification of LAMBDA. When a LAMBDA function is only used for directly passing parameters, the LAMBDA declaration can be omitted, and the function name can be directly used as an argument for functions such as BYROW, BYCOL, MAP, SCAN, MAKEARRAY, REDUCE, PIVOTBY, and GROUPBY.

For example, before Eta LAMBDA was introduced, passing an absolute value LAMBDA function to the BYROW function required writing =BYROW(A1:A10, LAMBDA(a, ABS(a))). After supporting Eta LAMBDA, it can be simplified to =BYROW(A1:A10, ABS).

DsExcel Java supports using Eta LAMBDA in defined names, the name_value parameter of the LET function, and the LAMBDA function. It also supports referencing custom functions in formulas.

Syntax Comparison Between Eta LAMBDA and Ordinary LAMBDA

The following example demonstrates the syntactic differences between Eta LAMBDA and ordinary LAMBDA.

// Create a workbook.
Workbook workbook = new Workbook();
IWorksheet sheet1 = workbook.getActiveSheet();
sheet1.setName("BYROW");

sheet1.getRange("$B$2").setValue("ETA LAMBDA VS Normal LAMBDA");
sheet1.getRange("$B$2").getFont().setBold(true);

ITable table1 = sheet1.getTables().add(sheet1.getRange("$B$5:$E$10"), true);
table1.convertToRange();

// Set data.
Object[][] sheet1rngB5E10 = new Object[][] {
        { "Student", "Math", "English", "Physics"},
        { "Tom", 19.0, 23.0, 19.0},
        { "Jerry", 21.0, 15.0, 18.0},
        { "Mario", 19.0, 22.0, 22.0},
        { "Luigi", 17.0, 15.0, 23.0},
        { "Galen", 16.0, 22.0, 24.0}
};

ITable table2 = sheet1.getTables().add(sheet1.getRange("G5:G10"), true);
table2.convertToRange();

ITable table3 = sheet1.getTables().add(sheet1.getRange("G13:G18"), true);
table3.convertToRange();

sheet1.getRange("$B$5:$E$10").setValue(sheet1rngB5E10);

// Pass Normal LAMBDA into the BYROW function.
sheet1.getRange("$G$4").setValue("Normal LAMBDA");
sheet1.getRange("$G$5").setFormula2("=FORMULATEXT(G6)");
sheet1.getRange("$G$5").getFont().setColor(Color.GetBlack());
sheet1.getRange("$G$5").getInterior().setColor(Color.GetWhite());
sheet1.getRange("$G$6").setFormula2("=BYROW(C6:E10,LAMBDA(row,SUM(row)))");

// Pass Eta LAMBDA into the BYROW function.
sheet1.getRange("$G$12").setValue("ETA LAMBDA");
sheet1.getRange("$G$13").setFormula2("=FORMULATEXT(G14)");
sheet1.getRange("$G$13").getFont().setColor(Color.GetBlack());
sheet1.getRange("$G$13").getInterior().setColor(Color.GetWhite());
sheet1.getRange("$G$14").setFormula2("=BYROW(C6:E10,SUM)");

// Save the Excel file.
workbook.save("EtaLambda.xlsx");

The output is shown in the figure below:

Referencing Eta LAMBDA in a Defined Name

The following example demonstrates how to reference SUM through the defined name AliasOfSUM.

// Create a workbook.
Workbook workbook = new Workbook();
IWorksheet sheet = workbook.getWorksheets().get(0);

sheet.getRange("A1").setValue(10);
sheet.getRange("A2").setValue(20);
sheet.getRange("A3").setValue(-5);
sheet.getRange("A4").setValue(0);
sheet.getRange("A5").setValue(20);

sheet.getRange("C4").setValue("Formula Text:");
sheet.getRange("C5").setValue("Formula Result:");
sheet.getRange("C4:C5").getFont().setBold(true);
sheet.getColumns().get(2).autoFit();
sheet.getRange("D5").getInterior().setColor(Color.GetYellow());

// Add a FORMULATEXT formula to display the formula text in D5.
sheet.getRange("D4").setFormula2("=FORMULATEXT(D5)");

// Add a defined name AliasOfSUM, whose content is SUM.
workbook.getNames().add("AliasOfSUM", "=SUM");

// Use the defined name AliasOfSUM as a formula in D5, which is equivalent to calculating the sum of A1 to A5.
sheet.getRange("D5").setFormula2("AliasOfSUM(A1:A5)");

// Save the Excel file.
workbook.save("EtaLAMBDAasDefinedName.xlsx");

The output is shown in the figure below:

Passing Eta LAMBDA as a Parameter in LAMBDA Functions

The following example demonstrates how to pass SUM as a parameter to a LAMBDA function to sum the range A1:A5.

// Create a workbook.
Workbook workbook = new Workbook();
IWorksheet sheet = workbook.getWorksheets().get(0);

sheet.getRange("A1").setValue(10);
sheet.getRange("A2").setValue(20);
sheet.getRange("A3").setValue(-5);
sheet.getRange("A4").setValue(0);
sheet.getRange("A5").setValue(20);

sheet.getRange("C4").setValue("Formula Text:");
sheet.getRange("C5").setValue("Formula Result:");
sheet.getRange("C4:C5").getFont().setBold(true);
sheet.getColumns().get(2).autoFit();
sheet.getRange("D5").getInterior().setColor(Color.GetYellow());

// Add a FORMULATEXT formula in D4 to display the formula text of D5.
sheet.getRange("D4").setFormula2("=FORMULATEXT(D5)");

// Define a LAMBDA function that takes SUM and the range A1:A5 as parameters,
// then uses the LAMBDA function to sum the range with SUM(A1:A5).
sheet.getRange("D5").setFormula2("=LAMBDA(AliasOfSUM, range, AliasOfSUM(range))(SUM, A1:A5)");

// Save the Excel file.
workbook.save("EtaLAMBDAasArguments.xlsx");

The output is shown in the figure below:

Referencing Eta LAMBDA in the “name_value” Parameter of the LET Function

The following example demonstrates using SUM as the “name_value” parameter of the LET function to sum the A1:A5 range.

// Create a workbook.
Workbook workbook = new Workbook();
IWorksheet sheet = workbook.getWorksheets().get(0);

sheet.getRange("A1").setValue(10);
sheet.getRange("A2").setValue(20);
sheet.getRange("A3").setValue(-5);
sheet.getRange("A4").setValue(0);
sheet.getRange("A5").setValue(20);

sheet.getRange("C4").setValue("Formula Text:");
sheet.getRange("C5").setValue("Formula Result:");
sheet.getRange("C4:C5").getFont().setBold(true);
sheet.getColumns().get(2).autoFit();
sheet.getRange("D5").getInterior().setColor(Color.GetYellow());

// Add a FORMULATEXT formula to display the formula text in D5.
sheet.getRange("D4").setFormula2("=FORMULATEXT(D5)");

// Pass SUM as the "name_value" parameter of the LET function, assign it to the variable AliasOfSUM,
// and use AliasOfSUM(A1:A5) to calculate the sum of A1 to A5, which is equivalent to
// "=LAMBDA(AliasOfSUM, range, AliasOfSUM(range))(SUM, A1:A5)".
sheet.getRange("D5").setFormula2("=LET(AliasOfSUM, SUM, AliasOfSUM(A1:A5))");

// Save the Excel file.
workbook.save("EtaLAMBDAforLET.xlsx");

The output is shown in the figure below:

Applying Custom Functions as Eta LAMBDA in Formula Functions

The following example demonstrates how to pass a custom function as an Eta LAMBDA argument to the MAP function.

public class IsNegativeFunction extends CustomFunction {
    public IsNegativeFunction() {
        super(
                "ISNEGATIVE",                                           
                "Returns true if the value is negative.",               
                FunctionValueType.Boolean,                               
                new Parameter[] { new Parameter(FunctionValueType.Number) } 
        );
    }

    @Override
    public Object evaluate(Object[] arguments, ICalcContext context) {
        if (arguments != null && arguments.length > 0 && arguments[0] != null) {
            try {
                double value = Double.parseDouble(arguments[0].toString());
                return value < 0;
            } catch (Exception e) {
                return false;
            }
        }
        return false;
    }
}

// Create a workbook.
Workbook workbook = new Workbook();
IWorksheet sheet = workbook.getWorksheets().get(0);

workbook.AddCustomFunction(new IsNegativeFunction());

Object[][] values = new Object[][]{
        { 5,  -3, 0  },
        { 23, -1, -4 },
        { -13, 0, -12 }
};
sheet.getRange("A2:C4").setValue(values);

sheet.getPageSetup().setPrintHeadings(true);
sheet.getPageSetup().setPrintGridlines(true);
sheet.getPageSetup().setOrientation(PageOrientation.Landscape);

sheet.getRange("A1").setValue("The data to be checked for negative values:");
sheet.getRange("A11").setValue("Formula Text (A7):");
sheet.getRange("A6").setValue("Formula Result:");
sheet.getRange("A1").getFont().setBold(true);
sheet.getRange("A11").getFont().setBold(true);
sheet.getRange("A6").getFont().setBold(true);
sheet.getRange("A5:E5").merge();
sheet.getRange("A10:E10").merge();

// Add FORMULATEXT formula to display the formula text in A7.
sheet.getRange("C11").setFormula2("=FORMULATEXT(A7)");

// Pass the custom function ISNEGATIVE to the MAP function as an Eta Lambda function.
sheet.getRange("A7").setFormula2("=MAP(A2:C4, ISNEGATIVE)");

// Save the Excel file.
workbook.save("CustomFuncasEtaLAMBDA.pdf");

The output is shown in the figure below:

Remarks

Defined names can share the same name as Excel's built-in functions (e.g., workbook.Names.Add("SUM", "ABS");). When such a name conflict occurs:
- The behavior of the built-in function remains unchanged. For example, =SUM(-1) will still return -1.
- If SUM has already been used as an Eta LAMBDA (for example, =BYROW(A1:A2, SUM)) and is then defined as a defined name (for example, workbook.Names.Add("SUM", "ABS");), SUM used as an Eta LAMBDA will automatically add the _xleta. prefix, i.e., =BYROW(A1:A2, _xleta.SUM).
- If SUM has already been defined as a defined name and is then used as an Eta LAMBDA, the Eta LAMBDA will be recognized as the defined name SUM. At this point, the defined name defined first takes precedence over the later Eta LAMBDA.
- After SUM is added as a defined name, if you want to use SUM as an Eta LAMBDA, you will need to prefix SUM with _xleta. (e.g., =BYROW(A1:A2, _xleta.SUM)).
If a built-in function is first used in Eta LAMBDA and then redefined as a defined name causing a naming conflict, the reference to this built-in function in Eta LAMBDA will automatically be prefixed with _xleta., and the calculation result of Eta LAMBDA remains unchanged. For custom functions, if they are first used in Eta LAMBDA and are later defined as defined names, the name in Eta LAMBDA will not be automatically prefixed and will be directly recognized as the defined name, which may change the formula’s meaning and calculation result.
The LAMBDA and LET functions themselves cannot be used as Eta LAMBDA, otherwise they will be treated as ordinary names.
Setting a cell formula as Eta LAMBDA (such as =SUM) will return a #CALC! error.
If a function does not support LAMBDA as an argument but receives an Eta LAMBDA, it will return a #VALUE! error. For example: =SUM(SUM).