How to Use the LET Function for Intermediate Variables in Microsoft Excel

Complex formulas in Microsoft Excel frequently repeat identical calculation steps—such as multi-sheet XLOOKUP lookups, text extraction sub-strings, or mathematical normalisation routines—multiple times within a single cell. Repeating these sub-calculations clutters formula readability, increases maintenance errors, and degrades workbook performance because Excel recalculates identical sub-expressions redundantly. The LET function solves this by introducing local variables to spreadsheet calculations.

Understanding LET Function Syntax and Flow

The LET function assigns descriptive names to intermediate calculation values and passes them into a final calculation block:

=LET(name1, name_value1, [name2, name_value2], ..., calculation)
  • name1: A user-defined variable identifier (must begin with a letter and avoid cell coordinate syntax like A1 or R1C1).
  • name_value1: The value, range, or sub-formula assigned to that variable name.
  • calculation: The final computation that references all preceding variable names to produce the cell result.

Refactoring Redundant Formulas Using LET

Consider a standard calculation that evaluates a sales tax calculation and verifies whether the output exceeds an executive review threshold:

=IF(XLOOKUP(A2, Products[ID], Products[Price]) * 1.2 > 500, 
    XLOOKUP(A2, Products[ID], Products[Price]) * 1.2 * 0.9, 
    XLOOKUP(A2, Products[ID], Products[Price]) * 1.2)

In this unoptimised formula, the XLOOKUP expression is evaluated three separate times. Now refactor it with LET:

=LET(
    basePrice, XLOOKUP(A2, Products[ID], Products[Price]),
    grossTotal, basePrice * 1.2,
    IF(grossTotal > 500, grossTotal * 0.9, grossTotal)
)

By storing basePrice and grossTotal as intermediate variables, Excel calculates the lookup exactly once in memory, reducing calculation overhead and producing crystal-clear logic.

Best Practices for Variable Naming and Formatting

To maximize spreadsheet maintainability across enterprise teams:

  • Use CamelCase Names: Employ readable identifiers such as taxRate, filteredData, or targetRegion to make formulas self-documenting.
  • Alt + Enter Formatting: Use Alt + Enter inside Excel’s formula bar to place each variable name, value, and the final calculation on distinct lines for clean visual indentation.

Adopting the LET function elevates spreadsheet authoring to professional programming standards while dramatically boosting calculation speed.

Get the best tech tips delivered straight to your inbox.

Join thousands of readers mastering Apple, Google, Microsoft, and Linux.