Formula Magic with Dynamic Arrays in Excel 365

Excel has changed… like seriously, changed.  Every time we used Excel in the past, we accepted a simple operating rule; one formula, one cell.  Even with advanced formulas, it was still necessary to have one cell for each calculation.  But this has changed; Excel now allows a single formula to fill multiple cells.  This is possible because of Excel’s new calculation engine, which allows dynamic arrays.

The term ‘dynamic arrays’ sounds complicated, but once you understand it, you’ll appreciate the simplicity and power.  Formula Magic with Dynamic Arrays will teach you the key concepts so that you can understand dynamic arrays and start creating formulas that were previously considered to be impossible.

Buy now – $12 + Sales Tax

Download the support files:

If you have already purchased the book, you can download the support files by clicking the link below:

Download support files

Outline

Understanding dynamic arrays
Provides a solid introduction to dynamic arrays, including some new key features: spilling, # references, and @ implicit intersection.

The UNIQUE function
Introduction to the UNIQUE function, using 5 examples to cover key scenarios.

The SORT function
Introduction to the SORT function, using 5 examples to explain the key concepts.  Examples include how to return top n values and how to sort on multiple columns.

The SORTBY function
Introduction to the SORTBY function using 6 examples.  Examples demonstrate how to select specific columns from the data set and the dangers of mismatching arrays.

The FILTER function
Introduction to the FILTER function using 4 examples.  Examples demonstrate how to use multiple criteria, and how to handle the new #CALC! error.

The SEQUENCE function
Introduction to the SEQUENCE function using 5 examples.  Including how to use SEQUENCE as an enabler for other functions.

The RANDARRAY function
Introduction to the RANDARRAY function, using 4 examples.  Examples demonstrate how it can replace the RAND and RANDBETWEEN functions.

Using dynamic arrays with other features
Learn how dynamic arrays work with Tables, Name Manager, charts, linked pictures, PivotTables, conditional formatting and data validation.

Advanced formula techniques
Learn single vs. cascading formula methodologies, useful supporting functions (CHOOSE, INDEX and SEQUENCE) and using # references with the range operator.


Buy now – $12 + Sales Tax