• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course โ€“ Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Excel LET Function

You are here: Home / Excel Formulas / Excel LET Function
Excel LET Function
April 16, 2020 by Mynda Treacy

The Excel LET function* enables you to declare variables and intermediate calculations inside of a formula. Itโ€™s like the DAX VAR function or โ€˜letโ€™ inside of Power Query. Those familiar with Power Pivot, Power Query or programming will understand these terms, but donโ€™t be put off. LET is a dead easy function to learn and improves the readability and performance of your formulas.

The LET function syntax is:

Excel LET function

For example: =LET(ย x, 5, y, 10, x + y)

Result: 15

*The Excel LET function is currently in beta in the Office 365 Insiders build. This is a sneak preview for those who donโ€™t have it yet.

LET Function Arguments Explained

name1ย  - The name of the variable (cannot be the output of a formula or conflict with range syntax)

value1ย ย  - Values can be text, formulas/expressions, arrays, numbers, cell references, Boolean values or defined names

name2ย  - Optionally add more name and value pairs as required

value2ย ย  - As above

calculation - the formula/expression that uses the names within the LET function.

Notes:

  • LET will not error if you don't use all the names, but any unused names are redundant, so you should remove them to avoid confusion and unnecessary work for Excel
  • The formula should have an odd number of arguments i.e. pairs of names and values, then a calculation
  • The โ€˜calculationโ€™ can be contained inside of a name and value pair. e.g. here Iโ€™ve declared a name for the formula; โ€˜resultโ€™ =LET(x, 5, y, 10, result, x + y, result)
  • Values can reference previously declared names, but not names downstream e.g. x in this formula is later referenced in yโ€™s value argument =LET(x, 5, y, x+1, total, y * 2, total) = 12
  • Avoid using names in the LET function that are already defined names in the name manager. If you use a name already defined in the name manager, LET will ignore the name manager version
  • Names defined inside of LET are available in the intellisense drop down.
  • LET formulas can be defined as names in the name manager

Watch the Excel LET Function Video

Subscribe YouTube

Download the Excel LET Function Workbook

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the Excel Workbook and follow along. Note: This is a .xlsx file please ensure your browser doesn't change the file extension on download.

When to use the LET Function

Improve Formula Readability

Naming variables, like ranges being referenced etc., makes it easier to understand what the formula is doing. Before the LET function we would define names in the Name Manager for this purpose, however LET now enables us to define names more quickly inside of the formula itself making it quicker to write and quicker to interpret.

For example, in the formula below we can easily understand that it is calculating sales from cells C2:C500 including GST of 10%, without the need to refer to any other cells, sheets or defined names:

=LET(

GST, 10%

Sales, C2:C500,

SalesIncl.GST, Sales*1+GST,

SalesIncl.GST)

The limitation of defining names inside of LET is that those names can only be used inside of that specific LET formula whereas names defined in the name manager can have the scope of the workbook.

Improve Calc Performance

The other reason to use LET is for improved performance through the elimination of duplicate calculations. By naming calculations inside of LET they are being evaluated once at the beginning of the formula and the results can then be re-used multiple times in the formula without requiring further calculations. This can significantly improve performance.

For example, the formula below performs the SUM at least twice. Once for the logical test and again for either the TRUE or FALSE outcome:

Excel LET Function calculation example 1

Whereas with LET we can reduce this to a single calculation by declaring it as a name, SalesCY, and then reusing the name later in the formula:

Excel LET Function calculation example 2

Obviously in practice there will be more efficiency to be gained with more complex formulas.

Excel LET Function Examples

Referencing Names in Values

Names you define can be used inside of downstream value arguments. For example, the value argument for the name โ€˜aggโ€™ in the formula shown below references the name โ€˜selectโ€™ which was previously declared.

referencing LET names in values 1

When youโ€™re authoring a LET function, prior names will be available in the intellisense drop down to choose from, just like a name defined in the name manager:

referencing LET names in values 2

Relative References

In this example I want you to take notice of the use of relative references in the name arguments.

First, the data Iโ€™m using is called Table1. It contains sales by brand and product category:

Excel LET Function relative references table 1

Below Iโ€™ve summarised the table by brand and month using a LET formula:

Excel LET Function relative references summary

The formula in cell G4 (wrapped onto separate lines for readability) is:

=LET(

MonthStart, G$3,

MonthEnd, EOMONTH(G$3,0),

Brand, $F4,

BrandRng, FILTER(Table1[Sales], (Table1[Brand]=Brand)*(Table1[Date]>=MonthStart)*(Table1[Date]<=MonthEnd)),

SUM(BrandRng)

)

You can see in the image above the LET formula nests the EOMONTH function ย and the FILTER function which perform calculations that are later used in the SUM calculation argument.

Also note that the first three โ€˜valueโ€™ arguments for each name use relative references in either the row or column, thus allowing the LET function to be copied to the remaining cells in the table.

