Cat On A Spreadsheet

The examples in this series have deliberately been fairly clean. A table contained transactions, another contained product information, and the reporting requirement could be expressed in terms of a small number of fields. That was useful while examining individual techniques, but it is not particularly representative of the workbooks most of us encounter in practice. Real reports tend to start with something less convenient.

The source may contain thousands of rows extracted from another system. Several records may belong to the same customer. Some transactions may be cancelled and therefore need to be excluded. The report may be required for a particular month and department, while management does not want to see transactions at all. They want one row per customer, showing total value, number of transactions, average transaction value and perhaps the customer's share of the overall result. That is the sort of requirement where dynamic arrays become much more interesting.

Suppose we have an Excel Table called tblTransactions. It contains Date, CustomerID, Department, Status and Value. There may be tens of thousands of rows, and each customer can appear many times. Elsewhere, tblCustomers contains CustomerID, CustomerName, Region and AccountManager. Our report has two controls. H2 contains any date within the reporting month, while H3 contains the department to report. The final output should contain one row per customer and show the customer name, region, transaction count, total value, average transaction value and percentage of total value. Customers should appear from highest to lowest total value.

The temptation is to begin writing formulas immediately. A better place to start is with the grain of the final report.

The transaction table has one row per transaction. The report requires one row per customer. Before worrying about presentation, therefore, we know that the central transformation is from transaction-level data to customer-level data. That observation determines much of what follows.

We can start by establishing the reporting period. Because H2 can contain any date within the month, we do not need the user to enter both a start and an end date. EOMONTH can derive those boundaries for us. StartDate is EOMONTH(H2,-1)+1, while EndDate is EOMONTH(H2,0). That gives us the reporting boundaries without adding another pair of input cells to the worksheet.

Now we can define the population that actually belongs in the report. We want transactions inside the selected month, belonging to the selected department, while excluding cancelled transactions:

This is the first genuinely important stage of the report. Data is no longer the raw transaction table. It is the reporting population. Everything that follows should, wherever practical, operate against this reduced dataset rather than repeatedly interrogating the entire source table.

This is also where mistakes in real reporting systems frequently begin. If one calculation excludes cancelled transactions while another accidentally includes them, two individually reasonable formulas can produce a report that is internally inconsistent. Creating the population once helps avoid that problem.

We now need to change the grain. Assuming CustomerID is the second column of Data, UNIQUE(CHOOSECOLS(Data,2)) gives us the distinct customers represented in the filtered population.

At this point, something fundamental has changed. Data may contain 20,000 transaction rows. The resulting CustomerIDs array might contain 350 rows. We have moved from the transactional grain to the reporting grain. Every measure we create from this point onwards needs to correspond to those 350 customers.

The transaction count can be calculated with COUNTIF(CHOOSECOLS(Data,2),CustomerIDs). Assuming Value is the fifth column, total value can be calculated with SUMIF(CHOOSECOLS(Data,2),CustomerIDs,CHOOSECOLS(Data,5)). We could calculate average transaction value independently with AVERAGEIF, but that would mean performing another aggregation across the transaction data. We already know the total and the count, so the average is simply TotalValue/TransactionCount. This may appear to be a minor optimisation, but it represents a useful general principle. Once a measure has already been calculated, use it where appropriate rather than recalculating the same underlying information through another route.

We now have customer IDs and three measures, but management does not particularly want to see internal IDs. We need descriptive information from the customer master. This is precisely the enrichment problem from an earlier post. If CustomerName and Region are adjacent in tblCustomers, a single XLOOKUP can return both fields:

From there, CHOOSECOLS(CustomerData,1) gives us the customer name and CHOOSECOLS(CustomerData,2) gives us the region. The report is now beginning to take shape.

Management also wants each customer's contribution to the total. We already have an array containing the total value for every customer, so there is no reason to return to the transaction table. SUM(TotalValue) gives us the grand total, and TotalValue/GrandTotal gives us each customer's share.

Again, we are calculating from the aggregated result rather than going back to the transactional data. That distinction becomes increasingly useful as reports become more sophisticated. The raw transactions were necessary to calculate TotalValue, but once TotalValue exists, many higher-level measures can be calculated from the customer-level array.

We can now put all of those stages together:

There is quite a lot of formula here, but the interesting part is not its size. It is that we can describe almost every line in business terms. We establish the reporting month, select the valid transactions, identify the customers, count their transactions, calculate their totals and averages, retrieve their names and regions, calculate their contribution to the whole, build the report and sort it. The Excel syntax follows the reporting logic. That is considerably easier to maintain than a formula whose internal structure follows whichever functions happened to be nested first.

There is, however, a weakness in the formula as written. FILTER has no [if_empty] argument. If the selected month and department contain no qualifying transactions, the report does not return an empty management report. It returns #CALC!. It may be tempting to add "" as the final argument to FILTER, but that introduces a less obvious problem. Data is supposed to behave like a transaction dataset. If no records exist, replacing that dataset with a single empty string changes its shape. Later operations such as CHOOSECOLS(Data,5) are expecting columns that no longer exist.

This is a good example of why error handling in array formulas deserves more thought than simply making the error disappear. The absence of records is not an error in the source data. It is a legitimate reporting outcome. A cleaner design is to determine whether qualifying records exist and handle that condition at the level of the report. For example, the same logical conditions can be summed to produce a MatchCount. If MatchCount is zero, the formula can return an appropriate empty-state result rather than attempting to construct the customer report.

