If you’ve ever stared at a monster Excel formula and thought, “There has to be a better way,” well, there is. It’s called LAMBDA and LET, and you don’t need to go to coding school to learn how to use them.

First: I Use LET to Clean Up Messy Formulas

Have you ever built a formula so long that it should have had a scroll bar? The LET function can fix that.

LET allows you to assign names to parts of your formula—kind of like giving nicknames to values or calculations. Instead of repeating the same sub-formula five times, you define it once, give it a name, and then reuse that name throughout your calculation.

As an example, I’ll use LET to grade student scores. Without LET, the formula would get messy really fast:

=IFS(AVERAGE(B2:E2) > 85, "Grade A", AVERAGE(B2:E2) >= 70, "Grade B", AVERAGE(B2:E2) 

These are three identical AVERAGE(B2:E2) calls, which are quite inefficient and hard to read. The LET function allows us to define it once:

        =LET(student_average, AVERAGE(B2:E2), IFS(student_average > 85, "Grade A",
 student_average >= 70, "Grade B", student_average < 70, "Grade C"))
    

Now the formula is cleaner, more readable, and easier to maintain. If I update the student_average logic, the change applies throughout.

You can define up to 126 variables in one LET function (yes, really), which makes it perfect for calculating complex metrics, building logic-packed single-cell dashboards, or actually understanding a formula you wrote six months ago.

Related

Excel Functions vs. Formulas: Here’s the Difference

Excel functions are the building blocks; formulas are how you bring them to life. Here’s the breakdown.

Then: I Use LAMBDA to Create My Own Excel Functions

I love LAMBDA so much because it allows me to take a giant, ugly formula and turn it into something manageable (like

=GrowthRate(B2, C2)

). You define it once, save it with a name, and reuse it across your workbook. When you need to tweak the logic, you change it in one spot (via Excel’s Name Manager), and the update applies everywhere.

How to Build Your First Custom Function

The process is more straightforward than you’d think:

1. Launch the workbook where you want to use the custom function.
2. Replace your actual values with placeholder names (like “price” and “rate”)
3. Test the LAMBDA function in any cell to see if it works.
4. Hit Ctrl+F3 to open Name Manager, click New and paste in your LAMBDA string into the Refers To field.
5. Give your new function any name you like, and put in any comments that might be helpful later on.

Now you can type the formula below, just like any built-in Excel function:

=YourFunctionName()

Let’s say you’re a freelance tutor and are constantly calculating grades. You might have to use a formula like this:

        =IFS(A1>=80,"Honors", A1>=50,"Pass", A1 < 50,"Fail")
    

Instead of typing this code every time, you can create a Score Calculator function:

        =LAMBDA(score, IFS(score>=80,"Honors", score>=50,"Pass", score < 50,"Fail"))
    

Save this in Name Manager as “Calculator,” and now you just type the below anywhere in your workbook:

=Calculator(B2)

You can even use LAMBDA for cooler functions, like one that strips unwanted characters one by one, or one that keeps multiplying until it hits 1 (just because you can).

These cooler functions might look complex at first, but once saved, all LAMBDA functions become plug-and-play. And thanks to AI chatbots, you can easily craft the formulas and query strings you need.

Related

I Let AI Handle Complex Excel Formulas for Me—You Should Too

Let AI handle the syntax so you can focus on results.

LET and LAMBDA: Human-Friendly Formulas

Separately, LET and LAMBDA are great. But when you fuse them, they’re a power couple.

When you’re creating a LAMBDA function, you can use LET inside the string to name pieces of the logic. Think of it like you’re breaking a complex recipe into clear steps before baking the final cake. You’ll give each ingredient a name so that each step makes sense.

Let’s say you need to calculate triangle areas using Heron’s formula, which is a notoriously messy calculation:

=SQRT(((a+b+c)/2)*(((a+b+c)/2)-a)*(((a+b+c)/2)-b)*(((a+b+c)/2)-c))

Good luck debugging that six months from now. Thankfully, with LET and LAMBDA, you can clean it up:

=LAMBDA(a, b, c, LET(s, (a + b + c) / 2, SQRT(s * (s - a) * (s - b) * (s - c))))

Instead of repeating (a+b+c)/2 four times, you now have s appearing four times, which is way more digestible. On top of this, you can save the full LAMBDA function in Name Manager. Now, that messy calculation becomes readable, efficient, and easy to reuse.

Related

9 Excel Mistakes You Should Never Make

Don’t let these preventable Excel mistakes derail your projects.

You can also build entire reports inside a single cell using this combo. Imagine a function that takes your sales data and spits out a formatted one-cell monthly summary:

=LAMBDA(p_col, s_col, LET(_total, SUM(s_col), top, INDEX(pcol, XMATCH(MAX(s_col), s_col)), TEXTJOIN(CHAR(10), TRUE, "Report", "Total: " & TEXT(_total, "$#,##0.00"), "Top: " & _top)))

Save this as MonthlyReport and call it with the formula below. With one function in one cell, you’ll get a complete report.

=MonthlyReport(B:B, C:C)

Once you start using LET and LAMBDA, you stop thinking like a formula machine and start thinking like a person. LET gives your formulas structure by naming each step, while LAMBDA turns logic into reusable, human-friendly functions. Together, they make Excel formulas easier to write, read, and reuse.

Related

Here’s How I Write Complex Excel Formulas Without Breaking a Sweat

Complex Excel formulas don’t have to be intimidating.

So the next time you find yourself writing a formula that looks like it belongs in a sci-fi novel, pause. Ask yourself: Could this be a LAMBDA? Chances are, the answer is yes.