Excel tricks that let you write formulas more easily

If you've ever stared at a long Excel formula and thought "is there a better way to write it?", the good news is that there is. Use LAMBDA and LET - you don't need to take a programming class to learn how to use them.

Note : These awesome functions are now available to all Microsoft 365 subscribers , even if you're on the basic plan. You no longer have to wait to get access to the beta or Insider builds.

Using LET to clean up messy formulas

Have you ever built a formula so long that it needed scroll bars? The LET function can fix that.

LET lets you assign names to parts of a formula—kind of like giving aliases to values ​​or calculations. Instead of repeating the same subformula five times, you can define it once, name it, and then reuse that name throughout your calculations.

The example will use LET to grade students. Without LET, the formula would get messy very quickly:

=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 this function 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 more compact, readable and maintainable. If you update the student_average logic, the change will be applied across the entire table.

You can define up to 126 variables in a LET function, which makes it perfect for calculating complex metrics, building single-cell dashboards filled with logic, or really understanding a formula you wrote 6 months ago.

Using LAMBDA to create your own Excel functions

Many people love LAMBDA because it allows them 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 entire workbook. When you need to adjust the logic, you change it in one place (via Excel's Name Manager) and the update applies everywhere.

How to build your first custom function

The process is simpler than you think:

1. Launch the workbook in which 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. Press Ctrl + F3 to open Name Manager, click New and paste your LAMBDA string into the Refers To field .
5. Give your new function any name you like and include any comments that might be useful later.

You can now enter the formula below, just like any built-in Excel function:

=YourFunctionName()

Tip : LAMBDA functions are saved in a specific workbook, so they won't automatically work in other workbooks. To reuse them across files, you can try the Advanced Formula Environment add-in to import or export your functions.

Let's say you're a freelance tutor and you're 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 can just enter the content below anywhere in your workbook:

=Calculator(B2)

You can even use LAMBDA for more interesting functions, like a function that deletes unwanted characters one by one, or a function that keeps multiplying until it reaches 1.

These fancy functions may seem complicated at first, but once saved, all LAMBDA functions are very simple to run. And thanks to the AI ​​chatbot, you can easily create the necessary formulas and query strings.

LET and LAMBDA: Human-friendly formulas

LET and LAMBDA work great on their own. But when you combine them, they are a power couple.

When creating a LAMBDA function, you can use LET inside the string to name the logical parts. Think of it like breaking down a complex recipe into clear steps before baking the final cake. Name each ingredient so that each step makes sense.

Suppose you need to calculate the area of ​​a triangle 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 in 6 months. Luckily, with LET and LAMBDA you can clean up the formula like this:

=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 much easier to understand. Best of all, you can store the entire LAMBDA function in the Name Manager. Now that messy calculation is readable, efficient, and reusable.

You can also build an entire report inside a single cell using this combination. Imagine a function that takes sales data and outputs a monthly summary in a formatted cell:

=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 function as MonthlyReport and call it with the formula below. With one function in one cell, you will get the complete report.

=MonthlyReport(B:B, C:C)

When you start using LET and LAMBDA, you stop thinking mechanically. LET provides structure to formulas by naming each step, while LAMBDA turns the logic into reusable, human-friendly functions. Together, they make Excel formulas easier to write, read, and use.

So next time you find yourself writing a formula that looks like it belongs in a sci-fi novel, stop and ask yourself: Can LAMBDA be used? Chances are, the answer is yes.

Samuel Daniel

Update 01 July 2025