Cat On A Spreadsheet

A report becomes considerably more useful when the person using it can control what it shows. Selecting a department or reporting period is straightforward enough, but real reporting workbooks rarely stop there. A user might want to select a department, restrict the report to one region, search for part of a customer name, set a minimum transaction value and choose a date range.

The complication is that they may not want to use all of those criteria every time. One day the report might need every department for a single region. The next day it might need one department across every region. On another occasion, the user might care only about transactions above €5,000 and want everything else left unrestricted.

That creates a different kind of filtering problem. The question is no longer simply whether a record meets several criteria. It is whether a record meets each criterion when that criterion is active. Dynamic arrays give us a particularly clean way to handle this because the inclusion argument of FILTER is itself an array. Rather than constructing several different report formulas for different combinations of selections, we can construct one inclusion array whose behaviour changes according to the controls the user has populated.

Suppose we have a table called tblTransactions containing Date, CustomerID, CustomerName, Department, Region, Status and Value. Our report has several controls. H2 contains a start date and H3 an end date. H4 contains a department, with "All" meaning that every department should be included. H5 does the same for region. H6 contains optional search text for the customer name, while H7 contains an optional minimum transaction value. We could start with the date range because that criterion is always active:

This is the familiar AND pattern. Each logical test creates an array of TRUE and FALSE values. Multiplying them produces an inclusion array in which only records satisfying both conditions evaluate to 1.

The department criterion is slightly different. If H4 contains "Sales", we want the familiar test tblTransactions[Department]=H4. If H4 contains "All", however, every row should pass that criterion. One way to express that logic is:

(tblTransactions[Department]=H4)+(H4="All")

If the selected department is "Sales", the second expression is false, so only Sales records pass. If H4 contains "All", the second expression is true for the entire calculation and every row passes the department criterion. Conceptually, we are saying that a row is valid if its department matches the selection OR the user has selected all departments. This use of addition as OR complements the multiplication-as-AND technique we have used throughout the series.

We can combine it with the date conditions:

There is a small detail worth understanding here. When H4="All", the department expression can produce values greater than 1 for any row whose actual department also happens to be "All". That does not matter to FILTER; any non-zero value is treated as included. If we want the logic to be more explicit, we could test whether the combined expression is greater than zero, but for this kind of inclusion array it is generally unnecessary.

Region follows exactly the same pattern. We do not need a different filtering technique simply because another optional control has been added. The inclusion logic becomes:

This is where the technique begins to scale nicely. We are not asking which of four different report formulas should run. We are constructing one definition of a valid row. A record must fall inside the date range, and it must either belong to the selected department or the department control must be unrestricted, and it must either belong to the selected region or the region control must be unrestricted.

The next requirement is slightly different because the user wants to search for part of a customer name. If H6 contains "Smith", we might want "Smith & Co", "John Smith Limited" and "Smithson Trading" all to match. An equality test cannot do that. SEARCH, however, can.

For a single value, SEARCH("Smith","John Smith Limited") returns the position at which the text begins. Applied to a table column, it returns an array of positions and #VALUE! errors. ISNUMBER turns that into the logical array we need:

ISNUMBER(SEARCH(H6,tblTransactions[CustomerName]))

The problem is what should happen when H6 is blank. Interestingly, SEARCH("",text) returns a match. We could therefore exploit that behaviour and allow a blank search cell to match every customer. That works, but I would be cautious about relying on a side effect that may not be obvious to somebody maintaining the workbook later. It is often clearer to make the optional nature of the criterion explicit:

ISNUMBER(SEARCH(H6,tblTransactions[CustomerName]))+(H6="")

Now the logic reads in the same way as our department and region controls: the customer passes if the search text is found or no search text has been supplied.

There is another useful characteristic of SEARCH: it is not case-sensitive. A search for "smith" will find "Smith". If case-sensitive matching is required, FIND can be used instead.

We can now add the customer search to our report:

At this point, the formula is still manageable, but the inclusion argument is beginning to become the most complicated part of the calculation. That is an excellent reason to introduce LET.

Rather than placing every condition directly inside FILTER, we can name the conditions according to what they mean.

This version is longer vertically, but much easier to understand. The final FILTER almost reads like a sentence. Include records that are after the start date, before the end date, match the department, match the region and match the customer search.

More importantly, if the result is wrong, each criterion can be inspected independently. Temporarily returning DepartmentMatch shows exactly which rows are passing that test. Returning CustomerMatch shows whether the text search is behaving as expected. This is particularly useful when optional controls are involved because a filter can otherwise appear to fail for reasons that are difficult to see. A single unnoticed space in a control cell, for example, can change which rows satisfy an equality test.

Now we can introduce the minimum-value criterion. If H7 contains 5000, we want transactions whose value is at least €5,000. If H7 is blank, the criterion should do nothing. The active version of the test is simply tblTransactions[Value]>=H7. To make it optional, we use the same pattern as before: the value must satisfy the threshold OR the control must be blank. We can add that as another named condition:

