Modern Data Analytics in Excel

Chapter 10. Introducing Dynamic Array Functions

Thus far, this book has covered building measures for Power Pivot with DAX and touched on M code in Power Query to a lesser extent. However, it hasn’t examined the traditional workbook formulas and functions that have long been the foundation of Excel. Even this area of the program, which may have seemed neglected in favor of other flashy developments like Power Pivot and Power Query, has undergone significant improvements, becoming more powerful and capable.

This chapter introduces dynamic array functions, shedding light on their capabilities. You will learn how to sort, filter, and join datasets, among other tasks, using the familiar environment of the Excel formula bar.

Formulas Versus Functions in Excel

In Excel, formulas and functions have distinct meanings. A formula is a mathematical expression that manipulates data using operators, cell references, and constants; for example, =B1 * B2. In contrast, a function is a predefined formula providing extensive data analysis and manipulation capabilities; for example, TRIMMEAN(B1:B3).

Formulas can include functions, blurring the distinction. This chapter aims to respect these as separate concepts, while recognizing their interdependence.

Dynamic Array Functions Explained

Dynamic array functions have impressive capabilities, and it’s tempting to start experimenting with them right away. However, it’s crucial to understand what makes these functions special and how they differ from ...