That raises an interesting design question. Should the result be "No transactions found", or should it return the headings with no data beneath them? There is no universally correct answer. For an interactive worksheet, a clear message may be preferable. If another formula or process consumes the report, preserving a consistent tabular structure may be more important. This is precisely the kind of decision that separates a technically correct formula from a well-designed reporting system.

Now suppose management changes the requirement. They no longer want every customer. They want only customers responsible for at least €1,000 of activity during the month. Because we have already calculated TotalValue, this is a small addition. We can define FilteredReport as FILTER(Report,TotalValue>=1000) and sort that array instead of Report. There is no need to revisit the raw transactions. More importantly, the €1,000 test is being applied at the correct stage. It is a condition on an aggregated customer measure, not a condition on individual transactions. Filtering the original transaction table for values of €1,000 or more would answer a completely different question. A customer with ten €200 transactions has contributed €2,000 and belongs in our management report, even though none of the individual transactions reaches the threshold.

This distinction appears constantly in real reporting work. A condition may apply to source records or to groups created from those records, and confusing the two changes the meaning of the report.

Suppose management makes another request. They want the top ten customers. Again, the existing structure makes this straightforward. Once SortedReport exists, TAKE(SortedReport,10) returns the first ten rows. But even this apparently simple requirement contains a business question. What happens if the tenth and eleventh customers have exactly the same total? TAKE(SortedReport,10) returns ten rows. It does not understand ties. If the requirement really means “the ten highest-ranked customers, including ties”, then we need actual ranking logic rather than simply taking ten records. RANK.EQ(TotalValue,TotalValue,0) can create the ranks, after which the report can retain customers whose rank is less than or equal to ten.

The difference is subtle but meaningful. “Top ten rows” and “customers ranked ten or better” are not necessarily the same population. This is another recurring theme in reporting: the formula can only be as precise as the requirement it implements.

The same issue arises with percentages. Our ShareOfTotal measure is based on all qualifying customers. If we subsequently filter the report to customers above €1,000, should their percentage still represent their share of the original total, or should it represent their share of the displayed customers only? Both are valid calculations. They answer different questions.

If we calculate GrandTotal before the €1,000 filter, the percentage describes each customer's contribution to the entire department. If we calculate the denominator after the filter, it describes each customer's contribution to the displayed subset. The position of a single calculation inside the formula therefore changes the business meaning of the report. This is why thinking of a large LET formula as a pipeline is so useful. Each stage has a grain, a population and a purpose. At the start, our grain is transaction. After UNIQUE, our grain is customer. Before the threshold is applied, our population is every customer with qualifying activity. After the threshold, our population is only customers satisfying the management-report criterion. The calculation is easier to reason about when those transitions are explicit.

There is another improvement we could make. Our report currently retrieves customer name and region, but tblCustomers also contains AccountManager. If management wants the account manager included, the XLOOKUP return array can simply be expanded from tblCustomers[[CustomerName]:[Region]] to tblCustomers[[CustomerName]:[AccountManager]]. The third returned column can then be extracted with CHOOSECOLS(CustomerData,3) and added to the final HSTACK.

This is a small change because the report has been constructed from named components. The lookup is responsible for customer attributes. The final HSTACK is responsible for presentation. We know exactly which stages need to change.

That is a significant maintainability advantage. In many traditional reports, adding one new field means inserting a worksheet column, copying a lookup formula, changing downstream ranges, checking charts or summaries, and hoping nothing depended on the previous column positions. A dynamic-array report is not immune to structural changes, but a carefully designed one can localise them considerably.

There is also an obvious question about performance. We are filtering a potentially large table, extracting columns repeatedly, performing aggregations and running a lookup. Is this necessarily faster than helper columns or a PivotTable? No. Dynamic arrays are not a magic performance layer, and formula architecture should not be chosen on elegance alone. Large datasets, repeated calculations and volatile dependencies can make sophisticated array formulas expensive.

The purpose of this approach is not to claim that one formula is always superior. Its advantage is that the transformation can be expressed as one coherent calculation, without creating permanent intermediate structures purely to support the report. For a dataset of moderate size and a report that needs to respond immediately to worksheet selections, that can be extremely attractive. For hundreds of thousands or millions of records, or for transformations involving substantial cleansing and merging, Power Query or the Data Model may be a better architectural choice.

The important thing is recognising the boundary. Excel formulas, Power Query, PivotTables and the Data Model are not competing religions. They are different tools for different layers of a reporting solution. Dynamic arrays occupy a particularly useful position because they allow the worksheet itself to behave much more like a small data-transformation environment than it could in the past. And our management report demonstrates why.

We started with transaction-level data that was not suitable for management consumption. We restricted it to the correct reporting population, changed its grain from transaction to customer, aggregated several measures, enriched those groups from a master table, calculated measures that existed nowhere in the source, filtered and ranked the result, and constructed a presentation-ready dataset. The finished report is not a filtered copy of the source. It is a new dataset derived from it.

That distinction is at the heart of modern Excel reporting. Once we begin treating formulas as transformations between datasets rather than calculations between cells, many problems that previously required sprawling worksheet infrastructure become surprisingly compact.

But our example still has one convenient characteristic: the grouping field already exists in the source as a clean customer ID. The next real-world problem becomes more interesting when the categories we need to report do not exist in the source at all. Dates are an excellent example. A transaction table contains individual dates, while management asks for months, quarters, year-to-date comparisons, rolling periods and previous-period performance. The source gives us dates. The report needs time intelligence. That is where we will go next.

Full Service Consulting

Reporting

Automation

Cat On A Spreadsheet