Comprehensive Excel Dynamic Array Formula Lesson - The Power of Array Formulas

Overview

Dive into a comprehensive 40-minute video lesson on Excel's Dynamic Array Formulas and the new calculation engine. Explore over 30 topics, including OR logical tests, FREQUENCY function, spilled arrays, UNIQUE and FILTER functions, SORT operations, and dynamic PivotTables. Learn to leverage array formulas for efficient data analysis, create formula reports, and manipulate data with advanced techniques. Download the accompanying Excel file to practice hands-on and master the power of modern array formulas in Excel.

Syllabus

) Introduction to the new Excel Calculation Engine and Array Formulas in Excel.
) OR Logical Test AVERAGE Array Formula. Delivers a single Answer. No Ctrl + Shift + Enter.
) How Old Single Cell Array Formula Behaved.
) How New Calc Engine Avoids Trouble with Array Formulas.
) FREQUENCY Function.
) How Old FREQUENCY Function Worked.
) FREQUENCY Function. New Calc Engine. Spill Automatically.
) Where Spilled Array Formula Lives. Refer to Spilled Array with F40#.
) Spill Error.
) Standard Deviation. Delivers a single Answer. No Ctrl + Shift + Enter.
) OR Logical Test Adding formula with SUMIFS & SUM Function, rather than SUMPRODUCT.
) SUMIFS and Function Argument Array Operation will Spill.
) Array Formula to create a Formula Report that is EAISER than using a PivotTable.
) Unique List using UNIQUE Function.
) Distinct List using UNIQUE.
) Unique List and FILTER to avoid zeroes.
) Unique Count Formula using COUNTA, UNIQUE and OR.
) FILTER Function as Lookup with One Lookup Value & Return Multiple Items in Single Column.
) FILTER Function as Lookup with One Lookup Value & Return Multiple Items in Multiple Columns.
) FILTER Function as Lookup with Multiple Lookup Values & Return Multiple Items (Boolean Logical Test).
) #CALC! Error and the third argument in FILTER.
) SORT Function to sort a filtered list.
) Extract Sorted & Unique List from Mixed Data using SORT and UNIQUE.
) Extract Unique & Sorted List of Mixed Data in Single Cell using TEXTJOIN, SORT and UNIQUE.
) Spill Direct for Dynamic Arrays using Array Syntax.
) Variable Length Spilled Arrays using LARGE & SEQUENCE Function.
) SEQUENCY Function for incrementing Numbers in cells.
) SEQUENCY Function for incrementing Stepped Numbers in cells.
) MID, LEN and SEQUENCE to extract characters from a cell in a Row.
) MID, LEN, TRANSPOSE and SEQUENCE to extract characters from a cell in a Column.
) Dynamic PivotTable using SUMIFS and a number of new Array Functions.
) SINGLE Function and Implicit Intersection.
) Some Functions still will NOT make Array Calculations, like SUMIFS, COUNTIFS and AVERGAEIFS.
) RANDARRAY Function.
) Summary.