Excel is powerful, but its formulas can get unwieldy fast. I found a way to simplify even the most bloated ones without losing functionality, and it’s been a total game-changer for my spreadsheets.

What Is the LET Function?

LET is a function in Excel that allows you to add variables to your formulas to make them more manageable and efficient.

Think of variables like containers with labels that store different values, such as numbers or text. They make the values easier to reuse and manipulate, especially if they’re the result of intermediate calculations. That’s because you can refer to them using the name of their respective container instead of directly.

An Excel spreadsheet on a tablet on an office desk with pens, a calculator, and some papers around.

Here is the syntax of the LET function:

In the syntax,variable_name_1is the name or label of the variable you want to use. For instance, if it’s going to store the weight of something, you may simply call itweight. Thevalue_to_assign_1parameter is the value you want to assign tovariable_name_1.

You can also create more variables and assign names to them, which is where the optional[variable_name2, assigned_value2, …]argument comes in. Andfinal_calculationis the formula that uses the named variables to produce a result.

Sample data for calculating the total cost of an item in Excel.

Here’s How I Write Complex Excel Formulas Without Breaking a Sweat

Complex Excel formulas don’t have to be intimidating.

LET is one of thoseExcel functions that can save you tons of workin the long run. However, it requires everyone looking at the Excel sheet to grasp the concept of variables, on top of how the function works. It’s worth learning, though, as we will see with the example in the next section.

How the LET Function Makes Formulas More Readable

The best use case of the LET function is to reduce the complexity of formulas with several intermediate calculations. Let’s look at the sample data below that shows the price, quantity, and discount rate of an item.

We are going touse basic math in Excelto figure out the total cost of the product after factoring in everything. Here’s the syntax of the calculation:

Calculating the total cost of an item using hard-coded figures in Excel.

Let’s break down what is happening here:

Here is what the formula would look like in Excel if we hard-coded the values:

There are a couple of problems here that make this formula complex. The most glaring one is that the calculation for thesubtotal(1000 * 3) is repeated, making the formula hard to read. It’s also bad for performance, since Excel is repeating the calculation instead of just reusing the result.

Calculating the total cost of an item using the LET function in Excel.

Also, imagine if we had repeated it several more times in the formula. That means if we changed something in that calculation, we’d also have to change it everywhere it’s repeated. That’s a tedious and error-prone process.

Also, it’s hard to tell what the numbers mean by themselves. For instance, when calculatingsubtotal, is the1000the quantity or the price of the item? There is nothing that makes it obvious what those numbers are.

Productivity

It also doesn’t become much easier to read if we use cell references, like in the example below:

When we use the LET function, the formula becomes more readable because we build it up slowly, removing the complexity with each step. Here is what it would look like (I will put everything in a separate line to make it easier to follow the example):

Here, the values inB2,B3, andB4become more descriptive withprice,quantity, anddiscount_rate, respectively. Now we just use those named variables to calculatesubtotalanddiscount_amount. As you can see, every value is assigned to a variable once, either by referencing a cell or storing the result of an intermediary calculation, and then reused throughout.

Furthermore, it’s easier for someone else to trace how you calculated the total cost. And if we change the values in the variables (e.g., use another cell reference or add another variable to the intermediary calculations), we don’t have to go around tweaking the formula in multiple places.

Rules to Keep in Mind When Naming Variables

Before you go about using the LET function with your formulas, it helps to learn some rules and best practices when naming your variables.

Keep the following in mind:

Functions like LET make your formulas human-readable, which is great for collaboration. On top of that, if you come back and look at your formulas months down the line, it’s much easier to remember what they do if you have descriptive names. It’s a very useful function that significantly cuts complexity by simplifying how you interact with formulas, even if they end up being more verbose.