M365 Excel Desktop: Advanced Formulas and Dynamic Arrays

Level: Advanced

Duration: 1 day

Prerequisites: Strong formula and Table skills

Course Overview

Use modern functions to build maintainable lookup, filtering, reshaping, and reusable calculation solutions.

Learning Objectives

• Apply modern lookup patterns
• Create dynamic-array formulas
• Reshape and combine arrays
• Improve formulas with LET
• Audit complex formulas

Course Topics

Modern Lookups

• XLOOKUP exact, approximate, reverse, wildcard, and missing-value options
• Return multiple columns; nested and two-way lookups
• XMATCH; INDEX and MATCH for compatibility
• VLOOKUP as a legacy topic

Dynamic Arrays

• Spill behaviour and spill operator
• FILTER, SORT, SORTBY, UNIQUE, SEQUENCE, RANDARRAY
• Combined arrays, dynamic dropdown sources, and #SPILL! errors

Modern Array and Text Functions

• TEXTSPLIT, TEXTBEFORE, TEXTAFTER
• VSTACK, HSTACK, TAKE, DROP, CHOOSECOLS, CHOOSEROWS, TOCOL, TOROW, WRAPROWS, WRAPCOLS

Maintainable Formulas

• LET, named intermediate calculations, reduced repetition
• Trace Precedents and Dependents, Evaluate Formula, Watch Window

Optional Advanced Functions

• LAMBDA, MAP, BYROW, BYCOL, REDUCE, and SCAN