Tip: When copying formulas with Table Structured References, be sure to copy and paste as opposed to left clicking and dragging to ensure the absolute referencing of the structured references is applied correctly.

Arrays as an Input and Output

You may have already noticed in the second example that the LET function can accept arrays as inputs, but it can also return arrays as outputs. The โ€˜RankArrayโ€™ name in the example below has an array as a value {1;2;3}, and it returns an array, being the list of top 3 brands which spills to the cells D5:D7:

Excel LET Function arrays as an input and output

Debugging LET Formulas

You can easily check the results returned by a name and value pair by placing that name in the last argument (which is โ€˜calculationโ€™). In the example below in cell C6 the calculation has been declared as a name (calc) and value pair, and the last argument simply repeats the last name to return the result:

debugging LET formulas 1

However, we can test any name and value pair by replacing the last argument with a different name. For example, say I want to check how the โ€˜Rngโ€™ name evaluates. In the image below Iโ€™ve placed โ€˜Rngโ€™ in the last argument of the LET formula and you can see in cell C6 it now spills the sales values from Table1:

debugging LET formulas 2

I can then simply replace โ€˜Rngโ€™ with โ€˜Calcโ€™ in the last argument to return the final calculation I want, without losing the formula I authored for โ€˜Calcโ€™.

Excel LET Function

More Dynamic Arrays Posts

excel shaping arrays

New Array Shaping Excel Functions

The Excel Shaping Array Functions makes it easier than ever to reshape arrays and ranges using these purpose built functions
list_first_monday_date_in_each_month

List First Monday Date in Each Month

With the DATE and WEEKDAY functions we can easily list first Monday date in each month. With EOMONTH you can easily switch to the last Monday of each month.

Excel XLOOKUP Function

The new Excel XLOOKUP Function replaces the need for VLOOKUP, HLOOKUP, INDEX & MATCH and more. Itโ€™s available in Office 365.
Excel Hash 2019

Excel Hash 2019 – Dynamic Playoffs Table

Excel Hash Competition 2019 โ€“ dynamic playoffs table uses Icons, XOR, Dynamic Arrays and Linked Pictures to create an integrated solution.

Extract Values Present in Two Lists

Use an Excel formula to extract values present in two lists of varying sizes. Watch the video and download the Excel file with examples.
Excel Sorted Dynamic Unique List

Excel Sorted Dynamic Unique List

Excel Sorted Dynamic Unique Lists have never been so easy now that we have dynamic array formulas.
excel dynamic arrays

Excel Dynamic Arrays

The new Excel Dynamic Arrays will change the way you work with Excel formulas. As well as a host of new functions, CTRL+SHIFT+ENTER is no longer required.

More Excel Formulas Posts

excel formula by example

Excel Formula by Example

Excel can now write a formula by example. Simply give it an example or two of the result and Excel will write the formula.
ai-aided excel formula editor

AI Aided Excel Formula Editor

Save time with this free AI Excel formula editor add-in that writes, edits, improves and interprets formulas for you!
top excel functions for data analysts

Top Excel Functions for Data Analysts

Must know Excel Functions for Data Analysts and what functions you donโ€™t have to waste time learning and why.
excel advanced formula environment

Excel Advanced Formula Environment

Excel Advanced Formula Environment is a long awaited, new improved way to write, name and store Excel formulas.
Pro Excel Formula Writing Tips

Pro Excel Formula Writing Tips

Must know Excel formula writing tips, tricks and tools to make you an Excel formula ninja, including a new formula editor.
excel shaping arrays

New Array Shaping Excel Functions

The Excel Shaping Array Functions makes it easier than ever to reshape arrays and ranges using these purpose built functions
excel nested if functions what not to do

Excel IF Formulas and What Not To Do

Excel IF formulas can get out of hand when you nest too many IFs. Not only do they become unwieldy theyโ€™re difficult for anyone to understand
excel image function

Excel IMAGE Function

The Excel IMAGE Function enables you to embed images in a cell using a formula. It supports BMP, JPG/JPEG, GIF, TIFF, PNG, ICO, and WEBP files

Excel VSTACK and HSTACK Functions

New Excel VSTACK and HSTACK functions makes combining arrays of cells easy and with some clever tricks we can extend their capabilities.
identify overlapping dates and times in excel

Identify overlapping dates and times in Excel

How to identify overlapping dates and times in Excel with a formula that checks a range of cells. Works with Dates and Times.


Category: Excel FormulasTag: Dynamic Arrays
Previous Post:Searchable Drop Down List in ExcelSearchable Drop Down List in Excel
Next Post:Select Multiple Items from Drop Down (Data Validation) Listselect multiple items from drop down data validation list

Reader Interactions

