Cat On A Spreadsheet

One of the less attractive aspects of sophisticated Excel reporting is that useful formulas tend to become repetitive. We develop a calculation that works well, use it in several places, and eventually discover that the same logic exists in half a dozen different worksheets. When the business requirement changes, we have to find every copy, make the same adjustment and hope that none has been overlooked. Dynamic arrays make this problem particularly noticeable. A single formula might filter thousands of transactions, apply optional criteria, enrich the results with reference data and construct a report. That is powerful, but copying the entire formula every time we need a variation is not a particularly sustainable approach.

In the previous post, we built a reporting formula with several optional controls. The user could select a date range, department, region, customer search text and minimum transaction value. The formula handled all those conditions and returned the matching records. It worked, but what happens when we need the same filtering logic in a customer summary, a monthly trend and a transaction-level report? We could repeat it three times. Alternatively, we could turn the filtering logic into a function of its own. That is where LAMBDA becomes interesting.

From a formula to a function

Excel has supported user-defined functions through VBA for decades. If we wanted to create a function that performed a calculation Excel did not already provide, we could write a VBA function, give it a name and call it from a worksheet. LAMBDA brings a similar capability directly into Excel's formula engine. It allows us to define a calculation with parameters and return a result, without writing VBA or creating a macro-enabled workbook.

Consider a simple calculation that increases an amount by a specified percentage. Normally, we might write =A2*(1+B2). With LAMBDA, we can express the same operation as =LAMBDA(Amount,Rate,Amount*(1+Rate))(100,0.15). The result is 115. The first part defines the parameters and the calculation. The second part, (100,0.15), supplies the arguments.

This is useful for testing, but the real advantage comes when we give the function a permanent name. In Excel's Name Manager, accessible through the Formulas tab, we can create a workbook-level name such as IncreaseBy and assign the definition =LAMBDA(Amount,Rate,Amount*(1+Rate)) to its Refers to field. Once defined, the function can be called from a worksheet with =IncreaseBy(A2,B2).

We have effectively created a new Excel function. Unlike a VBA user-defined function, this function is defined entirely through Excel formulas. It requires no VBA project and no macro permissions, and its parameters can accept arrays as naturally as individual values.

That final characteristic is particularly relevant to the reporting techniques we have been developing. If A2:A100 contains amounts, =IncreaseBy(A2:A100,0.15) returns an array of increased values. There is no need to call the function separately for every row. The same function therefore works with a single value or a collection of values.

It is worth understanding the relationship between LET and LAMBDA here. We have already been using LET to assign names to intermediate calculations, making complex formulas easier to understand. Those names exist only inside the formula in which they are defined. LAMBDA allows us to take the next step. Rather than naming an intermediate calculation, we define a reusable calculation that accepts its own inputs. LET helps us organise the inside of a formula. LAMBDA allows us to reuse that logic elsewhere. The two functions are complementary, and they become particularly effective when used together.

Turning reporting criteria into a reusable function

Let's return to the transaction reporting example from the previous post. We have a table called tblTransactions, containing Date, CustomerID, CustomerName, Department, Region, Status and Value. Our users can control the reporting population through six inputs: a start date, an end date, a department, a region, customer search text and a minimum transaction value. Some controls are optional. A blank date means no restriction in that direction. A blank department or "All" means every department. Blank customer search text means no restriction on customer name, and a blank minimum value means transactions of any value are permitted.

Previously, all that logic sat directly inside a FILTER formula. We could simply wrap the entire formula in a LAMBDA and call the resulting function SelectTransactions. That would work, but there is a potentially better design. Instead of making the reusable function return the filtered transactions, we can make it return the inclusion array that determines which transactions qualify. This is a subtle distinction, but it makes the function more flexible.

Recall that the second argument of FILTER is an array that identifies which records should be included. If our transaction table contains 10,000 rows, the inclusion array also contains 10,000 values. Each value indicates whether the corresponding row satisfies the reporting criteria. That array is useful beyond FILTER. We can use it to count matching transactions, calculate matching totals, identify whether any qualifying records exist, or provide a consistent reporting population for several different outputs.

Let's create a reusable function called TransactionMask. In Name Manager, we define it as follows:

The formula is not particularly short, but it has an important quality: it expresses one coherent reporting rule. Each named calculation determines whether a transaction satisfies one criterion. When the corresponding control is inactive, IF returns 1, meaning that the criterion does not restrict the result. The double unary operator, --, converts the logical results of the active conditions into numeric zeros and ones. Multiplying all six results produces the final inclusion array.

A transaction receives 1 only when it satisfies every active criterion. Otherwise, it receives 0.

Notice that TransactionMask does not contain references to H2, H3 or any other specific control cells. Those references are provided when the function is called. That separation is important. The function defines how reporting criteria are interpreted. The worksheet determines where those criteria come from.

Once the function has been saved in Name Manager, our original reporting formula becomes remarkably small:

