Excel has thousands of functions, but most people stick to the basics, like SUM and AVERAGE. While those work fine for simple tasks, there are three functions that handle more complex scenarios with much less effort. SEQUENCE, LET, and LAMBDA aren’t commonly used, but they solve specific problems that would otherwise require awkward workarounds or lengthy formulas that become difficult to maintain.
When using them, you can build dynamic, self-contained solutions that update automatically instead of creating multiple helper columns or copying formulas across dozens of cells. Whether you’re generating sequential data, managing complex calculations, or creating reusable custom functions, these are among the Excel functions that can save you tons of work.
4
The SEQUENCE function generates data automatically
Create dynamic number and date sequences
SEQUENCE creates arrays of sequential numbers without manually typing each value. If you need a list of employee IDs, invoice numbers, or date ranges, this function handles it smoothly.
The syntax is straightforward:
=SEQUENCE(rows, [columns], [start], [step])
Let’s break down the parameters:
- rows: Determines how many numbers you want vertically.
- columns: Controls the horizontal spread—leave it blank for a single column.
- start: Sets your beginning number, defaulting to 1.
- step: Determines the increment between numbers, also defaulting to 1.
Considering a sales dataset, SEQUENCE proves useful for creating reference numbers. For example, the following formula generates numbers 1 through 32.
=SEQUENCE(32)
Similarly, if you need to start from 1001, you can use:
=SEQUENCE(32, 1, 1001)
It comes in handy with date sequences. The following formula will create twelve consecutive dates starting January 1st. This beats manually entering dates for monthly reports or project timelines.
=SEQUENCE(12, 1, DATE(2025, 1, 1), 1)
You can also generate working days only by combining SEQUENCE with other DATE functions in Excel, like WORKDAY, for more sophisticated scheduling scenarios.
Large SEQUENCE arrays can slow down your spreadsheet. Avoid generating more than 10,000 values at once unless absolutely necessary. If you need massive datasets, consider breaking them into smaller chunks or using external data sources.
3
LET makes complex formulas maintainable
Eliminate repetitive calculations and improve readability
LET assigns names to values within a formula. Hence, it eliminates repetitive calculations and makes the work readable. Instead of writing the same expression multiple times, you define it once and reference it by name.
The syntax follows this pattern:
=LET(name1, value1, [name2, value2, ...], calculation)
You can define multiple variables by adding more name-value pairs. The calculation at the end uses these named variables to produce the result.
Looking at the sales dataset, suppose you’re calculating a salesperson’s commission with bonuses. Without LET, you’d write:
=IF(G2*0.05>500, G2*0.05*1.1, G2*0.05)
The commission calculation B2*0.05 appears twice. With LET, it becomes cleaner:
=LET(commission, G2*0.05, IF(commission>500, commission*1.1, commission))
It does the same calculation but defines “commission” once at the beginning. You only need to change the commission rate in one place.
For complex profit margin analysis, LET proves even more useful. The following example defines each component clearly.
=LET(revenue, G2, costs, L2, margin, (revenue-costs)/revenue, IF(margin>0.3, "High", IF(margin>0.15, "Medium", "Low")))
This formula calculates profit margin as a percentage, then categorizes it as High (above 30%), Medium (15-30%), or Low (below 15%). Each component has a clear name, making the logic easy to follow.
This approach cuts formula complexity in half while making your spreadsheets easier to debug and modify later.
2
LAMBDA creates reusable custom functions
Build custom functions for repetitive business logic
LAMBDA builds custom functions you can use repeatedly across your workbook. Instead of copying formulas everywhere, you create a single function that accepts inputs and returns calculated results.
The syntax is:
=LAMBDA(parameter1, [parameter2, ...], calculation)
Parameters act like placeholders—when you call the function, you pass actual values that replace these placeholders. The calculation uses these parameters to produce the output.
Suppose you frequently calculate weighted performance scores. You can create a LAMBDA function such as the following:
=LAMBDA(sales, quota, weight, (sales/quota)*weight)
It creates a reusable function that takes three inputs: actual sales, sales quota, and a weighting factor. It returns a weighted performance score by dividing sales by quota and multiplying by the weight. Name this function “PerformanceScore” using Excel’s Name Manager.
To name your LAMBDA function, go to Formulas > Name Manager > New.
Now you can call this function anywhere in your workbook.
=PerformanceScore(B2, C2, 0.7)
This function calculates the performance score using the provided sales amount, quota, and weight factor.
For territory analysis, you can build a function that categorizes regions based on revenue:
=LAMBDA(revenue, IF(revenue>100000, "High", IF(revenue>50000, "Medium", "Low")))
This function categorizes revenue into three tiers: High for amounts over $100,000, Medium for $50,000 to $100,000, and Low for anything below $50,000. You can name it “Revenue” and use it throughout your sheets as:
=Revenue(J2)
The LAMBDA function works with other functions too, and it lets you write formulas like a human by using descriptive names instead of cryptic cell references.
You can keep your LAMBDA functions organized in Name Manager by using prefixes like “fn_” for all custom functions (e.g., “fn_PerformanceScore”). It makes them easier to find and prevents conflicts with regular named ranges.
1
I combine these functions to create powerful solutions
Build comprehensive business analysis tools
When you use SEQUENCE, LET, and LAMBDA together, they solve problems that would otherwise require multiple helper columns or array formulas. The combination creates dynamic, maintainable solutions.
Let’s consider building a sales forecasting tool using the sales data. The following formula calculates a 12-month sales forecast for a single starting sales amount. It starts by defining two key variables with LET. It takes the value from cell G2 as the starting base sale point.
=LET(base_sale, G2, growth_rate, L2, ProjectMonthly, LAMBDA(month, base_sale * (1 + growth_rate)^month), ProjectMonthly(SEQUENCE(12)))
Then it takes a monthly growth rate from L2 of 0.04 (4%). You can change this value to model different scenarios. It then defines a reusable mini-function called ProjectMonthly. This function calculates projected sales for a given month based on the base sale and growth rate.
Moreover, it calls the ProjectMonthly function and passes SEQUENCE(12) into it. This generates an array of numbers from 1 to 12, and the LAMBDA automatically applies its calculation to each number in that sequence.
Here’s a practical bonus calculator that calculates bonuses based on target achievement.
=LAMBDA(sales, target, LET(ratio, sales/target, IF(ratio>=1.2, sales*0.08, IF(ratio>=1, sales*0.05, 0))))
Start small, then build complexity
These functions work best when combined thoughtfully. Start with simple applications—use SEQUENCE for generating test data, LET for cleaning up repetitive calculations, and LAMBDA for business rules you use repeatedly. Once you’re comfortable with each function individually, you’ll find natural opportunities to combine them into more sophisticated solutions.
The learning curve isn’t steep, but the payoff is significant. Your spreadsheets become more reliable, easier to audit, and simpler to modify when business requirements change. That’s what makes these three functions particularly valuable for anyone who works with data regularly.
