Microsoft Excel Functions & Formulas : Dynamic Array

Overview

Microsoft Excel Dynamic Array represents a significant advancement in Excel's formula capabilities, fundamentally changing how users interact with and process data. The new spilling feature of Excel enables more complex calculations and, in some ways, simplify it. We are now able to perform calculations using multiple values (array) and let the results (array) spilled to adjacent cells. This eliminates the need for copy and paste, and refresh (like with PivotTable and Power Query). Some new functions allow us to perform complex calculations with “row context” like DAX functions in Power Pivot. In other words, they allow us to perform previously impossible calculation and increased efficiency at the same time.

If you’re building dashboards, automating reports, or just want cleaner formulas, dynamic arrays are a must-know.

For example, the one formula below spilled the product list without duplication in cell below. The formula updated automatically spilled to additional cell when the product in the table changed.

Course Objectives

At the end of the course, participants will be able to use functions to:

> Create PivotTable-like report.

For example, summarize sales by product:

> Transform data structure.

For example, rearrange data structure to show staff according to shift:

> Clean up data.

For example, extract number from text:

> Perform calculations on array with “row context”.

Generate index numbers starting from 1 for each category:

Audience
This course is intended for participants who are experienced with functions and formulas in Excel 2019 and prior, and who wish to learn more about dynamic array, with the objectives of transforming, analysing and presenting data.

Prerequisites

• Basic knowledge of the Microsoft Windows operating system and its core functionality.
• Some knowledge of Excel functions and formulas.

Course Duration: 1 Day

    Time Schedule

• Class Starts : 9:00am
• Tea Break : 10:30am (15 minutes)
• Lunch Break : 12:30pm (1 hour)
• Tea Break : 3:30pm (15 minutes)
• Class Ends : 5:00pm

Course Outline

Module 1: Dynamic Spilled Array

This module looks at the Dynamic Spilled Array and its behaviour.

Lessons

• Building dynamic spilled array
• # operator
• Limitations
• Tips and tricks

After completing this module, participants will be able to:

• Build dynamic spilled array formula.
• Describe the limitations of Dynamic Spilled Array.

Module 2: Simplify Calculations and Create Custom Function

This module describes how to simplify formula and make it readable, as well as to create a custom function.

Functions

• LET, LAMBDA, etc.

Module 3: Transform Data Structure

This module looks at functions that can be used to transform data structure.

Functions

• CHOOSECOLS, CHOOSEROWS, WRAPCOLS, WRAPROWS, DROP, TAKE, EXPAND, TRIMRANGE, TOCOL, TOROW, HSTACK, VSTACK, etc.

Module 4: Create Pivot-Table-like Report

This module looks at how to summarize data in a Pivot-Table fashion.

Functions

• GROUPBY, PIVOTBY, etc.

Module 5: Clean Up Data

This module describes how to clean up data using Regex.

Functions

• REGEXEXTRACT, REGEXREPLACE, REGEXTEST, etc.

Module 6: Complex Calculations

This module looks at some examples of calculations involved row by row calculation within an array.

Functions

• MAP, SCAN, REDUCE, BYROW, BYCOL, etc.