New Dynamic Array functions – be careful what you wish for

The most recent Excel release to the Beta Channel includes 14 new functions: 3 for manipulating text and 11 for working with Dynamic Arrays. The Excel Community will be covering all of them in some detail in future posts but there is one particular function that is worth taking an early look at: VSTACK().

Just over one year ago I posted an article considering a range of recent Excel updates and bemoaning the fact that several of the updates seemed to fall a little way short of what they could have achieved. When looking at Dynamic Arrays, the article considered the inability of a Dynamic Array to be included as an Excel Table and also suggested that one very important function was missing from the first set of Dynamic Array functions: some sort of append function to allow multiple ranges of data to be consolidated into a single Dynamic Array:

"Like the new chart types, the issues with Dynamic Arrays, probably fall into the categories of both deficiencies and missed opportunities. The inability of a Dynamic Array to be part of an Excel Table would appear to be a deficiency. One of the great advantages of Dynamic Arrays is the ability to automate processes that would previously have needed manual intervention. However, the inability to 'spill within a Table' makes it much more difficult to create a chart or a PivotTable that dynamically adjusts to take account of a change in the number of cells into which a dynamic array formula spills:

For the missed opportunity, an APPEND() function is noticeable by its absence from the initial batch of specific dynamic array functions. This would allow the dynamic creation of a single table from multiple tables, something that is currently only easily possible using Power Query with its need to refresh."

The new VSTACK() function does just this, combining the rows from multiple ranges of Excel cells by position into a single Dynamic Array. There is also a horizontal equivalent called, appropriately enough, HSTACK() which combines columns. In the following screen shot we can see both functions used to combine the three Excel Tables. The available arguments for VSTACK() and HSTACK() are simply up to 254 individual ranges:

As you can see, the stacking operations can lead to blank cells in the resulting array when there are varying numbers of columns (for VSTACK()) or rows (for HSTACK()) in the source arrays. These are indicated with #N/A errors. Several of the other new Dynamic Array functions introduced at the same time as VSTACK() and HSTACK() include arguments to allow you to replace the empty cells resulting from changing the size and shape of arrays with specified 'padding' characters. Perhaps strangely, VSTACK() and HSTACK() don't include equivalent arguments, so instead you will need to use one of the error checking functions, such as IFNA(), to replace #N/A errors in your output array. Here, we have just used "" as the second argument of IFNA() to replace #N/A with an empty text string:

=IFNA(VSTACK(Table8[#All],Table9,Table10),"")

While these new functions are a really useful addition to the capabilities of Dynamic Arrays, the introduction of VSTACK() has completely ruined my standard introductory demonstration of the importance of Power Query. I used to triumphantly show how Power Query was the only reasonably straightforward way to easily consolidate ranges in Excel dynamically. Not any more it isn't.