Simon Sez IT

Online software training and video tutorials for Microsoft, Adobe & more

  • Course List
    • Adobe
      • Dreamweaver
        • Dreamweaver CC
        • Dreamweaver CS6
        • Dreamweaver CS5
        • Dreamweaver CS4
      • Flash
        • Flash CS5
      • InDesign
        • InDesign CS6
        • InDesign CS5
      • Photoshop
        • Photoshop CS6
        • Photoshop CS5
        • Adobe Photoshop CS4
      • Photoshop Elements
        • Photoshop Elements 2022
        • Photoshop Elements 2019
        • Photoshop Elements 2018
        • Photoshop Elements 15
        • Photoshop Elements 14
        • Photoshop Elements 13
        • Photoshop Elements 12
        • Photoshop Elements 11
        • Photoshop Elements 10
        • Photoshop Elements 9
        • Photoshop Elements 8
    • Microsoft
      • Access
        • Access 2021
        • Access 2019
        • Access 2019 Advanced
        • Access 2016
        • Access 2016 Advanced
        • Access 2013
        • Access 2013 Advanced
        • Access 2010
        • Access 2010 Advanced
        • Access 2007
      • Excel
        • Data Analytics in Excel
        • Excel 2021 Advanced
        • Excel 2021 Intermediate
        • Excel 2021 Beginners
        • PivotTables for Beginners
        • Excel Dashboards
        • Advanced Formulas in Excel
        • Excel for Business Analysts
        • Advanced PivotTables
        • Power Pivot, Power Query and DAX in Excel
        • Excel 2019 Beginners (Mac)
        • Excel 2019 Beginners
        • Excel 2019 Advanced
        • Excel 2016 Beginners
        • Excel 2016 Intermediate
        • Excel 2016 Advanced
        • Excel 2013
        • Excel 2013 Advanced
        • Excel 2010 Beginners
        • Excel 2010 Advanced
        • Excel 2007
      • OneNote
        • OneNote Desktop and Windows 10
        • OneNote 2016
      • Outlook
        • Outlook 2021
        • Outlook 2019
        • Outlook 2016
        • Outlook 2013
        • Outlook 2010
        • Outlook 2007
      • Power Automate
        • Introduction to Power Automate
      • Power BI
        • Power BI
        • Power BI Intermediate
      • PowerPoint
        • PowerPoint 2021
        • PowerPoint 2019
        • PowerPoint 2016
        • PowerPoint 2013
        • PowerPoint 2010
        • PowerPoint 2007
      • Project
        • Project 2021
        • Project for the Web
        • Project 2019
        • Project 2019 Advanced
        • Project 2016
        • Project 2016 Advanced
        • Project 2013
        • Project 2013 Advanced
        • Project 2010
        • Project 2010 Advanced
      • Publisher
        • Publisher 2013
      • SharePoint
        • SharePoint Online
        • SharePoint Foundation 2013
        • SharePoint Server 2013
        • SharePoint Foundation 2010
      • Teams
        • Microsoft Teams
      • VBA
        • Macros and VBA for Beginners
        • VBA for Excel
        • VBA Intermediate Training
      • Visio
        • Microsoft Visio 2019
        • Visio 2016
        • Visio 2013
        • Microsoft Visio 2010
      • Windows
        • Windows 11
        • Windows 10 (2020 Update)
        • Windows 10
        • Windows 8
        • Windows 7
        • Windows Vista
      • Word
        • Word 2021
        • Word 2019 Advanced
        • Word 2019
        • Word 2016
        • Word 2013
        • Word 2010
        • Word 2007
    • QuickBooks
      • QuickBooks
        • QuickBooks Desktop Pro 2022
        • QuickBooks Pro 2021
        • QuickBooks Online Advanced
        • QuickBooks Online
        • QuickBooks Canada
        • QuickBooks Pro 2020
        • QuickBooks 2019
        • QuickBooks 2018
        • QuickBooks Pro 2017
        • QuickBooks Pro 2016
        • QuickBooks Pro 2015
        • QuickBooks Pro 2014
        • QuickBooks Pro 2013
        • QuickBooks Pro 2012
        • QuickBooks Pro 2011
        • QuickBooks Pro 2010
        • QuickBooks Pro 2009
    • Web Development
      • AngularJs
        • AngularJS Crash Course
      • Dreamweaver
        • Dreamweaver CC
        • Dreamweaver CS6
        • Dreamweaver CS5
        • Dreamweaver CS4
      • Bootstrap
        • Bootstrap Framework
      • Html/CSS
        • HTML/CSS Crash Course
        • HTML5 Essentials
      • Python
        • Python Object-Oriented Programming
        • Pandas for Beginners
        • Introduction to Python
      • Java
        • Java for Beginners
      • JavaScript
        • JavaScript for Beginners
        • jQuery Crash Course
      • MySql
        • MySQL for Beginners
      • PHP
        • PHP for Beginners
        • Advanced PHP Programming
      • XML
        • XML Crash Course
    • Data Analysis
      • Financial Modeling
        • Financial Risk Management
        • Financial Forecasting and Modeling
      • Alteryx
        • Alteryx Advanced
        • Introduction to Alteryx
      • Power BI
        • Power BI Intermediate
        • Power BI
      • Qlik Sense
        • Qlik Sense Advanced
        • Qlik Sense
      • R Programming
        • R Programming
      • Tableau
        • Tableau Desktop Advanced
        • Tableau Desktop
      • Python
        • Python Object-Oriented Programming
        • Pandas for Beginners
        • Introduction to Python
    • Work Productivity
      • Google Sheets
        • Google Sheets for Beginners
      • Confluence
        • Introduction to Confluence
      • Monday
        • Getting Started in Monday.com
      • Asana
        • Asana for Employees and Managers
        • Introduction to Asana
      • Jira
        • Getting Started in Jira
  • For Business
  • About Us
    • Testimonials
    • Contact Us
    • FAQ
    • Membership
    • About Us
  • Pricing
  • Free Resources
  • Sign In
  • Get Started
