New Excel function LET

LET lets you name parts of your formulas!

This is one of those features that has more power than we might first realize; chances are its use will increase dramatically over time as its utility becomes more obvious.

Why should you use the LET function?

Because it can help you make and edit spreadsheets faster, and it can make them recalculate faster.

Those are three great reasons!

Make spreadsheets faster with LET

Instead of typing some great long monstrosity of a formula with a lot of repeated parts in it, just define the common part with LET. This reduces the time you take to build those types of formulas.

Edit spreadsheets faster with LET

Changing or troubleshooting your long and repetitive formula? (Or worse, someone else’s)!

This has the potential to become much easier wherever LET is used. You need to update, tweak or puzzle over far less of the formula.

There is also less chance of having to deal with a long repetitive formula that should be repetitive, but has been put together mistakenly and isn’t working quite as intended.

Help your spreadsheets calculate faster with LET

This benefit alone is worth considering using LET quite a lot. By defining parts of your formula (albeit temporarily), the software can often calculate it much faster than when you re-input every instance of the defined part separately.

How do you use the LET function?

The let function has two parts:

1. Definition(s)
2. Formula

The definition(s) go at the start. You must have at least 1 definition, but you can have many more.

Each definition has two parts: a “name” you are defining, and a “value” you want it to contain. This does not literally mean value; the “value” can be a formula.

The formula part is whatever you would have written anyway, but with any defined parts of your formula replaced with the definition.

So, the LET formula actually has these parts:

1. Definition
   1. Name
   2. Value
2. Formula

If you use more than one definition, it becomes:

1. Definition 1
   1. Name 1
   2. Value 1
2. Definition 2 (optional)
   1. Name 2
   2. Value 2
3. … Definition n (optional)
   1. Name n
   2. Value n
4. Formula

Syntax

There is a comma in between each part in the list above.

The syntax to use is:

The part between the square brackets ([]) is optional. (The brackets themselves are not part of the formula).

The syntax is represented in the documentation like this instead:

These are the same thing. We just think ours is easier to understand (but you be the judge).

Note that you do not need to put each name/value pair in parenthesis(). So:

• Correct: =LET(pi_approx,22/7,round_places,5,100000*ROUND(ABS(PI()-pi_approx),round_places))
• Incorrect: =LET((pi_approx,22/7),(round_places,5),100000*ROUND(ABS(PI()-pi_approx),round_places))

For those examples, there is little point in using the LET function at all; it’s only when you use your defined values more than once in the same formula that it starts to earn its keep.

If you are the kind of person who immediately wonders what is the maximum possible number of name/value pairs that can be used with the LET function, then evaluate the above “correct” formula to find out!

Does LET sound familiar?

If LET sounds familiar, you may be used to using LET statements in programming (such as with C#, for example) or querying data (such as with SQL). You may also have used LET to define a variable in VBA.

The new LET function in Excel has a broadly similar purpose; to define something for reuse within that particular function.

This new feature hints at a more general goal of adding a few additional capabilities to formulas that enhance its programming-like capability. We anticipate seeing more “programming” type features make their way into formulas for two reasons:

1. Formulas are used for logical operations and calculations, and Excel formula syntax is essentially a programming language “in disguise” (i.e. with many users not realising it). Therefore, most of the basic fundamental capabilities of programming languages should be available to formulas (if warranted, and not too complex to implement or use) ; and
2. Building more capabilities into formulas and functions is a good thing, and makes those capabilities more accessible to users (both creators and collaborators). There is less need to resort to macros or VBA to achieve something relatively straightforward if it can be done using a cell formula instead.

We suggest LET is a welcome addition to the function list.

Learn more

Read more about how to use the LET function at the Office Support page for the LET function.