Excel Dynamic Array Methods Start the discussion!

What is a Dynamic Array?

A Dynamic Array is a subsection under the topic of computer science that is a list of data structures allowing various variables to be added or removed. They are used in C++, and also in Java where they are known as Array Lists. It consists of an array that makes a big improvement in size so that more elements are able to be added. Normally, arrays are fixed in size meaning that the number of elements able to be held is fixed. When space is needed within an array, the dynamic array usually doubles in size to make space for the new element. The strengths of a Dynamic Array is that they are fast, are able to vary in size, and are also Cache-friendly. However, they can be costly to replace and take a lot of time to do so.

How is excel related to this?

However, in addition to this, Dynamic Arrays, like LAMBDA, are also a feature in excel. Within the field of excel, Dynamic Arrays are ‘resizable arrays that calculate automatically and return values into multiple cells based on a formula entered in a single cell.’ In short, they allow the user to utilise multiple different values at the same time within the same formula.

Dynamic Arrays were announced as a new feature in Excel, named as ‘dynamic array formulas’ in the Autumn of 2018. In the same way as with LAMBDA, dynamic arrays do not work with older excel models, only working with Excel 365, and all future updates. When first introduced, they revolutionised the way that users navigate the database since they allow individuals to work with multiple different values at the same time within a formula. They work to solve some difficult problems used within excel, and allow the user to work much more efficiently.

As part of this dynamic array development, Excel has released a range of new functions to help to even improve the efficacy of dynamic arrays. These include filter, randarray, sequence, sort, sortby, unique, xlookup, xmatch. The later two, xlookup and xmatch were set to be updated versions to replace the vlookup and match functions.

A large element of Dynamic Arrays involves spilling. Spilled array formulas are formulas that are successfully spilling. For instance ‘older array formulas, known as legacy array formulas, always return a fixed-size result.’ However ‘spill means that a formula has resulted in multiple values, and those values have been placed in the neighbouring cells.’ Therefore this is said to be dynamic as it differs to the original fixed state formulas.

Two find out more information regarding how to use these functions within the subset of dynamic arrays, click here.