Cat On A Spreadsheet

Over the previous posts, we have gradually assembled most of the tools required to build a dynamic report. We have filtered records, enriched them with information from other tables, grouped and aggregated them, compared one dataset with another, and sorted or ranked the resulting arrays. Until now, however, we have generally approached each of those operations as a problem in its own right. The more interesting question is what happens when they stop being separate problems.

A real report rarely exists because somebody wanted to demonstrate FILTER, XLOOKUP or HSTACK. It exists because somebody wants to open a workbook, choose a reporting period or department, and see the information necessary to make a decision. The individual Excel functions are implementation details. That changes how we should approach the formula. Rather than beginning with a function and asking what we can do with it, we can begin with the final report and work backwards. What should one row represent? Which records should be included? Which fields already exist in the source data? Which need to be retrieved from elsewhere? Which values need to be calculated? In what order should the result appear? Once those questions have been answered, the formula becomes a pipeline for constructing the required dataset.

Suppose we have a transaction table called tblSales. Each row contains a transaction date, product code, quantity and salesperson. The product description, category and unit price are maintained separately in tblProducts. Our report should allow the user to enter a start date in H2 and an end date in H3, then return every transaction during that period with the product description, category, quantity, unit price and calculated sales value.

The first task is simply to establish the population.

There is nothing particularly new here. The two logical expressions create arrays of TRUE and FALSE values, multiplication acts as an AND operation, and FILTER returns records satisfying both conditions. What matters is that we have deliberately made this the first stage of the report.

There is little reason to perform lookups and calculations against thousands of transactions if the report ultimately requires only a small subset of them. By reducing the population first, the rest of the formula can operate against the dataset that actually matters.

We can give that dataset a name:

This may seem unnecessary while the formula contains only two lines, but Data is about to become the foundation for everything that follows.

Suppose the columns in tblSales are Date, ProductCode, Quantity and Salesperson. We can extract the product codes from our filtered dataset with CHOOSECOLS:

Those codes provide the relationship between the transaction data and the product master. We can therefore enrich the filtered transactions:

At this point, we already have a report of sorts. The original transaction records have been filtered to the requested period and supplemented with information from the product master. But the array is not necessarily arranged the way we want to present it.

This is an important distinction. The most convenient structure for storing data is not always the most convenient structure for reporting it.

Our source table might contain the product code because it is the correct identifier for the transaction. The person reading the report, however, may care much more about the product description and category. Similarly, the source column order may have been determined by the system that produced the data rather than by the requirements of the report. We can therefore construct the output explicitly rather than simply returning the source array with a few extra columns attached.

Assuming ProductData returns description, category and price, we can select each part we need:

The formula has become longer, but something useful has happened. The final report is no longer constrained by the physical arrangement of either source table. We have separated storage structure from reporting structure.

That is one of the most useful habits to develop when working with dynamic arrays. CHOOSECOLS, HSTACK and related functions mean that the shape of the source data does not have to dictate the shape of the output.

Now we can introduce information that does not exist in either source table. Sales value is quantity multiplied by unit price:

Because both Quantity and Price are arrays of corresponding size, Excel performs the multiplication element by element. We do not need to create a calculated column in tblSales, and we do not need to fill a formula down the worksheet. The complete construction becomes:

We are now very close to a complete reporting dataset. There is still a presentation problem, however. A spilled array contains values, not column headings.

We could type the headings manually above the formula, but that creates a small structural weakness. If we later change the output columns, the headings and the formula have to be maintained independently.

Instead, we can make the headings part of the array itself.

VSTACK places our heading array above the report array. The output is now self-contained. One formula produces both the structure and the contents of the report.

There is a subtle advantage to this beyond convenience. The headings are now logically attached to the fields being returned. If the report is moved elsewhere or used as the input to another formula, the entire structure travels together.

We can improve it further by deciding how the report should be ordered. Suppose the reader wants the most recent transactions first. Rather than sorting the source table, we sort the report array:

The end of the formula then becomes:

This distinction matters. We are not modifying the source data to suit the report. We are defining the presentation order of the report independently. The same principle applies if we want the largest sales values first:

Or if we want to sort first by category and then by descending sales value:

By this stage, the formula is doing something that would traditionally have required several worksheet operations. It is selecting the reporting population, joining it to another dataset, calculating a measure, rearranging columns, sorting the result and adding headings. Yet there is no intermediate worksheet.

That is perhaps the most important characteristic of this approach. LET is effectively allowing us to construct a series of temporary datasets inside a single formula. Data is one dataset. ProductData is another. Report is another. SortedReport is the final transformation before presentation. Those names exist only during calculation, but conceptually they behave rather like stages in a data-processing pipeline.

