When my Excel formulas spiral out of control, LET is my fix

When my Excel formulas spiral out of control, LET is my fix

Published Nov 16, 2026, 1:30 PM EST Tony Phillips is a senior author at How-To Geek with over a decade of experience working with documents, data, and digital productivity tools. Holding a dual-honors degree in Linguistics and Hispanic Studies, he combines technical expertise with a passion for making complex topics easy to understand. Before joining How-To Geek in 2024, Tony worked as a document producer, data manager, legal document specialist, and content creator. He has also proofread and edited more than 3 million words of academic and professional writing. While Tony specializes in Microsoft 365, particularly Excel, his interests extend far beyond Office. He enjoys testing software, exploring open-source projects, discovering useful companion apps, and finding ways to customize and optimize Windows. Whether he's building spreadsheets, evaluating new tools, or uncovering hidden features, Tony enjoys helping readers get more from their technology. Outside of tech, he enjoys spending time with his dog, watching football, astrophotography, gardening, and golf. Sign in to your How-To Geek account I've never deliberately written a horrible Excel formula. They usually start as perfectly reasonable calculations, but as a spreadsheet evolves, so do its requirements. A lookup gets added, then a discount, then an exception for missing data. Before long, I'm staring at a formula that still works but takes considerably longer to understand than it did to write. Rather than keep adding to the mess, I use Excel's LET function to give individual calculations meaningful names and make the whole thing easier to manage. Here's how formulas spiral out of control A simple calculation doesn't stay simple for long Imagine I'm building a spreadsheet to calculate what customers pay for products in a small online store. I have an Excel table containing product prices and discount rates, and another one containing customer orders. Initially, all I need is an XLOOKUP formula that retrieves the correct product price: =XLOOKUP([@[Product ID]],tblProducts[Product ID],tblProducts[Standard Price]) That's easy enough to understand. I enter a product ID, and Excel returns the corresponding price. But then the requirements change. Instead of displaying only the price, I now want to calculate what the customer will actually pay after the product's standard discount has been applied: =XLOOKUP([@[Product ID]],tblProducts[Product ID],tblProducts[Standard Price]) * [@Quantity] *(1 - XLOOKUP([@[Product ID]],tblProducts[Product ID],tblProducts[Discount Rate])) Still manageable, but the formula is already doing more work than before. When writing longer formulas, press Alt+Enter to start a new line within the formula bar. It doesn't change how the formula works, but it can make complex formulas much easier to read and troubleshoot. Next, premium customers receive an additional five-percentage-point discount. The formula now has to decide whether the customer qualifies for that extra discount. In this example, I'm adding the discounts together. So if a product has a 10% standard discount, a premium customer receives a total discount of 15%: =XLOOKUP([@[Product ID]],tblProducts[Product ID],tblProducts[Standard Price]) * [@Quantity] *(1 - (XLOOKUP([@[Product ID]],tblProducts[Product ID],tblProducts[Discount Rate]) + IF([@[Customer Tier]]="Premium", 0.05, 0))) At this point, the formula still isn't unreasonable, but it definitely requires more concentration to understand. Finally, I decide I don't want missing product IDs to return an error either. If someone enters a product ID that doesn't exist in my lookup table, like P999 in this example, I'd rather leave the result blank: =IF(XLOOKUP([@[Product ID]], tblProducts[Product ID], tblProducts[Standard Price], "")="","",XLOOKUP([@[Product ID]],tblProducts[Product ID],tblProducts[Standard Price]) * [@Quantity] *(1 - (XLOOKUP([@[Product ID]], tblProducts[Product ID], tblProducts[Discount Rate]) +IF([@[Customer Tier]]="Premium", 0.05, 0)))) Every addition makes sense individually. The problem is that the formula has gradually become a collection of lookups, conditions, and calculations that are increasingly difficult to follow. I could keep extending it, but I'd rather take a step back and make the existing logic easier to understand. The warning signs that tell me a formula needs LET When understanding a formula becomes a chore Looking at that final formula, I can already spot a couple of warning signs that tell me it's time to rethink how I've put it together. The first is repetition. If I find myself writing the same lookup or calculation several times within a single formula, I start wondering whether there's a better way to structure it. Every time I repeat a calculation, I create another place I might need to update later. Another warning sign is when functions start getting layered on top of one another. In my example, lookups, discount calculations, customer-tier logic, and error handling are all combined into a single formula. The calculation still works, but understanding how it works takes much more effort. If I have to mentally decode the formula every time I revisit it, I've probably let things get too complicated. I don't reach for LET every time I write a formula with more than a couple of functions. Sometimes complicated calculations are unavoidable. But when repetition and nested logic start making a formula hard to maintain, LET is usually the first tool I reach for. LET is available in Excel for Microsoft 365, Excel for the web, and Excel 2021 or later. I replace repetition with named variables Give each part of the formula a name Rather than repeatedly writing out the same calculations, I can use LET to assign each one a name and then refer to that name elsewhere in the formula. LET takes a name, assigns a calculation or value to it, and then moves on to the next name-value pair. Its final argument is the value Excel should return. For example: =LET(Price,100,Discount,0.1,Price*(1-Discount)) Going back to my product-pricing example, I can replace the repeated lookups with named variables: =LET(Price, XLOOKUP([@[Product ID]], tblProducts[Product ID], tblProducts[Standard Price], ""),DiscountRate, XLOOKUP([@[Product ID]], tblProducts[Product ID], tblProducts[Discount Rate], 0),TierDiscount, IF([@[Customer Tier]]="Premium", 0.05, 0),TotalDiscount, DiscountRate + TierDiscount,IF(Price="", "", Price * [@Quantity] * (1 - TotalDiscount))) Sometimes, LET formulas can actually be longer than the messy formulas they replace. But that's fine. The goal is to make the calculation easier to understand and maintain, not necessarily shorter. By replacing repeated calculations with named variables, I can see what the formula is doing without having to decode every part of it. For me, that's a worthwhile trade-off. I'd rather have a formula that's a little longer but makes its logic clear than one that's shorter and harder to maintain. I build larger formulas in logical stages Let each calculation build on the previous one The LET formula in the previous section focuses on eliminating repetition. This version goes a step further by breaking the calculation into logical stages. Reading the variable names tells me how the result is produced—calculate a subtotal, work out the total discount, and combine the two to calculate the final price—so I don't have to mentally decode one large expression just to understand the process: =LET(Price, XLOOKUP([@[Product ID]], tblProducts[Product ID], tblProducts[Standard Price], ""),Quantity, [@Quantity],DiscountRate, XLOOKUP([@[Product ID]], tblProducts[Product ID], tblProducts[Discount Rate], 0),TierDiscount, IF([@[Customer Tier]]="Premium", 0.05, 0),Subtotal, Price * Quantity,TotalDiscount, DiscountRate + TierDiscount,FinalPrice, Subtotal * (1 - TotalDiscount),IF(Price="", "", FinalPrice)) I don't use LET to replace helper columns entirely. If intermediate results need to be visible or reused elsewhere, helper columns often make more sense. But when calculations only support a single result, LET helps keep the worksheet uncluttered. LET is my formula maintenance tool I no longer think of LET as an advanced Excel function I use to show off complicated formulas. For me, it's a practical way to make calculations easier to understand and maintain. A good LET formula won't necessarily be shorter than the alternative. But if I can revisit it a year from now, understand how it works, and confidently make changes without decoding the whole thing again, that's a trade-off I'm happy to make.

Original Source

Read the full article at Howtogeek →

KhanList aggregates and links to publicly available news content. We do not host full articles from third-party sources. Always verify important information with original sources.