Excel contains somewhere between 400 and 500 built-in worksheet functions (Sum, Count, Lookup, etc etc).
For those situations where a function doesn’t exist, the alternative is often a long-winded formula, the logic and maths of which you’d have to work out yourself (or Google it!)
“What situations are they?” I hear you ask. Let me give you a few of examples:
- Excel has a built-in function to calculate the last day of a given month (EOMONTH) but not the first day
- Excel has a built in function to generate the month number from a date (MONTH) but not the quarter number from a date
- Excel has a built in function that converts the First Letter of EVERY word in a cell to Upper Case (PROPER) . But what if you wanted to convert the First letter of only the first word in a cell to upper case? (“Mary Had A Little Lambda” v “Mary had a little lambda”)?
Taking the 3rd example, instead of using the following “mega-formula”:
=UPPER(LEFT(A1,1))&LOWER(RIGHT(A1,LEN(A1)-1))
It would surely be easier to type =UPPERFIRST(A1)
UPPERFIRST is the name of a custom function, defined by me. You can name the function anything you like. The name Upperfirst makes sense to me.
Building custom functions in Excel isn’t new. You’ve always been able to do it using VBA, the built-in programming and automation language. However, for non-programmers, VBA can have a steep learning curve and may be a bit scary.
Also, some people see VBA as a security threat and the fact that VBA isn’t supported on all platforms (i.e iPads or the web version Excel) is also a concern to some
Enter LAMBDA. LAMBDA was introduced into Excel in late 2020 although initially only to Microsoft 365 subscribers on the Insider Beta Channel. LAMBDA allows you to create your own worksheet functions. Want to know more? Watch the video below…