=FILTER(tblTransactions,TransactionMask(H2,H3,H4,H5,H6,H7),"No matching transactions")

What previously required several lines of filtering logic is now expressed through a single named function. More importantly, the same function can be called from other worksheets using different control cells, without duplicating the underlying rules.  If management decides that the customer search should become case-sensitive, we can change SEARCH to FIND in the function definition. Every report using TransactionMask then inherits that change. That is the real advantage of this approach. We have centralised a piece of business logic.

Reusing the same population in different reports

Now suppose the workbook contains a transaction report and a customer summary. Both should respond to the same reporting controls. The detailed report is straightforward. We use FILTER with TransactionMask and return the original transaction rows. The customer summary is more interesting because it needs to change the grain of the data.

We still begin with exactly the same population, but after selecting the qualifying transactions we identify the distinct customers and calculate the required measures.

This introduces another use of LAMBDA, although we do not need to create any additional named functions. MAP applies a calculation to each value in an array. In this example, each distinct customer ID is passed into a LAMBDA that calculates the customer's transaction count or total value. Those LAMBDA expressions are local to their respective MAP operations. They do not need to exist in Name Manager because we are not intending to call them independently.

This illustrates an important distinction between two ways of using LAMBDA. Sometimes we give a LAMBDA function a permanent name because its logic needs to be reused across the workbook. At other times, we define it directly inside another function because it performs a temporary operation within a larger calculation. Both are useful.

Our example also deliberately checks SUM(Include) before attempting to construct the customer summary. When no transactions satisfy the controls, it returns a message rather than passing an empty dataset into later array operations. The transaction report and customer summary may produce entirely different outputs, but they now share the same definition of a qualifying transaction. That consistency matters more than the reduction in formula length. Consider what happens when a cancelled-transaction rule is introduced, or when the department filter must be interpreted differently. If the filtering logic is copied into five separate reports, there are five opportunities for inconsistency. If the rule belongs to a shared function, there is one place to maintain it.

We can even use the inclusion array directly for simpler measures. SUM(TransactionMask(H2,H3,H4,H5,H6,H7)) returns the number of matching rows, while SUMPRODUCT(TransactionMask(H2,H3,H4,H5,H6,H7),tblTransactions[Value]) calculates their total value. These calculations do not need to generate a filtered transaction array at all. One named function can therefore support a detailed report, an aggregated report and individual management KPIs.

Deciding what belongs inside a LAMBDA

Once we discover that LAMBDA can turn almost any calculation into a reusable function, it becomes tempting to create functions for everything. That is not necessarily a good idea. A function is most valuable when it represents a recognisable operation or business rule that is likely to be reused. Optional transaction filtering is a reasonable candidate because several reports may need exactly the same interpretation of the controls. A calculation that appears only once, is easily understood and is unlikely to change independently may be better left inside LET.

There is also a balance between flexibility and complexity. Our TransactionMask function contains a direct reference to tblTransactions. That means it is specifically designed for this transaction table. It is not a general-purpose filtering function that can be applied to any dataset. For the workbook we are building, that is perfectly reasonable.

We could make the function more generic by passing the date, department, region, customer and value arrays as parameters. The function would no longer depend on one particular Excel Table. But it would then require substantially more arguments every time we called it. A formula that was previously easy to read might become cumbersome.

This is a familiar design trade-off in programming as well as spreadsheet development. A highly general function can be reused in more situations, but it may become harder to understand and less convenient for the task it was originally intended to solve. There is little value in generalising a calculation beyond the requirements it actually needs to satisfy. I would generally begin with a function that serves a clear purpose within one workbook. If the same logic subsequently proves useful in other reporting systems, that is the point at which making it more generic becomes worth considering.

The function's name is part of that design. TransactionMask communicates that the function returns an inclusion array, not a filtered transaction table or a calculated total. Calling it ReportFunction1 would convey almost nothing. Calling it GetTransactions would be misleading because it does not return transaction records.

Names should describe the result or responsibility of the function rather than the Excel technique used to implement it. The same applies to parameter names. StartDate, SelectedDepartment and MinValue are much more useful than a, b and c when somebody is trying to understand a calculation several months after it was written.

Testing and maintaining custom functions

Named LAMBDA functions are convenient to call, but there is a practical disadvantage compared with ordinary worksheet formulas. Their definitions live in Name Manager rather than visibly in worksheet cells. That makes them easy to reuse, but also easier to forget about.

For any function that contains important business logic, it is worth maintaining a short description of what it does, what each parameter means and what it returns. Name Manager includes a comment field that can help with this. For TransactionMask, we might explain that blank criteria remove the corresponding restriction, that "All" is accepted for department and region, and that the result is a numeric inclusion array aligned with tblTransactions.

It is also worth testing the function independently before relying on it in several reports. During development, we can call

=TransactionMask(H2,H3,H4,H5,H6,H7)

