Author: webadmin

Become a Spreadsheet Ninja with the New Excel LAMBDA Function

woman using LAMBDA function in Excel


Spreadsheets are a critical tool for businesses to store, organise and analyze data. In the past, it has been difficult even for advanced Excel users to create dynamic spreadsheets that can do things like calculating conditional formatting based on changing values in other cells.

The new Excel LAMBDA function marks a significant change because you can now use formulas as if they were functions! This blog post covers everything you need to know about the new LAMBDA function and how it works so you can become a spreadsheet ninja!


What Is The LAMBDA Excel Function?


The LAMBDA excel function is a way to do simple programming in Excel. Microsoft has made this new Excel function available with the Office 365 subscription plan from April 2016.

In traditional programming, multiple inputs can affect your output values so it’s difficult to get unexpected results from your computer programs simply because you can test every possible combination of inputs and the conditions that those inputs affect.

The LAMBDA excel function is a way to do simple programming in Excel so you can use if-then statements as formulas instead of just values!

So why does this matter? Well, now your spreadsheet calculations can be dynamic and change automatically based on changing data or other events without making manual adjustments.


What Does The LAMBDA Excel Function Do?


Lambda is a function that’s built into Excel to help shorten your formula syntax. Here are some examples of what lambda can do:

– Count cells based on specified conditions with COUNTIFS, SUMPRODUCT & COUNTA functions in excel

– Use IF and AND statements as formulas instead of just values so you don’t have to write out the long syntax

– Use OR statements as formulas instead of just values so you don’t have to write out the long syntax


LAMBDA excel function ribbon box


How To Use The LAMBDA Excel Function?


To use the LAMBDA function in Excel you need to have a Microsoft Office 365 subscription and the latest version of Microsoft Office installed. You also need the latest Microsoft updates installed (including Beta updates). Currently, you also need to join Microsoft Office Insiders in order to receive Beta updates.

The most basic use of the new LAMBDA excel function is to add if-then statements in your spreadsheets that only show up when a condition you’ve defined occurs, like below:

If [A] = [value], then show this value or else show this other value.

The above is written in Excel exactly how you would write it out in plain English. The “if-then” statement tells excel when to show the first value and when to show the second one so if cell A has a certain value, then that label will be shown instead of another. You can use any formula in the “if-then” statement, not just values.

The next step after writing your if-then statement is to define what values will trigger it! You can do this by using either of these methods:

Use an =IF() formula in a different cell and connect that cell reference to another cell with the LAMBDA excel function.

Use a Data Validation formula in another cell and connect that to your LAMBDA excel function so the input will come from what you select with the drop-down menu next time you open up your spreadsheet.

This is much easier than it sounds!


LAMBDA Excel Function Example


The next steps will show you an example of how to use the LAMBDA excel function with a real-life spreadsheet.

Let’s start with the Syntax:

=LAMBDA([parameter1, parameter2, …,] calculation)

The parameter argument defines the values that you want to pass to the calculation. This could be a cell reference, a text string, or a numeric value.

You can define up to 253 parameters, but this limit is unlikely to be reached!

The parameter argument is optional. Parameter names are defined as a text string e.g. x, number, myPar, etc.

The calculation argument defines the calculation that will be performed using the defined parameters. It must return a result, and it is not optional


This will return the result of 10 in the cell. A single parameter (x) is defined and the calculation argument multiplies that parameter by 10. The value of (1) after the first part of the function defines the value for the x parameter. This is only required if you are using the LAMBDA function directly into a cell instead of using it to create a re-usable function.

You can use standard Excel functions within the calculation argument.

=LAMBDA(x, y, SQRT((x^2+y^2)))

This LAMBDA function will use Pythagoras to calculate the hypotenuse of a right-angled triangle. The parameters x and y represent the horizontal and vertical sides of the triangle. The calculation argument squares both the parameters and adds them together and then uses the Excel SQRT function to get the value of the hypotenuse.


lambda comment


 Creating Re-usable Function with LAMBDA Function In Excel


Re-usable LAMBDA functions are incredibly useful and can be used just like the Excel functions that exist in your everyday workbooks.

Get started by defining a new name then click on Formulas in the top Excel ribbon. This will give you the option to Define Name in the Defined Names group of the ribbon.

Give your new name a descriptive name, then click on the Scope button to define where you want this function to be available.

A workbook can contain up to 253 defined names (or functions). Each of these is either global or local in scope so by clicking one of these two buttons it will tell excel which setting you have chosen.

The final step is to define the parameters which you will need for your new function. These are simply text strings that can be used within custom functions just like standard excel formulas, so these could look something like x or number.

You now have a brand-new, re-usable Excel function! Be sure to save it with an appropriate name so that you can find it again later if needed!

You could then use this function in your spreadsheet by clicking on the cell where you want to see the results of your custom function, and entering =myFunction(parameter) into the formula bar. This will be displayed with a drop-down menu when using Data Validation so users can choose exactly which parameter they want to use as input for your custom function.


The LAMBDA Excel Function – A New Dawn In Excel


The LAMBDA function is a brand-new Excel tool that allows you to create flexible and reusable functions for your workbooks. From basic arithmetic, through Pythagoras’ Theorem, to complex conditional equations; the LAMBDA excel function opens up a whole new world of possibilities in Microsoft Office!

If you need help using the LAMBDA Excel function please get in touch with a member of our team. Our Excel Experts have worked with a range of businesses to transform their operations using automation, LAMBDA and more complex Excel features and functions.

We are recruiting for a virtual customer service assistant

the excel experts icon

Thank you very much for showing interest in this post. This current vacancy has now been filled…

As a growing business, we are looking for a freelance customer service assistant to help us answer our calls from prospective clients. 

We are a dynamic (virtual) team of spreadsheet experts providing business-to-business IT solutions and we need somebody to answer the phone for our 0800 number. You can see our website here

We make software solutions for our customers, tools that will make their lives easier. That’s why they will be calling you. Your first responsibility will be to make them feel listened to. You will then report back to us, so we can find the best expert to fulfill their needs.

We need someone friendly but serious, and a good listener. You will take caller details and record them in our online database. The database will then be distributed to our team of experts, to find the best match for that client.

When people call they generally have a problem, and they’re looking for someone who can listen and understand their problem. You don’t have to be an Excel expert, but any experience in these areas will be beneficial. You do need to have some experience with how offices work (especially ‘work from home’ offices), and how individuals are working and feeling in these difficult times.

To this end, one of the essential responsibilities will be to write a short brief on what the visitor is actually looking for, and a few details about their project. This will give our consultants something to start with, and the caller will almost certainly have told you something about it by the time they hang up!

We are looking for someone with excellent customer service, to work on a self-employed, freelance basis. You will need to have a computer and an internet connection, we will provide a mobile phone. You will need to be available to answer calls during office hours, with flexibility, if needed, for things like school runs, as being family-friendly is very important for us.

We do not know at present how many phone calls we will be receiving but on past (pre-Covid) experience it will likely be 3-4 a day. As this number increases there will be opportunities to re-negotiate rates and hours.

This is a great opportunity to play a vital role in an innovative, family-friendly workplace with future growth potential. This is an interesting position for someone with interests in technology, especially Microsoft technology, and for someone who enjoys talking a bit ‘technical’ on the phone.

Please apply by emailing your CV and a covering letter. In your letter, you should outline why you would like to join us in this role. Please also add the rate you would happily charge per call to fulfill this task for us. We want to pay a fair and competitive price for the right person.

Please get in touch if you have any questions.