We now have a single report formula supporting several independent controls, some mandatory and some optional. There is no branching formula for “department only”, another for “department and region”, another for “region and customer”, and so on. The controls determine which criteria are active. That becomes increasingly valuable as their number grows. With four optional criteria there are already sixteen possible combinations of active and inactive controls. With six there are sixty-four. Trying to design separate calculation paths for those combinations would be absurd.

A well-designed inclusion array makes the combinations irrelevant. Each criterion answers only two questions: what constitutes a match when the control is active, and what should make every row pass when the control is inactive? Once those questions have been answered, the criteria can simply be multiplied together.

There is, however, an important distinction between optional criteria and multiple selections. Suppose the department control no longer contains one department or "All". Instead, the user wants to select several departments. A single equality test is no longer enough.

Imagine that J2:J4 contains the departments the user wants to include. We need to determine whether each transaction's department appears anywhere in that selection. That is a matching problem, so XMATCH becomes useful again:

ISNUMBER(XMATCH(tblTransactions[Department],J2:J4))

XMATCH attempts to find each transaction department within the selected-department list. Successful matches return positions; unsuccessful matches return #N/A. ISNUMBER converts those results into the inclusion array required by FILTER.

This is a good example of techniques from earlier posts reappearing in a different context. XMATCH was previously useful when comparing datasets. Here, the second dataset happens to be a user-maintained selection list. The principle is identical.

Optional date controls deserve similar care. So far, both H2 and H3 have been mandatory. If we want to allow either boundary to be blank, the same optional-condition pattern works. The start-date condition becomes “the transaction date is on or after H2, OR H2 is blank.” The end-date condition becomes “the transaction date is on or before H3, OR H3 is blank.” This allows the user to create open-ended reports. A start date with no end date means everything from that date onwards. An end date with no start date means everything up to that date. Leaving both blank removes the date restriction entirely.

At that point, every control on the report can be optional. That flexibility is useful, but it introduces another design issue: validation. A formula can accommodate a start date later than the end date, but should it? Technically, the result will simply contain no matching records. From the user's perspective, however, "No matching records" is misleading. The problem is not that the data contains no records. The report criteria are invalid.

This is where report design extends beyond the FILTER expression itself. Before running the report, we can test whether both dates are populated and whether the start date exceeds the end date. If so, the formula can return "Start date must be before end date". Similarly, if a minimum value is expected to be numeric, we can validate it rather than allowing an accidental text entry to produce confusing results.

The purpose of these checks is not merely to suppress Excel errors. It is to distinguish between three fundamentally different states: valid criteria with matching data, valid criteria with no matching data, and invalid criteria. Those states should not necessarily look the same to the user. This is especially important in interactive reports because the user is not necessarily the person who built the workbook. They should not need to understand the formula in order to determine why the output disappeared.

There is another useful refinement we can make to the report. So far, FILTER returns every column from tblTransactions. The user may not need all of them.

As we saw in the previous posts, filtering and presentation are separate concerns. We can first establish the matching records and then construct the output we actually want. For example:

Now the formula has distinct stages again. The criteria define the population. FILTER creates the matching dataset. CHOOSECOLS defines the reporting structure. SORTBY defines the presentation order. VSTACK adds the headings. This separation becomes particularly useful when the reporting requirement changes. If the user wants another source field displayed, the filtering logic does not need to change; if the user wants the report sorted by value instead of date, the criteria do not need to change; if another filter control is added, the presentation logic does not need to change. Each part of the formula has a clear responsibility.

There is also nothing preventing the filtered dataset from becoming the input to the aggregation techniques we have already developed. Suppose the user does not want transaction-level results at all. They want to set the same optional controls and then see a customer summary. The first half of the formula remains essentially unchanged. We create Data from the user-selected population. After that, rather than using CHOOSECOLS to produce transaction rows, we identify the unique customers and aggregate against them. The filtering interface and the reporting grain are independent.

That is an important architectural idea. The controls answer, “Which source records belong in this analysis?” The aggregation answers, “At what level should those records be reported?” The presentation answers, “Which fields and measures should the user see?” Keeping those questions separate makes complex reports much easier to reason about. It also means that one set of controls can potentially drive several outputs. The same selected population might feed a detailed transaction report, a customer summary and a monthly trend.

At that point, however, another issue appears. If each output contains its own copy of the filtering logic, we are repeating the same potentially complicated calculation several times. Any change to the definition of the reporting population then has to be reproduced across multiple formulas. We could solve that with helper ranges, but doing so starts to undermine one of the advantages of keeping the transformation self-contained.

There is another possibility. Throughout this series, LET has allowed us to name calculations inside a formula. But those names disappear as soon as the formula finishes calculating. What if we could take a useful piece of reporting logic—such as our optional filtering rules—give it a name of its own, and reuse it throughout the workbook as though it were a native Excel function?

Modern Excel allows exactly that. And that takes us naturally to the next stage: using LAMBDA to turn repeated dynamic-array reporting patterns into reusable functions.

Full Service Consulting

Reporting

Automation

Cat On A Spreadsheet