Skip to content
AnIntent

How to Use LET in Excel to Stop Repeating the Same Calculation

Use Excel's LET function to name a value or calculation once, reuse it inside a formula, and have Excel calculate it only one time.

AnIntent Editorial · · 4 min read

Hands typing on a laptop displaying a data spreadsheet
Photo by Gorilla ROI Data Connector on Unsplash
In this article
  1. How LET's arguments fit together
  2. Rewrite a repetitive formula with LET
  3. Microsoft's example: LET with FILTER
  4. Why a LET formula fails, and the fix
  5. LET names vs named ranges
Share this article

The Excel LET function lets you give a name to a value or calculation inside a formula, then use that name as often as you need. If you have a formula that repeats the same FILTER or lookup two or three times, LET shortens it, makes it easier to read, and stops Excel from calculating the same thing more than once.

This guide covers the syntax, how to rewrite a repetitive formula, Microsoft's FILTER example, and the mistakes that most often break a LET formula.

How LET's arguments fit together

=LET(name1, name_value1, calculation_or_name2, [name_value2, calculation_or_name3...])

Read it as pairs followed by one final calculation:

  • name1: the name you want to use, like a variable.
  • name_value1: the value or calculation that name stands for.
  • More name/value pairs if you need them.
  • The last argument: the calculation that uses those names and returns the result.

Microsoft compares these names to variables in programming. They exist only inside that one LET formula, so a name you define in cell E2 means nothing in cell E3 unless you define it there too.

The smallest working example from Microsoft is:

=LET(x, 5, SUM(x, 1))

Here x is the name, 5 is its value, and SUM(x, 1) is the final calculation.

Rewrite a repetitive formula with LET

  1. Find the repeated part. Look for an expression that appears more than once in your formula, such as the same FILTER or lookup.
  2. Give it a short, clear name. Pick something that describes the result, like filteredRange or price. It must follow Excel's rules for valid names in Name Manager.
  3. Start the formula with LET. Type =LET(, then the name, a comma, and the repeated expression.
  4. Add any other values you want to name. A criteria value such as a customer name is a good candidate, because you then change it in one place.
  5. Write the final calculation last. Use the names instead of the original expressions. This must be a calculation that returns a result.
  6. Close the bracket and press Enter.

The payoff is not just neatness. According to Microsoft, when a formula repeats an expression, Excel calculates it each time it appears. With LET, you call it by name and Excel calculates it once.

Microsoft's example: LET with FILTER

Here is a formula that shows a dash when the filtered result is blank, written without LET. The FILTER appears twice, so Excel works it out twice:

=IF(ISBLANK(FILTER(A2:D8,A2:A8="Fred")),"-",FILTER(A2:D8,A2:A8="Fred"))

Microsoft's version with LET names the criteria and the filtered range once:

=LET(filterCriteria,"Fred", filteredRange, FILTER(A2:D8,A2:A8=filterCriteria), IF(ISBLANK(filteredRange),"-", filteredRange))

Breaking it down:

  • filterCriteria holds the text "Fred".
  • filteredRange holds the rows of A2:D8 where column A matches filterCriteria. Notice that the second name uses the first one.
  • The final calculation, IF(ISBLANK(filteredRange),"-", filteredRange), shows a dash for blank cells and the filtered data otherwise.

To look for a different person, you change "Fred" in one spot. Because FILTER returns several rows, the result spills into neighbouring cells. If you see a spill error, see How to Fix the #SPILL! Error in Excel.

The same pattern works for lookups: put the lookup inside a name/value pair, then refer to the name wherever you used to repeat the full lookup. If you use INDEX and MATCH rather than XLOOKUP, this INDEX and MATCH guide covers the lookup itself.

Why a LET formula fails, and the fix

Most broken LET formulas come down to one of these:

  • The last argument is a name, not a calculation. Microsoft says the final argument must be a calculation that returns a result. If your formula ends on a name/value pair, add the calculation at the end.
  • An invalid name. Names must follow the same rules as names in Name Manager. If Excel rejects your formula, try a simpler name made of letters, with no spaces.
  • No pairs at all. Microsoft's LET help article says you must define at least one name/value pair. The same article says LET supports up to 126 pairs.
  • Using a name outside its formula. LET names apply only within that LET function. Copying a cell reference to another formula will not carry the name with it.

If you get a #NAME? error, paste Microsoft's minimal example, =LET(x, 5, SUM(x, 1)), into an empty cell. If that works, the problem is in your own formula: check the spelling of each name and run through the list above.

LET names vs named ranges

Both follow the Name Manager naming rules, but a LET name lives only inside the formula that defines it. That makes LET a good fit for intermediate steps that only one formula needs, such as a filtered list or a single lookup result, without adding entries to Name Manager.

Common questions

How many variables can one LET formula have?

Microsoft's help article says you must define at least one name/value pair and LET supports up to 126.

Does LET make formulas faster?

Microsoft says that when a formula repeats an expression, Excel calculates it each time. With LET, you name it and Excel calculates it once.

Can I use a LET name in another cell?

No. Microsoft says LET names apply only within the scope of the LET function that defines them.

What names can I use in LET?

Names must follow the rules for valid names in Name Manager. Short descriptive names like filteredRange work well.

white printing paper with numbers
How-to

How to Repeat Header Rows on Every Printed Page in Excel

Set Print Titles in Excel so your header row prints at the top of every page, and fix the button when it's greyed out.

· 4 min

More in Cloud & Productivity Software