It’s been four years since Microsoft Excel introduced a set of functions for Excel 365 that promoted a new way of thinking about and writing formulas. Called dynamic array functions, they infused formulas with a radically new potency: for the first time, a single Excel formula could unleash results across multiple cells.

by which a series of consecutive values, 1 through 100, populated 100 consecutive cells plunging down a column.

identifies all the entries in a field – Salesperson – that features a salesperson’s name posted in cell B6, and returns all the records from a dataset called Sales bearing that name – even if the results number in the thousands. The formula count: one. The formula-writing potential: close to limitless.

And Excel hasn’t restricted dynamic array capability to its newest functions; in fact, most of its existing functions now sport multi-cell capability as well, a point that Up Up and Array! takes pains to stress.

Now while it’s true that Microsoft broke the news about dynamic arrays in September 2018, they may have caught up with your PC a good deal later than that. My set was installed – after having been stalled – in January 2020, for example. But no matter. If you’re an Excel 365 subscriber you’ve had dynamic array capability for some time now, and this book seeks to familiarize you with the functions and their vast promise for empowering and streamlining the formulas you write – and the formulas you didn’t realize you could write.

Excel has released its current complement of dynamic array functions in two waves: the first set of six – SEQUENCE, UNIQUE, SORT, SORTBY, FILTER, and RANDARRAY (along with the @ function you’ll use most sparingly, if at all; but there’s more about it in the book) dating from that 2018 inception point – and a later collection of 14 brought out earlier this year that continue to be rolled out to 365 users as of this writing.

But note that while the original six are likewise available to owners of Excel 2021, Microsoft has no current plan to implement the newer 14 to that version – at least as of now (remember that Excel 2021 offers itself as an as-is application, and can’t receive uploaded updates). One assumes that 2021 or its successor will acquire the new functions sooner or later, but who knows? By then it may be called Excel 2023.

Up Up and Array! commences by addressing the tricky definitional issue of the term array, and then proceeds to recount some of the defining features of array formulas, both past and present. It moves on to detail the new functions themselves, supplementing the exposition with numerous examples and illustrative screenshots. And you can click through those examples via the practice files stored here: https://github.com/Apress.

Up Up and Array! is a concise work devoted specifically to the workings of dynamic array functions and the new possibilities for formula writing they encourage. It’s my hope that as a result, you’ll come to appreciate, and perhaps even be inspired, by the decidedly cool things they – and you – can do.

Abbott Ira Katz