Comments

  1. Matthew Smith

    September 30, 2022 at 6:22 am

    Finally got to use LET at work. Thanks for helping me understand it!

    Reply
    • Mynda Treacy

      September 30, 2022 at 8:31 am

      Yay! Glad we could help.

      Reply
  2. Jeff Robson

    May 14, 2022 at 6:07 pm

    A suggestion to simplify the formula in the Relative References section further would be to use SUMIFS instead of FILTER then SUM

    e.g. BrandTotal = SUMIFS(Table1[Sales], Table1[Date], “>=” & MonthStart, Table1[Date], “<=" & MonthEnd, Table1[Brand], Brand)

    Reply
  3. Wes Stewart

    October 7, 2021 at 2:17 pm

    Not a question, but a comment.

    Thank you for your LET function tutorial and the sample workbook. I managed to expand on the function example to be able to select a Brand and find total sales for each month for each for each category. Took a bit of work, but I am happy with myself (LOL).

    Reply
    • Mynda Treacy

      October 7, 2021 at 4:19 pm

      Well done! Great to hear, Wes ๐Ÿ™‚

      Reply
  4. Teresa

    October 5, 2021 at 3:50 am

    What would be the formula column c is I want to divide a by b but sometimes b is zero ?

    Reply
    • Philip Treacy

      October 5, 2021 at 8:54 pm

      Hi Teresa,

      Not sure you need to do this with LET?

      If you want to avoid divide by zero errors use the IFERROR function

      In your cell in Column C you’d have

      =IFERROR(A1/B1,"")

      Regards

      Phil

      Reply
  5. Steve Buckley

    March 17, 2021 at 6:59 am

    I cannot find a definite answer about the general availability of the LET function. Do you know when it was (or is it?) released for general use, not when it was released for those in the Microsoft Insiders program? I want to use it in applications that other people use, but I don’t want them to see #NAME? when they use my spreadsheet. Thanks!

    Reply
    • Mynda Treacy

      March 17, 2021 at 8:53 am

      Hi Steve, LET has been GA for a couple of months now. The only reason you’d see errors is if users are on a semi-annual update channel instead of monthly.

      Reply
  6. Peg Moter

    March 14, 2021 at 12:48 am

    I loved your video on the LET function! Is there a way to download the raw data workbook so that I can follow along and create the formulas as you do? The workbook I downloaded has that all done, and I can see the formulas, but I learn by doing as I see. Thanks!

    Reply
    • Mynda Treacy

      March 14, 2021 at 11:30 am

      Hi Peg, great to hear you enjoyed my LET function video ๐Ÿ™‚ I don’t have a blank workbook, but you can just use an empty cell to recreate the formulas yourself, or delete mine and start again.

      Mynda

      Reply
  7. NORMAN JOHN HARKER

    February 4, 2021 at 10:47 am

    Simple Question. Why is LET put in the Text function category? I would have thought Lookup & Reference was more appropriate.

    Reply
    • Mynda Treacy

      February 4, 2021 at 1:10 pm

      Hi Norman,

      I don’t know the logic for putting LET in the text function category. Maybe it didn’t really fit in any category, so it was least out of place there. I don’t think I’d call it a lookup and reference function either. It needs a new category ๐Ÿ™‚

      Mynda

      Reply
  8. Peta

    May 6, 2020 at 9:19 pm

    Thanks so much Mynda for another great & clear how to..
    I appreciate your efforts.

    Reply
    • Mynda Treacy

      May 9, 2020 at 10:10 am

      Cheers, Peta! Glad you liked it ๐Ÿ™‚

      Reply
  9. Kevan Rice

    April 21, 2020 at 5:02 pm

    Great lesson on the LET function, thank you
    When it is released to the generic Office 365 version, will it work on prior versions of Excel or would you have to have Office 365?

    Reply
    • Mynda Treacy

      April 21, 2020 at 5:08 pm

      Thanks, Kevan! LET won’t ever go back and work in earlier versions of Excel. Only Office 365 and the next perpetual licence e.g. Excel 2022!

      Reply
  10. bernard liengme

    April 16, 2020 at 10:52 pm

    Pease explain the use of # in the formula =XLOOKUP(D5#,A5:A14,B5:B14) in the Arrays sheet

    Reply
    • Mynda Treacy

      April 17, 2020 at 8:34 am

      Hi Bernard, the # is a spilled range operator. It is a shortcut to referencing the whole spilled range, so instead of entering D5:D7 we can use D5# and if the spilled range changes size it will automatically find the correct range. More on dynamic array functions here.

      Reply
  11. Jon Wittwer

    April 16, 2020 at 11:30 am

    This is about how many times over the years that I’ve thought about wanting to define variables within a formula:

    =LET(Awesome,1,Awesome*150)

    This function is truly brilliant. Kudos to the Excel team, and thank you for the great explanation.

    I am SO excited to start using this!

    Reply
    • Mynda Treacy

      April 16, 2020 at 11:33 am

      Thanks for your kind words, Jon! Have fun with LET ๐Ÿ™‚

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Featured Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

239 Excel Keyboard Shortcuts

Download Free PDF

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 ยท My Online Training Hub ยท All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.