directly in a spare area of the worksheet. Excel will spill the resulting inclusion array, allowing us to see exactly which transaction rows qualify. That is especially useful when a report appears to be missing records. If the mask is correct but the report is wrong, the problem lies in the subsequent transformation. If the mask itself is wrong, we know to investigate the controls or the function's conditions.

The function should also be tested with deliberately awkward inputs. A date range with no matching transactions, blank controls, the "All" option, customer names containing unusual characters and a minimum value of zero are all worthwhile cases.

There is a particularly important distinction between a blank minimum value and a minimum value of zero. They may happen to produce the same result if all transaction values are positive, but they do not mean the same thing. A zero threshold deliberately excludes negative values, whereas a blank threshold imposes no restriction. Our function preserves that distinction by checking whether MinValue is blank rather than simply treating zero as an inactive criterion.

Invalid dates and non-numeric thresholds deserve separate validation as well. TransactionMask is responsible for calculating which records match; the reporting interface can validate the user's inputs before calling it. This keeps the function focused without pretending that every possible data-quality problem can be solved by the inclusion array itself.

Another practical limitation is that named LAMBDA functions belong to the workbook in which they are defined. They do not automatically become available to every Excel workbook on the computer. For functions that are needed across many unrelated workbooks, an add-in or another managed distribution approach may be more appropriate. For a self-contained reporting workbook, however, a named function is often exactly what we need. It keeps the logic together with the report, requires no macro-enabled file format and works through Excel's native calculation engine.

There are performance considerations too. Moving a calculation into LAMBDA does not automatically make it faster. A named function is not a cache. If three separate report formulas call TransactionMask, each call still needs to be evaluated as part of its own calculation. Likewise, creating dozens of small functions and calling them repeatedly over very large arrays can introduce unnecessary calculation overhead. The main benefit is reuse and maintainability. Any performance improvement has to come from the actual calculation design, not simply from giving the formula a name.

For large reporting solutions, the same architectural judgement we have discussed throughout this series still applies. Some transformations belong in worksheet formulas, others are better handled by Power Query or the Data Model. LAMBDA extends what formula-based reporting can do. It does not remove the need to choose the appropriate tool.

From individual formulas to a reporting library

The most interesting consequence of using LAMBDA is that it changes how we can organise reporting logic within a workbook. Until now, our formulas have generally been self-contained. Even when we used LET to make them readable, each formula still contained all the logic necessary to produce its result. With named LAMBDA functions, we can begin separating common operations from individual reports. One function might define valid transaction records.  Another could convert dates into the organisation's financial periods. Another could calculate a particular business measure according to agreed rules. The report formulas would then combine those operations rather than implement each one from scratch.

This begins to resemble a small function library. There is no need to create a large or elaborate framework. In fact, doing so prematurely would probably make the workbook harder to maintain. But where a rule is important, repeated and reasonably stable, giving it a dedicated function can be worthwhile.

Consider a measure such as net sales. One department might calculate net sales as completed transactions less refunds. Another report might accidentally calculate it as all transactions excluding cancellations. A third might include adjustments handled differently again. If the organisation has one agreed definition of net sales, that definition should ideally exist in one maintained calculation rather than being independently recreated throughout the workbook. A named LAMBDA offers one way to achieve that within a formula-based reporting solution.

There is a useful separation emerging here between business logic and presentation. The reusable function determines what something means or how it is calculated. The report determines which inputs to supply, how to combine the results and how to display them. That separation is valuable because business rules and presentation requirements change for different reasons. Management may want a different column order or another chart without changing the definition of a valid transaction. Equally, the organisation may update the transaction status rules without wanting to redesign every report that depends on them. A well-chosen LAMBDA function helps keep those changes independent.

This is also where the connection between Excel formulas and programming becomes more apparent. We are defining functions, passing parameters, reusing calculations, composing results and separating responsibilities. Those are familiar software-development ideas, but they are now available directly inside an ordinary Excel workbook.

The result does not have to become a software application in the traditional sense. It can remain a spreadsheet, with all the accessibility and flexibility that makes Excel useful in the first place. What changes is the quality of the underlying structure. Instead of treating every formula as an isolated solution, we can begin building a small collection of reliable operations that work together.

And there is another direction in which this idea becomes particularly powerful. So far, we have focused mainly on LAMBDA as a function that we create and call by name. But several modern Excel functions accept a LAMBDA directly as an argument. We briefly encountered this with MAP in our customer summary. Functions such as MAP, BYROW, BYCOL, REDUCE and SCAN allow us to describe an operation and then have Excel apply it across an array, a collection of rows or an accumulating sequence. These are not simply additional ways to write familiar formulas. They allow us to express calculations that can be awkward with traditional conditional aggregation functions, particularly when each record or group requires a more sophisticated calculation.

We have now seen how LAMBDA can make reporting logic reusable. The next step is to explore how it can make array transformations themselves more expressive, allowing us to perform custom calculations across whole datasets without helper columns or VBA loops.

Full Service Consulting

Reporting

Automation

Cat On A Spreadsheet