- 1 week ago
Create your own custom functions in Excel with Lambda and turn repetitive formulas into reusable tools. This tutorial shows why Lambda functions are so useful when you keep combining the same formulas for discounts, sorting, and other repeated tasks, then walks through how to build and save them as a named function.
You’ll start with a simple discount example using age and purchase amount, then move into a more advanced use case that sorts products by sales using SORTBY. The lesson also shows how to use the Name Manager, pass arguments correctly, anchor references when copying formulas, and add a helpful comment so your custom function is easier to understand later. If you work with Excel formulas, data analysis, or spreadsheet automation, this is a practical guide to making your work faster and more consistent.
Created for Excel users looking for Lambda functions, custom Excel functions, formula automation, SORTBY examples, and practical spreadsheet tutorials. It’s useful for data analytics workflows, repetitive reporting, and anyone who wants to simplify complex formulas into one reusable Excel function.
You’ll start with a simple discount example using age and purchase amount, then move into a more advanced use case that sorts products by sales using SORTBY. The lesson also shows how to use the Name Manager, pass arguments correctly, anchor references when copying formulas, and add a helpful comment so your custom function is easier to understand later. If you work with Excel formulas, data analysis, or spreadsheet automation, this is a practical guide to making your work faster and more consistent.
Created for Excel users looking for Lambda functions, custom Excel functions, formula automation, SORTBY examples, and practical spreadsheet tutorials. It’s useful for data analytics workflows, repetitive reporting, and anyone who wants to simplify complex formulas into one reusable Excel function.
Category
📚
LearningTranscript
00:00What's going on, everybody?
00:01Welcome back to another video.
00:02Today, we're gonna see how we can create
00:04our own custom functions in Excel.
00:11Now, you may be thinking there's so many functions in Excel,
00:14why on earth would I wanna create my own custom function?
00:16Well, sometimes, especially as you get more advanced
00:19in Excel, you start combining multiple functions together
00:23and you keep reusing this, maybe for a specific use case
00:26for a client or a customer,
00:27and you use this over and over again,
00:30and you always have to combine those functions together
00:32in order to get the output that you're looking for.
00:34Well, you're gonna be able to save all of those combinations
00:37of functions into just one function.
00:39We're gonna be able to do that
00:40by using something called a Lambda function.
00:42I'm gonna show you exactly what that is,
00:44how to use it and how it works.
00:46So without further ado,
00:47let's jump onto my screen and get started.
00:48All right, the first thing that we're gonna do
00:50is we're just gonna take a look at
00:51kind of the syntax of a Lambda function.
00:54Now, if you're familiar with Python
00:56and you've used Lambda functions in Python before,
00:58this should seem very, very familiar to you.
01:01But if you're not, I'm gonna break this down
01:04because once you actually take a look at it,
01:06it isn't super confusing or extremely difficult by any means.
01:11And so let's take a look at this diagram right here.
01:14So what we have is a function called Lambda.
01:17Then we have two parameters.
01:19We have the X and the Y,
01:22and you can have as many as you'd like.
01:23Then you have a calculation.
01:25Now, this X and Y, these parameters,
01:27and you can read it right down here
01:29while I'm talking about it,
01:31but this is what you can use to pass in values as parameters.
01:35So over here, when we call this function,
01:38when we actually declare it as a function,
01:41when we call this function, we'll call my Lambda,
01:44then we're passing through one and three.
01:46The one becomes this X, the three becomes this Y.
01:50And then here's the actual calculation that's being performed.
01:53So it's taking X and Y, and it's just adding them together,
01:56and we get four as the output.
01:58And that is what we're able to do.
02:00Now, this is a super simple kind of version of this,
02:04and the calculation can get very complex.
02:07The Lambda and the parameters
02:09are usually kind of the simpler parts,
02:11and then the calculation can get quite advanced.
02:14Now, let's jump over to our Excel.
02:15And for this first example, we're gonna be using a discount.
02:19Just a really simple, so we can see how the whole process works,
02:22and how you actually write Lambda functions,
02:24and then create your custom function.
02:26And then we're gonna go into a different example,
02:29which I know I've used, and I think it's just a neat example.
02:32And so the first thing that we're gonna take a look at
02:34is just the data.
02:35We have a name, age, purchase amount,
02:38and let's say this is their total purchase amount,
02:40but we want to apply a discount to this based off of their age.
02:45And this is something that we do all the time,
02:48and so we want to save this so that we can reuse it
02:51over and over and over again.
02:53So the formula for something like this
02:55is we could say equal, we can say if,
02:58then we'll do our parentheses.
03:00Now we have our logical test.
03:01So we can say if the age, and we'll just do this column,
03:05and we'll say is greater than 64,
03:08then if that is true, we're gonna take the purchase amount,
03:12and we're gonna multiply it times, let's say 0.75.
03:16That means we're giving them a 25% discount.
03:19But if it is false, which means if that logical test is not true,
03:23maybe they're 63 or 25, if they're 64 years or younger,
03:29then we'll do a comma, and we'll say D2,
03:32and we'll still give them a discount, we're not bad people,
03:34but we'll just give them a smaller discount.
03:36So instead of a 25% discount, we're gonna multiply it times 0.9,
03:40which is a 10% discount.
03:42So we're gonna hit enter, and whoops,
03:45let's drag this all the way down.
03:47So now we've applied this all the way down.
03:50So if they're 65 and up, they're gonna get 25% discount.
03:55If not, they're still getting a 10% discount,
03:58which I think is very fair.
04:00And so these are our final numbers right here.
04:03So what we have up here in our formula, or if you want to double click into here,
04:07this is something that in the programming world, you'd call hard coded,
04:10which means that if you ever want to change anything in here,
04:14you need to come in here and manually change the C2, D2, and D2 in order to use it in
04:20the future.
04:21But with a Lambda function, we make it more dynamic,
04:23which means it changes as you pass through different things.
04:27So as we were talking about before with those parameters,
04:30as we pass through new values into those parameters,
04:32we don't have to go and manually change all of this.
04:35We just have to specify the parameters really quickly.
04:39And so it saves a lot of time.
04:41So let's see how we can write this Lambda function,
04:43and then we're gonna create it as a custom function.
04:45So what we can do is we're gonna say,
04:48we're actually gonna copy all of this real quick,
04:50or at least let's copy this if part.
04:53So we're gonna copy this.
04:55And we're gonna come in here and we're gonna say equals,
04:57and then we're gonna say Lambda.
04:59It says creates a function value, which can be called within formulas.
05:03So we're gonna open up a parenthesis.
05:05Here is our parameter or calculation.
05:08Now we only have two parameters.
05:09We have the age, and we have the purchase amount.
05:13So we're gonna call this age, and then we'll say amount.
05:17We'll do it just like this.
05:18And then we have a comma.
05:19And we can either put in a parameter or calculation.
05:21So if we wanted to keep putting in more parameters, we can.
05:25But now we're gonna add in our calculation.
05:27So we're gonna paste in our calculation.
05:30But this C2 and D2 and D2,
05:33these are references to the age and the purchase amount.
05:37So this age right here is this C2.
05:41So we're gonna go over to C2.
05:43We're gonna put in age.
05:44And then for the amount, that's that D2.
05:47So now we're gonna put it right here and put it right here.
05:51Now, right at the bottom, at the very end, we need to close our parentheses.
05:56And so what's gonna happen if we hit enter?
05:59It's not gonna work.
06:01And this is because this Lambda function requires us to,
06:05and let me pull it up over here.
06:07This requires us to pass through arguments.
06:09Right now, it's just age and amount.
06:11There's no actual numbers for this calculation to work right here.
06:15So we have to pass through these numbers.
06:17Now, just within the Lambda, we can pass through it at the very end.
06:22We're gonna pass through this, comma, this.
06:26And we're gonna close the parentheses.
06:28And we'll hit enter, and there we go.
06:31So now we can go all the way down with this.
06:34And these should be the exact same numbers.
06:37Now, this seems like a lot of work.
06:40Because right here, it was pretty simple.
06:43And then, right next to it, we have this really long Lambda function.
06:47So why would we want to do that?
06:49Well, we're about to save us a lot of time.
06:51Because we're gonna copy this entire Lambda function right here.
06:55And we're gonna go over to right here, which is Name Manager.
06:59So you're gonna go to the Formulas.
07:00We're gonna come over to the Name Manager.
07:03This Name Manager is gonna allow us to create a custom function.
07:06So let's go ahead and click on this.
07:08We're gonna go to New.
07:10And we're gonna name this.
07:11We're gonna call this the 10...
07:14Actually, we're gonna call this the discount underscore applied function.
07:20Now, in this refers to, we need to get rid of this.
07:23And we need to paste in our function, which is our Lambda function.
07:26And in our Lambda function, we have everything we need.
07:29So now, that discount apply, we're going to actually create this.
07:32So now, this is an actual function.
07:36And there is our formula that we created.
07:39Now, we applied this to all of our workbooks.
07:42So it's gonna save it in there.
07:43We'll close this out.
07:45And now, all we have to do to apply that discount, instead of writing all this out,
07:49and instead of creating this whole Lambda, all we have to do is we say equal to.
07:55And we're gonna say discount.
07:57And there it is right here.
07:59So now, we can double click on this.
08:01And we have to pass through the age and the amount.
08:04So now, we're just gonna do here, C2, comma, D2.
08:08And that's it.
08:09And now, we can apply that all the way down.
08:12And so, it's pretty awesome because if you are using this a lot,
08:16if you're gonna be using this discount applied a ton,
08:19then you don't have to rewrite this calculation every single time.
08:22You can standardize it.
08:24You can create it with this Lambda function.
08:26And then, you can save it.
08:27And that's really, really powerful.
08:28So now, let's go over to our next example.
08:32And over here, we have a bunch of data.
08:35We just have some SAT prep products.
08:38And these are some SAT companies selling middle school books, high school books,
08:44ACE the SAT bootcamp, test booklets, and all sorts of things.
08:48And then, we have January through December's data.
08:51Now, this is something that I've actually, in a similar use case,
08:54this is something I've actually used a Lambda function for because I used to get this data
08:59from several clients where I needed to organize it based off of some other data.
09:04So, I wanted to say, okay, I have this January data.
09:08In January, let's organize the products from the ones that sold the most to the least.
09:14And if you're going to do this manually, you know, you have to come in here.
09:17And I want to do this for each month, right?
09:19It's not just one month, but I want to do this for each month.
09:21So, I have to come in here.
09:23I'm going to choose data.
09:24I'm going to filter.
09:25I'm going to go smallest to largest, or sorry, largest to smallest.
09:31And then, I need to copy this, and that'll be for January.
09:35Then, I need to come over here, and I need to do largest to smallest.
09:39I need to copy this, and that's going to be for February, and so on and so forth.
09:44And so, you can see, this is a very manual task.
09:46I'm just going to control Z to get back.
09:48This is a very manual task, and this can be, let me get rid of the filters there, too.
09:52This can be a very manual task.
09:55And so, what you can do is you can do a function that does this for you,
09:58and it's going to speed up your process immensely.
10:01So, let's see how we can do this.
10:03We're going to say equal to, and we'll just create the formula just like this.
10:08We'll create just the formula first, and then we'll save it as a function.
10:12Now, we can do this, and we're going to create the whole lambda formula first,
10:16and then we'll save it so that we can just keep reusing it for this entire month.
10:20And by the time it takes us to create the formula and the function,
10:25we basically would have had to do that all manually.
10:28And now, we can use this function forever, and we can just keep using it.
10:32It'll save us a lot of time in the long run.
10:34So, we're going to say equal to, and you guessed it, we're going to say lambda.
10:37And what we need to do is we need to take in two different things.
10:41We need to take in the unit sold, so that's in January.
10:46And then we also need to take in this right here, which is our product.
10:50Now, we don't want to actually take those in.
10:53These are cell references and ranges.
10:55We want to just specify the parameters.
10:57So, that's going to be sales and products.
11:01Now, we need to actually create our calculation.
11:04And so, this is the calculation we could have just created by itself if we wanted to.
11:08We want to save this, right?
11:09We want to save this.
11:10So, we're going to do sort.
11:14We're going to do sort by.
11:16Now, sort by has several things that we need to pass in.
11:19We have an array.
11:21We have a buy array in the sort order.
11:24So, what we're going to be actually sorting is this right here.
11:27So, that's going to be our B2 to B7.
11:30That's going to be our products.
11:31So, we need to specify the products.
11:34And then, our buy.
11:35What are we sorting these products by?
11:39We're doing it by how many were sold.
11:41So, that's going to be this right here.
11:42That's our sales.
11:43So, we need to specify the sales.
11:46Now, we have to specify whether it's ascending or descending.
11:49So, you want from highest to lowest.
11:51So, we're going to specify negative one.
11:53And we're going to close this with two parentheses at the end.
11:58Now, again, if we run this, it's not going to work.
12:01But, if we pass through at the end, we're going to come in here.
12:05We're going to pass through the sales.
12:07It's going to be right here.
12:08And then, we're going to pass through this right here, which is our products.
12:13And so, when we run this, it's going to organize all these for us.
12:18So, the biggest one was SAT prep high school book.
12:23And that's going to be this one right here at 439.
12:25The next one should be the SAT boot camp.
12:28And then, it should be SAT test booklet at 285.
12:33Then, the middle school prep.
12:34And then, tutoring.
12:35And then, college admission.
12:37And so, it did that all for us.
12:38And we're going to save this as a custom function.
12:42So, we're going to copy this whole thing.
12:44We're going to click escape so we can get out of there.
12:47We're going to go back to our formulas and click name manager.
12:50We're going to go to new.
12:53And for this one, we're going to say order by sales.
13:00And then, right down here, we're going to get rid of this refers to.
13:04And we're going to paste in our custom lambda function.
13:08So, now we have that one saved.
13:10Let's come over here.
13:11And let's actually, let's move this over.
13:15Because now, I want to bring this right here.
13:17We're going to say equal to.
13:18And this is going to be order by sales.
13:22And we have to pass through the sales and the products.
13:25So, we just choose the sales.
13:27We choose our product.
13:30We close it.
13:31And we're good to go.
13:32Now, if we want to apply this to all of these.
13:35Let's say we want to get February through December.
13:38There is one small thing that we have to do.
13:41Is we have to come in here and say that this is locked.
13:46Because we have to anchor it.
13:48If we bring this over, it's not going to work.
13:51Because now, this is referencing D2 and D7 and C2 and C7.
13:56So, these are the wrong ones.
13:59So, what we have to do is we have to anchor this.
14:01So, we can come up here and we'll hit F4.
14:03So, now, this C2 and C7 will move over with every time as we pass it over.
14:09Where this, which is the products, which is staying stationary.
14:12Is going to stay locked or anchored in place.
14:15So, now, we're just going to drag this all the way over.
14:19And let me go one more.
14:21And now, we have all of these organized for us very, very quickly.
14:25And so, that actually saves us a lot of time having to manually do this.
14:29Or having to rewrite that calculation using the order by, which is right here.
14:35Or the sort by every single time.
14:37So, it speeds it up a lot.
14:38And we're able to create these custom functions that are now saved.
14:42And we can use whenever we want.
14:44And so, if I'm in my job and I'm doing a ton of manual work.
14:47Or writing a lot of similar formulas all the time.
14:50Well, then, I can just create this custom function.
14:52And I can reuse it.
14:54And it saves a lot of time being able to do that.
14:57Now, the last thing that I want to mention is this comment right over here.
15:00When you select a function.
15:02You can usually get some type of quick example of what it is.
15:05Or a quick definition of what it is.
15:07And we can do that too.
15:08This is the discount applied.
15:10So, we can write applies 25% discount for seniors.
15:17We'll say 65 plus.
15:21And 10% for everyone else.
15:25There we go.
15:27Let's click OK.
15:28And let's just say we're going to use that.
15:30So, we're going to say discount applied.
15:32If we hover over this.
15:33We get this message right here.
15:35Applies 25% discount for seniors.
15:37And you can add anything you want in there.
15:39Right?
15:39If you want to explain what the parameters are.
15:41And what they do.
15:42You can put anything in there.
15:43And so, that can be really helpful as well.
15:45And so, that is how we can create these custom functions.
15:48That I think are extremely, extremely helpful.
15:51Especially when you're doing a lot of repetitive type of formulas and functions.
15:55You don't have to just rely on it and remember all this code.
16:00You can make it more dynamic using these Lambda functions.
16:03And saving them as a function that you can reuse over and over.
16:06So, I hope that that was helpful.
16:07I hope that you now know how to create your own custom functions.
16:10If you have not.
16:11Be sure to check out analystbuilder.com.
16:13Where you can take my full Excel for data analytics course.
16:16And if you like this video.
16:17Be sure to like and subscribe below.
16:19And I'll see you in the next one.
16:31I'll see you in the next one.
Comments