woman standing at laptop

New LAMBDA functions available in Excel

Feature deep dive
  • Share on Facebook
  • Share on Twitter
  • Share on LinkedIn

Hey, Office Insiders! I’m Chris Gross, a Program Manager on the Excel team. Late last year, we told you about the new LAMBDA function (now rolling out to Office Current Channel Preview users), which enables you to create your own custom Excel functions. Today, we’re happy to announce seven new LAMBDA functions, as well as other improvements to the feature.

LAMBDAs as values

Over the past several years, we have been “teaching” Excel how to understand new types of values. Some of the most recent additions have been data types (Wolfram, Geography, Stocks, Power BI, and Power Query) and dynamic arrays.

LAMBDAs continue this effort by allowing Excel to understand functions as a value. (This was enabled by the introduction of LAMBDAs, but the capability was not initially exposed to any functions.) This is exciting, because it means that calculations which were previously very hard or even impossible to execute can now be achieved by writing a LAMBDA and passing it as a value to a function.

New LAMBDA functions

Here are the seven new LAMBDA functions we’re introducing.

  • MAP: Returns an array formed by “mapping” each value in the array(s) to a new value and applying a LAMBDA to create a new value.
  • REDUCE: Reduces an array to an accumulated value by applying a LAMBDA function to each value and returning the total value in the accumulator.
  • SCAN: Scans an array by applying a LAMBDA to each value and returns an array that has each intermediate value.
  • MAKEARRAY: Returns a calculated array of a specified row and column size by applying a LAMBDA function.
  • BYROW: Applies a LAMBDA to each row and returns an array of the results.
  • BYCOL: Applies a LAMBDA to each column and returns an array of the results.
  • ISOMITTED: Checks whether the value is missing and returns TRUE or FALSE.

Read on for some examples illustrating several of these new functions. For more detailed information and further examples, see my post on the Excel Tech Community site.

How they work

Let’s take a closer look at a few of the new functions.

BYCOL function

With the BYCOL function, you can easily take a large set of data and return calculations for each column in your set.

For example, say you had a table of data for your monthly expenses, and you wanted to calculate how much money you spent each month.Screenshot showing example statistics for BYCOL LAMBDA function

You could author an individual function for every column, but you’d run into issues when new data comes in or if you wanted to change your calculation in the future. This is where the power of LAMBDA comes in handy, with its ability to encapsulate a single calculation and apply it to a set of data.

With BYCOL, the formula looks like this:

=BYCOL(Expenses[#All], LAMBDA(col, SUM(col)))

This calculates the total expenses for each month in the table.Screenshot showing example statistics for BYCOL LAMBDA function

It’s important to note that BYCOL passed a single parameter to the LAMBDA function, which corresponds to a column in the array. BYCOL will call the LAMBDA and return a result for each column. The fact that BYCOL passed one parameter is important to remember, because each of the functions expects a different set of parameters and (depending on the function) can return one or many results.

SCAN function

In our next example, imagine we wanted to calculate a running total of sales for each month.Screenshot showing example statistics for the SCAN LAMBDA function

This is a perfect use case for SCAN, since this function takes two parameters, one of which will be passed subsequently to each LAMBDA call.

The final solution looks like this:

=SCAN(0, MonthlySales[Sales], LAMBDA(accumulator, value, accumulator+value))

SCAN “scans” an array by applying a LAMBDA to each value. It then returns an array of results corresponding to the accumulator value returned by the LAMBDA. SCAN takes two parameters:

  • accumulator: The initial value returned by SCAN and each LAMBDA call.
  • value: A value from the supplied array.

The other thing to note is the first, optional argument, which is the “[initial value]” for the accumulator. In our case, we want it to be 0.

The accumulator is ultimately what allows us to return the running total; all this can be done with just one formula!Screenshot showing example statistics for the SCAN LAMBDA function

MAKEARRAY function

MAKEARRAY allows you to use LAMBDA to generate arrays through calculations. This function is useful for situations in which you want to combine or transform arrays, but it’s also useful for generating data.

Let’s say you want to generate a random list of colors, using MAKEARRAY in combination with CHOOSE and RANDBETWEEN. The final solution looks like this:

=MAKEARRAY(A2, B2, LAMBDA(row, col,

CHOOSE(RANDBETWEEN(1,5),”Red”,”Blue”,”Green”,”Yellow”,”Orange”)))

The first two arguments to MAKEARRAY are A2 and B2, which correspond with how many rows and columns we want to generate. The last argument to MAKEARRAY is the LAMBDA, which takes two parameters, corresponding to the value generated by LAMBDA:

  • row: The index of the row.
  • col: The index of the column.

The calculation for our LAMBDA makes use of RANDBETWEEN in combination with CHOOSE to generate a random number corresponding to how many choices we have (in this case, 5). For example, if Excel generates the number 3, the value “Green” will be chosen. If 5 is generated, the value “Orange” will be chosen.

Once our formula is written, we can easily generate large sets of data. The final output looks like this:Screenshot showing example statistics for the MAKEARRAY LAMBDA function

LAMBDA improvements

We’re also excited to announce that we’ve added support for optional parameters within the LAMDBA function. To make use of optional parameters, just wrap the optional name in “[ ]”. For example:

=LAMBDA(param1, [param2], IF(ISOMITTED(param2), param1, param2))

This LAMBDA will return the value of param1 if param2 is omitted, and otherwise return the value of param2.

Availability

These new functions are now available to Office Insiders running Beta Channel Version 2108 (Build 14312.20008) or later on Windows, or Version 16.52 (Build 21072100) or later on Mac.

The LAMBDA function itself has been released to Office Insiders running Current Channel Preview Version 2107 (Build 14228.20154) or later on Windows, or Version 16.51 (Build 21071101) or later on Mac.

Features are released over some time to ensure things are working smoothly. We highlight features that you may not have because they’re slowly releasing to larger numbers of Insiders. Sometimes we remove elements to further improve them based on your feedback. Though this is rare, we also reserve the option to pull a feature entirely out of the product, even if you, as an Insider, have had the opportunity to try it.

Feedback

We’d love to hear what you think of these new features! Please go to Help > Feedback in Windows, or Send a Smile/Frown (Send us a Smile 😊 or Frown ☹ button) in Mac.

Learn what other information you should include in your feedback to ensure it’s actionable and reaches the right people. We’re excited to hear from you!

Sign up for the Office Insider newsletter and get the latest information about Insider features in your inbox once a month!