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 - it 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 and 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 - and in simple formulas, it often is. As formulas become more sophisticated, however, LET becomes an architectural tool.

Consider the difference between repeatedly writing:

throughout a formula and assigning the result a name:

The second approach gives the formula an internal structure. We can think of North as an intermediate dataset created during execution. The subsequent stages operate on that dataset rather than reconstructing it. This makes the formula easier to read, easier to modify, and often more efficient because the same calculation does not need to be performed repeatedly. The formula begins to resemble a small program, although it remains entirely within the worksheet.

Transforming Rows Rather Than Individual Cells

There is another important consequence of this pattern. Once a dynamic array has been created, we should resist the temptation to return immediately to cell-by-cell thinking.

Suppose our filtered data contains customer names and we want to convert them to uppercase. We do not need to think about applying UPPER to each row individually. We can simply write:

The entire filtered array passes through UPPER. Likewise, if we wanted to extract the year from every date:

Or if we wanted to round every value:

This is the essential dynamic-array mindset: the array is the unit of work. Once that idea becomes natural, many formulas that previously seemed to require helper columns become considerably simpler.

Transforming More Complicated Results

Simple functions such as UPPER, ROUND, or YEAR can operate directly on arrays, but more complicated transformations sometimes require a function that explicitly applies a calculation to each element. This is where functions such as MAP become particularly useful.

Imagine that we have filtered a list of customer names and want to apply a more complicated transformation to each name. We can write:

Here the architecture is slightly more explicit: FILTER produces the dataset, MAP iterates over that dataset and the LAMBDA defines what should happen to each element. The result is another dynamic array.

This gives us an important refinement of the original pattern:

Filter → Transform each element → Return.

The distinction between direct array operations and MAP is something we will explore much more deeply later in this series. For now, the important principle is simply to recognise that an entire dataset can flow through multiple transformations without requiring a collection of physical helper cells.

Transforming the Shape of the Result

Transformation does not necessarily mean changing the values. Sometimes the transformation is structural.

Perhaps we have filtered a table but only want three of its columns. CHOOSECOLS allows us to express that directly:

The records remain unchanged, but the shape of the returned dataset has changed. This is an important distinction because modern Excel treats arrays as multidimensional objects. We can manipulate their rows and columns independently, selecting, removing, stacking, or reshaping them as required. The filter does not have to be the final output. It can simply be the first stage in constructing a new dataset.

Adding Calculated Columns

Another common reporting requirement is to retain the original data while adding a calculated field.

Suppose our filtered dataset contains a transaction value, and we want to add a classification indicating whether the transaction is above €1,000. We could build the output with HSTACK:

The original filtered data is preserved, while a new calculated column is appended to the right. This is another example of thinking in terms of datasets rather than individual cells. We have taken one array and constructed another array from it.

The Pattern in Its Simplest Form

At this point, the architecture can be reduced to something very simple: Source data -> FILTER -> Transform -> Output.

The transformation might be almost anything. It could modify values, extract columns, calculate classifications, clean text, convert dates, add derived fields, or reshape the dataset entirely. The important thing is that the stages are conceptually separate. This separation makes complicated formulas easier to reason about because each stage answers a different question: Which records do I want? What should I do with them? What should the final result look like?

Why This Is Better Than Adding Helper Columns

There is nothing inherently wrong with helper columns. In fact, helper columns can make complicated calculations easier to understand, particularly when a workbook is being maintained by users who are not comfortable with advanced formulas.

The problem arises when helper columns exist purely because Excel previously required them. If a column exists only to identify records that will subsequently be filtered, or another exists only to perform an intermediate calculation that is immediately consumed elsewhere, the dynamic-array approach may allow the entire structure to be replaced with a single transformation pipeline. The result is not merely fewer columns. It is a clearer separation between the source data and the analytical logic: the source table remains a source table while the report formula becomes the transformation layer.

A Practical Reporting Example

Suppose our source table contains thousands of transactions, and management wants a live report showing only overdue transactions above €500, sorted by amount from largest to smallest. The requirement sounds reasonably complicated when expressed as a series of manual Excel operations. With modern formulas, however, it maps naturally onto the design pattern:

First, filter the transactions according to the business rules.

Then sort the resulting dataset.

Finally, return the required columns.

A formula might look like this:

The formula does not describe a sequence of cell operations. It describes the report itself:

Take the source data.

Keep overdue transactions above €500.

Sort them by value descending.

Return the result.

That is the essence of declarative formula design.

The Pattern Is More Important Than the Functions

It would be easy to leave this article thinking that the important lesson is learning FILTER, CHOOSECOLS, HSTACK, LET, and SORTBY. Those functions certainly matter, but they are not the real objective. The more valuable skill is recognising the underlying pattern.

When confronted with a reporting problem, ask whether it can be expressed as:

Select the relevant data → transform it → return the result.

Once you start thinking this way, the individual functions become interchangeable tools. Perhaps the transformation requires TEXTAFTER. Perhaps it requires MAP or IF. Perhaps the output needs to be reshaped with HSTACK. The architecture remains the same. That is what makes a design pattern more useful than a collection of function-specific tricks.

From Patterns to a Formula Pipeline

Eventually, several patterns can be combined. A real reporting formula might filter a dataset, clean its text, perform a lookup, calculate a derived value, sort the results, select the required columns, and finally stack the output alongside another dataset. That sounds complicated, but the underlying principle remains the same. Each stage receives an array and produces another array. The output of one stage becomes the input of the next. Once formulas are approached as pipelines rather than isolated calculations, modern Excel becomes considerably more powerful.

And this brings us naturally to the next design pattern. Filtering and transforming data is only one side of the problem. In real reporting work, we constantly encounter information that needs to be split apart, rearranged, combined, or reconstructed into a different shape. That is where the next pattern becomes particularly useful.

Full Service Consulting

Reporting

Automation

Cat On A Spreadsheet