Microsoft describes dynamic arrays as Excel formulas that return sets – or arrays – of values to neighbouring cells, based on specific reference points. The term the software giant uses for this form of data population is ‘spilling’.

But how does that functionality impact the work of accountants? To find out how dynamic arrays streamline everyday task management, Insights spoke to Ruth Butler-Lee, Head of Financial Modelling at Arriva UK Trains.

Hidden figures

“Dynamic arrays are functions that enable you to put a formula in one cell and return multiple results across a range of cells, depending on the size of the input ranges,” Butler-Lee explains. “If the number of rows or columns in the input range increases, the formula will automatically spill into the rows and columns around it.”

In Butler-Lee’s experience, one major use case for dynamic arrays would be to rationalise large models that take up a lot of memory space and, as a result, can end up running rather slowly. Using dynamic arrays can significantly reduce a model’s memory size and speed up calculations, she says.

Another use case is to help you address models with variable numbers of input rows – for example, if you are importing data from an Excel file that has a different number of rows for each period. 

Butler-Lee notes: “Before dynamic arrays, one common error would have been to import rows of data into your spreadsheet, but forget to copy the calculations down to include the new inputs. So although you’ve brought in lots of new inputs, they’re essentially ignored.”

As dynamic arrays automatically spill that data, they remove the risk of it being overlooked.

More generally, Butler-Lee says, dynamic arrays can make other aspects of Excel easier to work with. “One interesting case is the UNIQUE function,” she points out. “It lists all of the unique items in a range of data. So if you’re working with some cost data, for example, you can use the function to pick out all the unique categories to which that data is assigned. In the ‘pre-dynamic arrays’ era, that task was more difficult. We had helper functions – but now, you can do it all with just one formula.”

That improved ease of use also impacts the SORT function, which enables users to order a dataset, say, alphabetically, or rank it from the highest-value item to the lowest. While users would once have had to go through a tricky workaround of calculations to determine the ordering, they can now simply apply a single formula.

Shadow model

For Butler-Lee, best-practice usage of dynamic arrays should be framed in the context of certain notable drawbacks. First, she says, the newness and unfamiliarity of dynamic arrays makes the results they produce quite hard to review. To an untrained eye, their syntax may look unexpectedly different, alien and perhaps even intimidating.

Another issue is that some of the standard tools third parties would typically use to audit financial models in Excel do not work with dynamic arrays.

Butler-Lee notes: “There’s a feature called Evaluate Formula, found in the Formula Auditing section of the ribbon’s Formula tab, which enables you to unpick a calculation step by step so you can understand what’s happening. In other words, it breaks down calculations so you can assess whether they’re doing the right thing. But because dynamic arrays populate multiple cells at once, you can’t really do that.”

With those issues in mind, Butler-Lee advises users to break down dynamic arrays into simpler, separate stages, rather than trying to pack too much into one formula in a single cell. That would make it easier for a third party to follow the trail and grasp what sort of effect the data preparer has sought to achieve.

In parallel with their dynamic arrays, users could also operate a ‘shadow model’, based on older-style formulas. This would provide a ready reckoner, enabling users to check whether their new model is doing what it is meant to do. Butler-Lee explains: “For testing purposes, you’d have a really big model based on older Excel formulas, which would be slower. But for day-to-day use, you’d have your quicker, new model that you know is working, because every time you transfer the data into the bigger one, it’s giving you the same outputs.”

Controlled spills

Butler-Lee urges accountants to manage how the data from dynamic arrays spills, so it is easy to follow.

“Dynamic arrays allow you to spill both down rows and across columns to populate an entire block on a spreadsheet,” she says. “However, formulas that spill across both rows and columns can be quite difficult to decode.”

As such, another best-practice approach she recommends is to spill either down or across, instead of both. “You may find just spilling across rows or down columns results in a much more understandable formula, which is less likely to contain an error.”

Users should also take care to ensure that blocks of data do not spill into each other. “You would typically see a series of data blocks going down your spreadsheet, but because they can resize as a result of dynamic arrays, you may find that the number of rows in a given block increases and spills into the next block, which would spill into the next, and so on,” Butler-Lee says. “That will start to cause errors within your model. To avoid that, if you’ve opted to spill down the rows, don’t stack all your calculation blocks within the same sheet. Either put each block on a separate sheet, or spill your calculations across.”