Home > Microsoft Excel > Using the LET Function in Excel

Using the LET Function in Excel

This LET Function in Excel tutorial is suitable for users of Excel for Microsoft 365.  

OBJECTIVE 

Use the new LET function in Excel to define a calculation once and use it multiple times in a single formula.  

THE LET FUNCTION EXPLAINED 

The new LET function is part of the Text group of functions in Excel. It allows you to assign names to calculate results inside a formula.  

If you find yourself writing the same expression multiple times in a formula, LET basically allows you to name the expression. Then Excel will calculate the result using the name.  

Not only is this a great time-saving function, but it also makes your formulas easier to read and understand, as you don’t have to remember what a specific cell reference or range refers to. It’s right there in the formula! In front of your eyes!  

If you like the LET Function, you might also enjoy learning about Dynamic Array Functions in Excel.

Syntax 

=LET (name1, value1, [name2/value2], …, calculation) 

To use the LET function, you must define at least one name/value pair (variable), and LET supports up to 126 pairs.  

BASIC EXAMPLES OF LET 

Let’s start with a very basic example using one named variable:  

In this calculation, I am declaring my variable name as ‘var’ and assigning a value of ‘10’ to the variable. I am then recalling the variable name and adding 5. So, the answer here is 15.  

Let’s add a second variable: 

This time I am defining two named variables and assigning values. 

My first variable, ‘var’, has been assigned the value of 10. The second variable, ‘x’, has been assigned the value of 2.  

So therefore, the result of the calculation var+x is 12.  

Let’s add a third variable: 

In this example, I am defining three named variables and assigning values.  

My first variable, ‘var’, has been assigned the value of 10. My second variable, ‘x’, has been assigned the value of 2. My third variable of y has been assigned the value of 3. (grossmancapraroplasticsurgery)   

The calculation contained in brackets (parenthesis) is calculated first.  

So, var/x equates to 10/2 to give the result of 5.  

5+y equates to 5+3 to give the result of 8.  

PRACTICAL EXAMPLES OF LET 

EXAMPLE 1 

In this example, I am using the LET function to work out each employee’s total salary once a $2000 bonus has been applied.  

