Spreadsheets

LET function in Excel: make long expense formulas readable

Use the LET function in Excel to name each range once and make a long SUMIFS savings formula readable. Needs Excel 2021 or Microsoft 365.

Editorial illustration for LET function in Excel: make long expense formulas readable

LET lets you give a name to a value or a range inside a formula, so you write each piece once and then use the name. For an expense sheet, that turns a long SUMIFS formula that repeats the same ranges three times into something you can read at a glance. LET needs Excel 2021 or Microsoft 365; older versions cannot calculate it.

What does a long expense formula look like without LET?

Say your transactions sit in a sheet like this: dates in B2:B500, amounts in C2:C500, the month start in E1, the month end in E2, and your monthly income in H1. You want a cell that says how much you saved, or how much you overspent. The old way repeats the SUMIFS twice or three times:

=IF($H$1-SUMIFS($C$2:$C$500,$B$2:$B$500,">="&$E$1,$B$2:$B$500,"<="&$E$2)<0,"Over by "&TEXT(SUMIFS($C$2:$C$500,$B$2:$B$500,">="&$E$1,$B$2:$B$500,"<="&$E$2)-$H$1,"#,##0"),"Saved "&TEXT($H$1-SUMIFS($C$2:$C$500,$B$2:$B$500,">="&$E$1,$B$2:$B$500,"<="&$E$2),"#,##0"))

It works, but if you change a range you must change it in three places, and one missed edit gives a wrong answer that looks right.

How does LET make it shorter?

The syntax is LET(name1, value1, name2, value2, ..., calculation). Each name is followed by what it stands for, and the last argument is the formula that uses them. Rewritten:

=LET(amt,$C$2:$C$500,dates,$B$2:$B$500,spent,SUMIFS(amt,dates,">="&$E$1,dates,"<="&$E$2),remaining,$H$1-spent,IF(remaining<0,"Over by "&TEXT(-remaining,"#,##0"),"Saved "&TEXT(remaining,"#,##0")))

Read it from left to right: amt is the amounts, dates is the dates, spent is the total in the window, remaining is income minus spent, and the last part chooses the message. The SUMIFS is calculated once, and each range is written once.

Does the result check out?

Use made-up numbers. If your income in H1 is ₹60,000 and the month's spending adds up to ₹52,400, then remaining is 60,000 minus 52,400, which is 7,600, and the cell shows Saved 7,600. If the spending is ₹63,150, remaining is 60,000 minus 63,150, which is minus 3,150, and the cell shows Over by 3,150.

Income in H1Spent in the windowremainingCell shows
₹60,000₹52,4007,600Saved 7,600
₹60,000₹63,150-3,150Over by 3,150
₹60,000₹60,0000Saved 0

What rules apply to names in LET?

  • A name must start with a letter, and cannot look like a cell address such as A1 or R1C1. That is why the example uses amt and dates and not names like x1.
  • Names cannot contain spaces. Use a short word, or join words with an underscore.
  • A later name can use an earlier one, as remaining uses spent. A name cannot use one that comes after it.
  • The last argument is always the calculation, never a name and value pair.

Which Excel versions can use LET?

LET works in Excel 2021 and in Microsoft 365, including Excel for the web. It does not work in Excel 2019, 2016 or earlier. If someone opens your workbook in one of those, the cell typically shows #NAME? and the formula may appear with a prefix such as _xlfn.LET. This matters if you send the file to a CA or a relative who uses an older Excel. Google Sheets also has a LET function with the same pattern of names and values.

Is LET always worth using?

No. For a short formula such as =SUM(C2:C500), LET adds nothing. It pays off when the same range or the same sub-calculation appears more than once, or when a formula has grown so long that you dread editing it. A good test is whether you could explain the formula aloud in one breath. If not, name its parts.

A small checklist for rewriting a formula

  1. Copy the old formula into a spare cell so you can compare answers.
  2. Underline every range or calculation that appears more than once.
  3. Give each a short name and write it once as a name and value pair.
  4. Replace the repeats with the names and put the final calculation last.
  5. Test with two or three cases, including an empty month and an overspent month, and make sure both versions agree.

When a workbook has several of these formulas across tabs and the monthly import and categorising starts to take more time than the thinking, an expense tracker that imports statements can take over the repetitive part. Until then, a well-named formula is a perfectly good tool.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports