Make long Excel formulas easier to audit with LET

A formula can return the right number and still be difficult to trust. When the same calculation appears three times, the next person has to check all three copies before changing anything.

LET gives those repeated pieces names inside the formula. You can read the calculation in steps and reuse a result without writing it again.

The task

You are reviewing department spending. You want each row to show the difference between actual and budget as a percentage of budget.

For this example, a positive result means spending is over budget. A negative result means spending is under budget.

Imagine these headings in A1:C1, with the first department in row 2.

Before writing the formula, decide what zero budget means. Here, we will display “No budget.” That keeps an undefined percentage separate from a genuine zero variance.

Start with the calculation

For Operations:

=(B2-C2)/C2

Actual spending exceeds budget by $2,500. Divide that by the $50,000 budget and the variance is 5%.

To handle a zero budget:

=IF(C2=0,"No budget",(B2-C2)/C2)

That is already a reasonable formula. LET becomes more useful as we add a reporting threshold and reuse the same percentage.

Give the inputs names

Here is the same calculation with LET:

=LET( actual,B2, budget,C2, IF(budget=0,"No budget",(actual-budget)/budget) )

Read it from top to bottom:

• actual means the value in B2.
• budget means the value in C2.
• The final expression decides what the formula returns.

Those names apply inside this formula. They do not create workbook-wide named ranges.

LET is available in Microsoft 365, Excel 2021, and Excel 2024. If a workbook must work in Excel 2019 or earlier, use a compatible formula or helper columns instead.

Where LET earns its place

Suppose your monthly report should flag any variance whose magnitude is at least 5%, in either direction.

A formula without LET might look like this:

=IF(C2=0,"No budget",IF(ABS((B2-C2)/C2)>=5%,"Review: "&TEXT((B2-C2)/C2,"0.0%"),"Within threshold: "&TEXT((B2-C2)/C2,"0.0%")))

The same percentage calculation appears three times.

Here is the LET version:

=LET( actual,B2, budget,C2, threshold,5%, IF( budget=0, "No budget", LET( variancePct,(actual-budget)/budget, status,IF(ABS(variancePct)>=threshold,"Review","Within threshold"), status&": "&TEXT(variancePct,"0.0%") ) ) )

The outer section names the inputs and handles zero budget.

For a nonzero budget, the inner section calculates variancePct, chooses a status, and builds the displayed result. Keeping the percentage calculation inside that branch avoids dividing by zero.

The formula has more lines, but each line has a job.

Try it

Choose a department and change the review threshold. Watch the named inputs and result update together.

At a 5% threshold:

The test uses >=, so a variance exactly at the threshold is flagged.

ABS checks the size of the variance without its sign. That is why both 5% over budget and 5% under budget trigger review.

A review flag is a request for explanation. It does not automatically mean the result is bad.

Keep numbers available for analysis

The example combines a label and percentage to make the logic visible. Its output is text.

For a working report, you will often want two columns:

• A numeric variance percentage for sorting, charting, and further calculations.
• A separate status for review.

Use the simpler LET formula in the percentage column and format numeric results as percentages. Keep “No budget” visible as an exception, and decide how that exception should appear in charts or summaries.

Do not turn every useful number into a sentence.

Choose names that explain the business logic

Good names reduce the need to decode cell references.

Use actual, budget, threshold, and variancePct rather than value1, value2, or temp.

Names must follow Excel’s naming rules. Avoid names that look like cell references. A descriptive name such as budgetAmount is easier to understand and less likely to conflict.

Keep each name close to the calculation that uses it. A formula with fifteen names can become just as difficult to follow as the one it replaced.

What LET does not fix

LET makes a calculation easier to inspect. You still need to verify the inputs and rules.

Check:

• Actual and budget cover the same department and period.
• The sign convention is stated.
• Zero budget has an explicit treatment.
• The threshold reflects the review policy.
• At least one result agrees with a manual calculation.

A clearly named wrong input still produces a wrong answer.

When to use it

Use LET when a calculation repeats, a formula is difficult to explain, or named steps would make a review easier.

Keep a short formula short when the names add little value. If colleagues need to inspect intermediate results directly on the sheet, helper columns may be easier.

The aim is a formula the next person can change with confidence.

Related guides