Cat On A Spreadsheet

One of the most useful things about dynamic arrays is that they change what we mean when we talk about a formula returning a result. For a long time, an Excel formula was essentially a cell-level operation. We gave a formula a value, it performed some calculation, and it returned another value to the cell containing it. Even when the formula referred to a large table, we tended to think about the result one row at a time.

Dynamic arrays introduce a different model. A formula can return an entire collection of values, and that collection can then become the input to another calculation. We can filter it, sort it, remove duplicates, look values up against it, change its orientation, or combine it with another array. That becomes particularly interesting when the source data is text.

In the previous articles in this series, we looked at TEXTSPLIT and how it can be used to turn delimited text into an Excel array. Splitting the text, however, is rarely the real objective in a reporting environment. Usually, the text has been stored in that form because some external system has decided that several values belong in one field. What we actually need is a usable dataset.

Suppose, for example, that an export contains this in a single cell:

Dublin|Galway|Cork|Limerick

We can turn it into an array very easily:

The result spills across the worksheet as four separate values. That is useful, but it is only the first step. Perhaps the report we are building needs those locations to appear vertically rather than horizontally. There is no need to extract each value separately and place it into a different row. We can simply change the shape of the array.

The values now spill down the worksheet instead.

This distinction between the contents of an array and its shape is worth becoming comfortable with. We have not changed the underlying information at all. We have simply changed the way Excel represents it on the worksheet, which can make a considerable difference to what we can do with it next.

Real data, of course, is rarely as clean as our first example.

Suppose the source actually contains:

Dublin|Galway||Cork|Dublin|Limerick|

Now we have an empty value, a duplicate location and another empty value at the end. If the purpose of the calculation is to produce a clean list of locations, we probably do not want any of those things. This is where the idea of combining dynamic-array functions becomes more interesting than learning any individual function. We can first split the text, then remove the empty elements, then remove duplicates, and finally sort the result.

The resulting list contains each location once, in alphabetical order. There is nothing particularly remarkable about any one of the functions in that formula. What matters is that each one performs a small part of the transformation. TEXTSPLIT creates the array, FILTER removes values we do not want, UNIQUE reduces the result to distinct values, and SORT puts the final dataset into a predictable order.

This is one of the most useful ways to think about modern Excel formulas. Rather than looking for a single function that solves the entire problem, we can treat the formula as a sequence of transformations applied to a dataset.

The same approach becomes even more useful when the values need cleaning before they are used. Imagine that a system export contains categories in a single field:

The spaces after some of the commas are now part of the values. To a person looking at the worksheet, " Financial" and "Financial" may appear effectively identical. To Excel, they are different strings.

We can clean the values as we split them.

If we also want to eliminate empty values, duplicates and inconsistent ordering, we can continue the transformation:

At this point, we are doing something that would traditionally have been described as data preparation. We are taking an inconvenient representation of the data and producing a structured array that can be used by the rest of the report. That distinction is important because the array does not necessarily have to be the final output of the formula.

Suppose we have a reference table containing category descriptions. The source system gives us:

while our reporting table contains:

Once the categories have been converted into an array, we can pass that array directly into XLOOKUP.

The important part here is that TEXTSPLIT does not have to produce one value for XLOOKUP. It can produce an entire array, and XLOOKUP can operate on that array. The result is another array containing the corresponding descriptions.

That is a much more significant change in thinking than it might initially appear. We are no longer writing a lookup formula for one category and copying it down or across the worksheet. We are giving Excel a collection of lookup values and asking it to perform the same operation across the collection. We can then reshape the result if necessary.

Or perhaps we want the category and its description together as a small reporting table. In that case, HSTACK allows us to construct the two-column result directly.

The result is a dynamically generated table containing the categories and their descriptions.

This is where the concept of reshaping becomes particularly useful. A dynamic array does not have to be regarded as the finished report. It can be an intermediate dataset that is passed through several stages before it reaches the worksheet in the form we actually want.

The same principle applies when combining datasets. Suppose two departments have supplied lists of case numbers. One list contains the cases handled by the first department and another contains those handled by the second. If the two datasets have the same structure, VSTACK can combine them into one array.

If the requirement is to produce one consolidated list without duplicates, we can continue the transformation.

And if the report should be ordered:

Again, the individual functions are relatively straightforward. What is interesting is the way they can be composed.

The business requirement might be expressed in ordinary language as: combine the two departmental lists, remove duplicate cases and sort the result. The Excel formula expresses essentially the same logic:

That is one of the characteristics that makes modern Excel formulas particularly attractive for reporting. Once we start thinking in terms of arrays rather than individual cells, the formula can become a description of the transformation we want Excel to perform. There is, however, a point at which simply nesting functions begins to make the formula difficult to understand.

Consider the earlier example:

The formula works, but TEXTSPLIT(A2,",") has been calculated twice. More importantly, if the transformation becomes more complicated, it becomes increasingly difficult to see what each stage represents. This is where LET becomes useful.

The formula now has a small internal data structure. We create an array called Categories, and the rest of the formula operates on that array.

This is a useful way to understand LET. Its purpose is not merely to make formulas shorter. It gives us a way of naming intermediate results, which becomes increasingly valuable when a dynamic-array formula starts to resemble a small data-processing procedure.

The distinction between a calculation and a transformation pipeline is becoming important here. A conventional Excel formula might answer a question such as "what is the total value of these transactions?" A dynamic-array formula can answer something more like: "Take these records, select the ones meeting these conditions, transform their contents, enrich them with information from another table, remove duplicates, order the results and return the resulting dataset." That is much closer to the kind of work we normally associate with data-processing tools.

It does not mean that every reporting problem should be solved this way. There are still situations where Power Query, VBA, SQL or another dedicated data-processing tool is the better choice. A formula that performs a dozen transformations on a badly designed source dataset can easily become more difficult to maintain than the problem it was intended to solve. The objective is not to create the cleverest possible formula. The objective is to create a reliable transformation whose logic is clear.

That is also why it is useful to distinguish between data preparation and presentation. We might first produce a clean vertical array because that is the easiest form in which to perform a calculation, then reshape it horizontally because that is how the final report needs to display it. Alternatively, we might keep the data in a tabular form throughout and only worry about presentation elsewhere. Once we begin working this way, TEXTSPLIT, FILTER, UNIQUE, SORT, TRANSPOSE, XLOOKUP, HSTACK and VSTACK stop looking like unrelated functions. They become components of a larger vocabulary for manipulating arrays. A piece of delimited text can become an array. That array can be cleaned, reduced, ordered and enriched. Several arrays can be combined into a larger dataset. The resulting dataset can then be reshaped into whatever form the next stage of the report requires.

The important part is that none of these operations requires us to know in advance how many values will be produced. If the original cell contains three categories, the formula returns three results. If it contains seven, the same formula returns seven. If the source changes tomorrow, the spilled range changes with it. That is fundamentally different from designing a worksheet around a fixed number of rows and copying formulas into those rows. And this is where the idea of dynamic arrays starts to become much more interesting than simply having formulas that "spill." The spill behaviour is the visible part. The more important change is that Excel can now manipulate a collection of values as a collection. Once we start thinking in those terms, splitting text is only one small part of the process. The more interesting question is what we do with the array after we have created it.

That brings us to the next problem in our reporting series. So far, we have mostly dealt with transforming the contents of individual records. The next step is to start transforming the dataset as a whole: identifying groups, calculating metrics for those groups, comparing them and ultimately producing a dynamically generated report. That is where dynamic arrays begin to move from data preparation into reporting itself.

Full Service Consulting

Reporting

Automation

Cat On A Spreadsheet