How to use the LET function in Microsoft Excel

If you're tired of repeated calculations, hard-to-read formulas, and sluggish Excel worksheets, the LET function is your solution. It assigns simple names to complex calculations, making formulas clearer, easier to update, and much quicker by calculating expressions only once.

The LET function is available to those using Excel 2021 or later, Excel for Microsoft 365, Excel for the web , or the Excel mobile and tablet apps.

The LET syntax

The LET requires at least three arguments:

where:

• name_1 (required) is the name of the first variable

• value_1 (required) is the value or calculation assigned to the first variable name

• name_2 and value_2 (optional) are the second of up to a maximum of 126 name-value pairings

• calculation (required) is a calculation that uses the assigned names and values

The names and values assigned within a LET formula apply only to that formula, meaning they can't be referenced by another cell. However, the same names and values can be reused in other formulas.

Variable names must contain fewer than 255 characters (though you're not likely to need that many!), can't contain spaces or most punctuation characters, must start with a letter, and can't be the same as a cell reference or a name already assigned elsewhere in the Name Manager. Although names can contain a single letter, that letter can't be "r" or "c" (or their capitalized alternatives), as these are used as shortcuts elsewhere in Excel.

How the LET function works (and why it's worth using)

Let me show you how this works by using a very basic algebraic expression. Suppose your aim is to calculate the sum of two numbers, 2 and 3. To do this, you can define x as 2 and y as 3 in the LET function, before using these two variables in the addition:

Excel works through this formula from left to right, storing the named variables ( x and y ) locally. Then, once it reaches the final argument, which must use the defined variables, Excel returns a single result by adding them together.

Of course, with a calculation as simple as this, you could simply type:

However, explaining the LET function in these simple terms makes understanding it in real-life scenarios much more straightforward. What's more, it highlights the function's benefits:

• Easier logic:Naming variables when repeated in calculations makes the formula easier to understand—you can quickly see the logic and what is going on, even though the LET formula might end up being longer than the alternative.

• More efficient formula maintenance:As a result of their simplified logic, LET formulas are easier to update if adjustments are needed. Rather than having to update the same expression multiple times, you only need to make a single change.

• Better performance:When the same expression is repeated in a formula, it's calculated multiple times. However, when named in LET, it's only calculated once. As a result, the worksheet has to perform fewer calculations overall, a benefit that becomes particularly noticeable when you use lots of long formulas or have a sizable dataset.

Now, let's apply it to some real-world scenarios.

Replacing repeated IF arguments

One of the biggest benefits of the LET function is its ability to prevent repeated calculations, such as when using conditional IF statements .

To follow along as you read this guide, download a free copy of the Excel workbook used in the examples. After you click the link, you'll find the download button in the top-right corner of your screen.

Let's imagine you're calculating your employees' sales bonus. If someone's net sales (their gross sales minus their returns) exceed $10,000, the bonus is 5%. Otherwise, it's 2%.

Without the LET function, here's what you might type into cell D2 (and autofilled down column D):

I use direct cell references in this article to demonstrate how the formulas work. However, the LET function works just as well when the data is formatted as an Excel table and the formula uses structured references .

Although this method works, it causes three major issues:

• Inefficiency:Excel performs the B2-C2 subtraction three times every time the cell recalculates. This wastes processing time, especially in large spreadsheets.

• Poor readability:The formula is visually confusing because the IF logic is obscured by the repetitive calculation.

• High error risk:If you need to update the B2-C2 subtraction, like subtracting an additional fee, you would need to manually edit the formula in three places, increasing the chances of a typo.

The LET function can overcome these hurdles:

where:

• Net_Sales,B2-C2 creates a variable named Net_Sales , which is calculated by subtracting the returns (C2) from the gross sales (B2).

• IF(Net_Sales>10000,Net_Sales*0.05,Net_Sales*0.02) uses the same syntax as the non-LET alternative formula but uses the named variable instead of repeating the calculation.

When typing a formula in the formula bar, press Alt+Enter to move to the next line after each segment or argument. This helps ensure your formula is structured correctly.

In other words, the LET function creates a local, named variable ( Net_Sales ) that holds the result of the intermediate net sales calculation. Then, this calculation is used three times in the IF calculation without being recalculated or rewritten. Yes, the LET formula is longer than the traditional alternative, but this is intentional—the goal of using LET isn't to shorten the overall length but to maximize clarity and efficiency.

When you autofill the LET formula down the rest of column D, the variable updates automatically by default to apply to the row it's on.

Using LET with multiple names

While my previous example used a single variable to clean up one repetitive calculation, the real power of the LET function quickly becomes clear when you define multiple names.

This time, the aim is to calculate the weekly net profit margin. To do this, you need to follow sequential logic:

• Calculate the gross profit (gross sales minus cost of goods sold).

• Then, calculate the net income (the gross profit minus the operating expenses).

• Finally, calculate the net profit margin (the net income divided by the gross sales).

Here's how you would do this without using LET:

While this appears simple at first, it can cause frustration in the long term:

• Opaqueness and auditing:The formula is entirely made up of cell references and parentheses . Even if you were to use structured references instead, to understand how the gross profit is calculated, you would need to understand and parse the nested structure . Also, if the resulting margin is incorrect, there's no easy way to check the net income value without finding and isolating that portion of the formula.

• High error risk:As with all LET alternatives, if you need to adjust part of the formula that appears more than once (in this case, the reference to cell B2), it's easy to make a typo or accidentally only change one instance.

On the other hand, using the LET function clearly defines each intermediate step. Here's the formula for cell E2:

where:

• Sales,B2 tells Excel that cell B2 contains the variable named Sales.

• COGS,C2 tells Excel that cell C2 contains the variable named COGS (cost of goods sold).

• OpEx,D2 tells Excel that cell D2 contains the variable named OpEx (operating expenses).

• GrossP,Sales-COGS tells Excel that the variable named GrossP (gross profit) is calculated by subtracting COGS from Sales .

• NetI,GrossP-OpEx tells Excel that the variable named NetI (net income) is calculated by subtracting OpEx from GrossP .

• NetI/Sales tells Excel that the final calculation is to divide NetI by Sales .

Again, you'll notice that the LET formula is lengthier and visually more complex than the traditional reference-parentheses formula. However, it shows the sequential logic more clearly, it's easier to edit if the sales input cell changes, and the fact that it's self-documenting (in other words, the variables are clearly defined) means that the formula is always easy to understand, even years later, for someone who didn't write it.

You could apply the same multi-name logic to the net sales example I used above:

where the Net_Sales , Threshold , BHigh , and BLow variables are defined, before the IF function uses these variables to determine the overall bonus.

Absolute references ($) are used when determining the fixed variables so that when the formula is repeated down a column, the correct cells are still identified.

Using LET with dynamic arrays

I've already said many times that the LET function is essential for optimizing performance. However, in no scenario is this more true than when working with dynamic array functions , such as FILTER , SORT , and UNIQUE . Dynamic array functions are resource-intensive because they must scan, process, and spill entire arrays of results at once, placing significant strain on your system. The LET function can overcome this problem by ensuring the calculation happens only once.

Imagine you want to analyze a subset of a large dataset. Specifically, your aim is to calculate the range of prices (column C) for items sold in the West region (coded as W in column B).

Here's the non-LET formula you could type into cell F2:

There are two main problems with this:

• Inefficiency:The FILTER function is needed twice: once inside the MAX function, and once inside the MIN function. If the datasets were larger, this would significantly slow down your workbook.

• High error risk:The price range (C2:C17) and criteria (B2:B17) are repeated, so if you later need to update these, you would have to do so twice.

Using the LET alternative, the filtered array is named as a variable, so the resource-intensive operation is performed only once:

where:

• WSales,FILTER(C2:C17,B2:B17="W") tells Excel to filter the prices so that only those in the W region are included, and calls this filter WSales.

• MAX(WSales)-MIN(WSales) minuses the smallest number in this WSales filter from the largest.

As well as only needing to filter once, the new formula makes it clear what the final calculation is doing. What's more, you can rest assured that you're getting the correct maximum and minimum figures in the calculation because you've repeated the same stored array ( WSales ).

---

The LET and LAMBDA functions are often discussed together, but they serve very distinct purposes. Where the LET function creates local variables for use within a single formula, the LAMBDA function defines a name that acts as an entirely new, custom function. So, if your goal is to simplify and optimize a single complex formula within a single cell, you should use LET. On the other hand, if you're aiming to create a reusable function, go with LAMBDA.