Cat On A Spreadsheet

One of the most important changes introduced by modern Excel is not a particular function, but a different way of thinking about formulas. For a long time, Excel formulas were generally designed around individual cells. We would identify the value we wanted, write a formula to calculate it, and then copy that formula down through the rows that required it. If the calculation needed several intermediate stages, we would create helper columns, with each column performing another part of the transformation. This approach worked extremely well, and it remains perfectly valid for many situations, but it encouraged us to think of Excel as a collection of individual calculations rather than as an environment capable of processing datasets as a whole. Dynamic arrays have changed that.

A modern Excel formula can receive an entire table, select the records that matter, transform those records, and return the completed result as a single spilled array. Once this becomes familiar, a surprisingly large proportion of reporting problems can be understood as variations of a small number of recurring design patterns.

The first of those patterns is perhaps the most fundamental:

Filter → Transform → Return.

It sounds simple, but learning to recognise this pattern is one of the most useful steps towards becoming comfortable with modern Excel.

Starting with the Problem Rather Than the Formula

Imagine that we have a table containing sales transactions. Each row contains a customer, region, product, transaction date, quantity, and value. A traditional approach to producing a regional report might involve adding a helper column that determines whether each transaction belongs to the required region. Another column might calculate a particular value, followed by another formula that prepares the information for presentation. Finally, the relevant rows might be copied into a report.

The modern approach begins somewhere else. Instead of asking which formulas need to be placed into which columns, we ask what the final dataset should contain.

Suppose we want a report containing only transactions from the North region, with the transaction value increased by 10% for reporting purposes. The requirement can be described in three stages. First, identify the records belonging to the North region; second, transform the relevant values; third, return the resulting dataset. That is the pattern.

The FILTER Stage

The first stage is handled naturally by FILTER. Suppose our source is an Excel Table named SalesTable, with a Region column. We can retrieve the relevant records with:

The important thing here is not merely that FILTER removes unwanted rows - the formula returns an array. That distinction is fundamental.

The result of FILTER is not just another value that happens to occupy a cell. It is a dataset that can immediately become the input to another function. This is where dynamic-array thinking begins to differ from traditional formula design. We are no longer necessarily calculating a result one cell at a time. We are constructing a pipeline in which one operation produces the data consumed by the next.

The TRANSFORM Stage

Filtering is often only the beginning. Once we have isolated the relevant records, we may need to modify them before returning the final report. Perhaps a price needs to be adjusted, a text value cleaned, a date converted into a reporting period, or a calculated classification added. For example, suppose the filtered dataset contains a Value column and we want to increase those values by 10%. We could calculate the adjusted values separately:

This produces a dynamic array containing only the North-region values, with the adjustment applied to every element. The important point is that Excel performs the multiplication across the entire returned array. There is no formula to copy down. There is no helper column. There is simply a transformation applied to the dataset returned by FILTER.

Combining the Stages

The real strength of the pattern appears when the operations are combined. Suppose our report needs to display the customer name and adjusted transaction value for North-region transactions. We can construct the two arrays independently and combine them with HSTACK:

The exact column numbers will obviously depend upon the structure of the table, but the architecture is what matters.

FILTER selects the records.

CHOOSECOLS extracts the information we need.

The multiplication transforms the value.

HSTACK assembles the final report.

LET gives the intermediate dataset a name so that we do not have to repeat the filtering operation.

The formula is effectively a miniature data-processing pipeline.

Why LET Matters

At first glance, LET might appear to be an optional convenience.

In simple formulas, it often is.

As formulas become more sophisticated, however, LET becomes an architectural tool.

Consider the difference between repeatedly writing:

text5

text6

text7

text8

text9

Full Service Consulting

Reporting

Automation

Cat On A Spreadsheet