The variable ‘bon’ has been assigned to the value ‘2000’. The formula then calculates the result of salary+bon.  

EXAMPLE 2 

In this example, I want to sum the totals sales for all the travel companies on the list and, if the total is above $300,000, apply a 10% sales tax. If not, then apply a 5% sales tax.  

This can be accomplished by combining the SUM and IF functions.  

You’ll notice with this calculation that we have the calculation SUM(C4:C9) repeated three times in the formula.

If we use LET, we can assign the name ‘q1s’ to the SUM calculation and then use the name in the rest of the formula.  

This is a much more efficient way of working.  

Both formulas produce the same result. The sum of the sales equals $362,000.  

As this is greater than $300,000 the result is 10% of $362,000, $36,200. 

Once a name has been defined for a variable, it can be reused in other places within the workbook. 

EXAMPLE 3 

In this example, I am using the FILTER function to filter the table’s data on the left and return only results that match my FILTER criteria. For example, I only want to see the results for ‘West’ and ‘Maths’. 

In the LET formula, I have named three variables and assigned values to them: 

tab –  cell range A2:C12 (the entire table) 

bloc – cell range A2: A12 (the block column) 

sub – cell range B2:B12 (the subject column) 

Once the variables have been defined, I have then completed the rest of the FILTER formula using the named variables instead of cell references.  

Whilst the LET formula is longer, it’s much easier to read and see which ranges relate to which named variables.  

Both formulas produce the same result.  

Once you have defined variables in a workbook, you can continue to reuse them in other calculations. The names will also appear in IntelliSense, much like a defined table or range name.  

LET is such a versatile formula, and we’ve only touched on a few examples here. If you would like to learn more, please check out the following links: 

Andrew Moss – Introducing the new LET function in Excel 

ExcelExciting – The Excel LET function 

Take a look at other free Excel tutorials from Simon Sez IT.

Deborah Ashby

Deborah Ashby is a TAP Accredited IT Trainer, specializing in the design, delivery, and facilitation of Microsoft courses both online and in the classroom. She has over 11 years of IT Training Experience and 24 years in the IT Industry. To date, she's trained over 10,000 people in the UK and overseas at companies such as HMRC, the Metropolitan Police, Parliament, SKY, Microsoft, Kew Gardens, Norton Rose Fulbright LLP. She's a qualified MOS Master for 2010, 2013, and 2016 editions of Microsoft Office and is COLF and TAP Accredited and a member of The British Learning Institute.

Most Popular Posts

  • Kanban vs Scrum: Project Management Methodologies and Their Differences [2022]
  • How to Insert a Checkbox in Excel? 3 EASY Examples
  • How to Autofit Excel Cells? 3 Best Methods
  • XLOOKUP Google Sheets – 4 Best Alternatives!
  • Dashboards in Excel Using Pivot Tables, Pivot Charts and Slicers
  • Free Microsoft Project Training Course
  • Free Microsoft Access Tutorial for Beginners (3.5 Hours Video)
  • How to Use Blending Mode With Layers in Adobe Photoshop Elements 15

Similar Posts

How to Calculate Correlation Coefficient in Excel? 2 Useful Ways

Using the Windows 8 Control Panel and PC Settings

The Most Used Microsoft Teams Shortcuts – Download

10 Microsoft Outlook Productivity Hacks

10 Steps to Know about Task Management on Monday.com

How to Superscript in PowerPoint? (And Subscript) 3 Easy Ways

Course Categories

  • Adobe
  • Data Analysis
  • QuickBooks
  • Microsoft
  • Web Development
  • Work Productivity

About Us

  • About Us
  • Free Resources
  • Affiliates
  • Become an Instructor

Products

  • Pricing and Plans
  • Business Pricing
  • Government Discounts
  • Non-Profit Discounts

Support

  • FAQ’s
  • Contact Us
  • DVD support

Connect

YoutubeFacebookLinkedIn
© 2023 Simon Sez IT, Inc.
  • Terms
  • Privacy Policy
  • Sitemap
888.817.6665 Monday thru Friday 7:30 a.m. - 5:00 p.m. (ET)