This becomes even more apparent when we introduce another user selection. Suppose H4 contains a category, with "All" used when no category filter should be applied. We could incorporate that condition into the initial filtering operation, but doing so can quickly make the first line difficult to read. Another option is to create the base period first and then apply the category condition after the product information has been retrieved. Because category does not exist in tblSales, we cannot filter the original transactions by category until the product lookup has taken place.

That observation determines the order of our transformations. We first filter by date, because date belongs to the transaction table. We then enrich the data with category, because category belongs to the product table. Only after that can we filter by category.

This is an important point. The order of operations should follow the dependencies in the data rather than whichever function happens to come to mind first. We can construct the report and then apply the category filter:

At first glance, this might look like a rather large formula. Compared with the formulas we started with earlier in the series, it certainly is. But length and complexity are not the same thing. Read from top to bottom, the formula describes what the report does. It creates the date-filtered data. It identifies the product codes. It retrieves the product information. It extracts the fields required for presentation. It calculates sales value. It constructs the report. It applies the category selection. It sorts the result. It adds the headings. The formula is long because the report performs several operations, not because those operations have been hidden inside an incomprehensible nest of functions.

That distinction is exactly why LET becomes so important as dynamic-array formulas grow. Without it, the same report could be compressed into a much shorter-looking expression containing repeated FILTER, XLOOKUP, CHOOSECOLS and SORTBY calls nested inside one another. It might occupy fewer lines, but understanding or modifying it six months later would be considerably harder. A reporting formula should not be judged by how few characters it contains. It should be judged by whether its logic remains understandable.

There is another reason to construct the report in stages: debugging. Suppose the final result is wrong. Because each important intermediate result has a name, we can temporarily replace the final expression with one of those names. Instead of returning:

we might temporarily return <<Report>> to inspect the constructed dataset before filtering and sorting, or <<SelectedReport>> to check whether the category selection is behaving correctly.

This makes a large LET formula surprisingly practical to develop. It does not need to be written perfectly from beginning to end. We can build it incrementally, checking each intermediate array before introducing the next transformation.

That approach also changes how errors should be handled. It can be tempting to wrap the entire formula in IFERROR:

The result may look clean, but it also hides every possible error behind the same message. A missing product code, an invalid reference and a genuine absence of transactions could all appear to mean "No data". That makes the report prettier at the cost of making it less trustworthy.

A better approach is usually to handle expected conditions at the stage where they can occur. For example, an empty date selection is an expected reporting condition and can be handled deliberately. A product code that cannot be found in the product master is a different issue. That may indicate bad source data and should not necessarily be disguised as an empty report. The formula should therefore distinguish between nothing matched the reporting criteria and something went wrong while constructing the report. This is not unique to dynamic arrays, but the more logic we place inside a single reporting formula, the more important that distinction becomes.

There is also a practical boundary to this technique. The fact that Excel can construct an entire report inside one formula does not mean that every report should be built that way. If the source data requires extensive cleansing, dozens of transformations, relationships across many large tables or repeated reuse across several reports, Power Query, the Data Model or another data-processing layer may be the more appropriate tool. Dynamic arrays are particularly attractive when the source data is already reasonably structured and the reporting transformation needs to remain visible, responsive and closely integrated with the worksheet. The objective is not to prove that everything can be done with formulas. The objective is to recognise when formulas have become capable of doing something that previously required a much more elaborate worksheet structure. And that is where the change introduced by dynamic arrays becomes most apparent.

A traditional worksheet often grows horizontally. Source data is followed by helper columns, lookup columns, calculated columns and intermediate results. A summary area then refers back to those calculations. Each additional requirement tends to create another piece of worksheet infrastructure. A dynamic-array report can grow differently. The complexity can remain inside the calculation while the worksheet itself stays relatively simple. Source tables hold source data. Input cells hold report parameters. One formula constructs the output. That does not automatically make the workbook better. A badly designed 40-line formula can be every bit as unpleasant as a worksheet containing 20 helper columns. But when the transformations are logically structured and named with LET, the formula begins to resemble a description of the reporting process rather than a collection of cell calculations.

We started this series by looking at individual dynamic-array operations. We filtered records, split and reshaped data, enriched records through lookups, aggregated them into groups and compared one dataset with another. Here, those techniques finally converge. We are no longer using dynamic arrays simply because spilling is convenient. We are using arrays as the intermediate language of the report. Each stage receives a dataset, changes its shape or meaning, and passes another dataset to the next stage. That opens the door to much more realistic reporting problems. Because once we can construct a complete reporting dataset in memory, the next interesting question is no longer which dynamic-array function we should learn next. It is what happens when we take messy reporting problems from the real world and try to solve them using these patterns.

Full Service Consulting

Reporting

Automation

Cat On A Spreadsheet