Complete Guide to Next Gen Excel Formulas for Data Analysts by Microsoft Press

Course details

Over the years, Microsoft Excel has evolved from a simple spreadsheet tool into an extremely powerful calculation engine. In this course, instructor Alan Murray helps you learn how to use modern features like dynamic arrays, LET, and LAMBDA to write cleaner, more efficient formulas for data analysis. Build a foundation with Excel tables and structured references, then explore functions like FILTER, SORT, and UNIQUE for dynamic results. The course also covers data-shaping tools like CHOOSECOLS and XMATCH, combining datasets with TRIMRANGE and VSTACK, and iterative functions—BYROW, MAP, and SCAN—for row-wise calculations without helper columns. You'll finish by learning how to use LET for reusable formulas, GROUPBY and PIVOTBY for single-cell reporting, and LAMBDA to build custom functions.

Concepts

Introduction

Welcome to the course

Build Structured Models with Excel Tables

Learning objectives
Tables - The foundation of dynamic models
Create and manage Excel tables
Write smarter formulas with structured references
Reference table data like a pro
Filter faster with Slicers
Exercise - Build and analyze with tables

Master Dynamic Arrays

Learning objectives
Explore the concept of dynamic array formulas
Manage spill ranges effectively
Build solutions with dynamic array formulas
Filter data dynamically with the FILTER function
Sort data in arrays
Lookup values with XLOOKUP
Return arrays using XLOOKUP
Exercise - Extract, sort, and return

Shape and Slice Arrays

Learning objectives
Use CHOOSE to specify columns in an array
Extract columns with CHOOSECOLS
Retrieve columns dynamically with MATCH
Target rows dynamically
Explore XMATCH for next-level matching
TAKE what you need and DROP what you don t
Exercise - Pick the right pieces

Trim and Stack Ranges

Learning objectives
Apply the TRIMRANGE function
Use the dot operator in formulas
Stack Tables and trimmed ranges vertically
Combine data with HSTACK
Exercise - Clean and combine ranges

Perform Iterative Calculations with BYROW, MAP, and SCAN

Learning objectives
Aggregate values by column
Make calculations dynamic with the dot operator
Iterate down a column using BYROW
Apply the MAP function
Choose when to use BYROW BYCOL or MAP
Format spill ranges effectively
Generate running totals with SCAN
REDUCE iterations to a single result
Exercise - Iterate and calculate

Optimize Formulas with LET and Names

Learning objectives
Recognize why LET is so useful
Simplify a formula with the LET function
Apply good practices for names
Extend LET to the next level
Troubleshoot complex formulas with LET
Understand why to name a formula
Reuse calculations by naming a formula
Avoid pitfalls with named formulas
Exercise - Simplify with LET and names

Create Single-Cell Reports with GROUPBY and PIVOTBY

Learning objectives
Summarize data with GROUPBY
Build formula-based PivotTables with PIVOTBY
Compare GROUPBY and PIVOTBY
Add subtotals to your reports
Format GROUPBY and PIVOTBY results
Stack multiple aggregation functions
Filter report results dynamically
Use Slicers with GROUPBY and PIVOTBY
Exercise - Build a formula-driven report

Create Custom Functions with LAMBDA

Learning objectives
Explore the LAMBDA function with an example
Convert a formula into a custom LAMBDA
Save and manage named LAMBDA functions
Use your custom LAMBDA function
Modify an existing LAMBDA
Share LAMBDA functions across workbooks
Exercise - Create and reuse your own function

Conclusion

Course summary and next steps