Autoplay
Autocomplete
HTML5
Flash
Player
Speed
Previous Lecture
Complete and continue
Excel Dynamic Arrays: Beginner to Expert (Office 365)
Section 1: Before You Dive In
Course Structure & Outline (3:11)
Availability of Excel Dynamic Arrays (0:52)
Summary of Updates to Excel's Calculation Engine (2:15)
DOWNLOAD Course Files HERE (1:05)
Course Outline for Quick Reference
Section 2: Dynamic Arrays & Spill / hash Behavior (Excel's NEW Calculation Engine)
Introduction & Glossary of Modern Excel Terms (2:11)
Formulas Spill & Other Dynamic Array Characteristics - Watch before diving in (7:40)
Excel FILTER Function (The New Excel Power Multi-Lookup) (9:27)
SORT & SORTBY Functions in Excel (9:42)
UNIQUE Function (Get a Unique List of Values in Excel) (7:26)
Practical Combination of UNIQUE, SORT & FILTER in Excel (6:47)
SEQUENCE Function in Excel (8:12)
RANDARRAY Function in Excel (7:03)
@ Prefix for Compatibility (in Excel Formulas) (4:47)
Challenge: Create an Excel Summary Report FAST
Answer: Create an Excel Summary Report FAST (3:14)
XLOOKUP Basics - The New Power Lookup Formula in 2020 (6:39)
XLOOKUP - 5 Practical Examples (11:56)
Quiz: Test Your Excel Dynamic Array Knowledge
Conclusion: Dynamic Array Rules Recap
Section 3: How Legacy Formulas are Impacted by Excel's NEW Calculation Engine
Introduction - DA impact on Excel Formulas (3:31)
Backward & Forward Compatibility with Excel Dynamic Arrays (10:06)
Absolute & Mixed Cell Referencing - No Longer Needed in Excel? (5:46)
SUMIFS & COUNTIFS Improved! (More than one criteria in the same column) (7:34)
VLOOKUP with Excel Dynamic Arrays (5:43)
IF Function & Excel Dynamic Arrays (4:09)
Referencing Spilled Data Ranges in Other Excel Sheets (6:35)
TRANSPOSE & FREQUENCY Made Easier with Excel DA (5:31)
Do Excel Dynamic Arrays Make Spreadsheets Slow? (5:56)
Challenge: Data Analysis with Legacy Excel Formulas & Dynamic Arrays
Answer: Data Analysis with Legacy Excel Formulas & Dynamic Arrays (8:30)
Conclusion: Impact of Dynamic Arrays on Legacy Excel Formulas (2:11)
Section 4: How Excel Features are Impacted by Dynamic Arrays
Introduction - DA Impact on Excel Features (1:11)
Data Validation List using Spill Range (and Excel Table) (5:53)
Excel Drop-down List (Excludes Blank Cells) (5:07)
Dependent Drop-down Excel Data Validation (Unique & Sorted) (4:49)
Conditional Formatting & Excel Dynamic Arrays (10:57)
Excel Charts with Dynamic Arrays (7:46)
Challenge: More Dynamic Dependent Data Validation Excel Report
Answer: More Dynamic Dependent Data Validation Excel Report (5:11)
Conclusion: DA Impact on Excel Features (1:27)
Section 5: Becoming Advanced in Excel Dynamic Arrays
Congratulations! You Completed the Fundamentals! (1:26)
More Challenges & Practice Activities
Quiz: Challenge Successfully Completed?
Challenge Answers
Section 6: The ONE Theory you NEED to Use Excel FILTER Like a Pro
Why THIS Excel Theory is Important (1:37)
Boolean Logic (TRUE & FALSE) in Excel Formulas Explained (4:29)
Excel FILTER to Exclude Blanks, Zero-Value Cells & Text (5:54)
FILTER Function for Multiple Criteria (AND Logical Test in Excel) (7:33)
FILTER Formula for Either Value (OR Logical Test in Excel) (3:18)
Challenge: Dynamic Array Filter Like a Pro
Answer: Dynamic Array Filter Like a Pro (7:50)
Conclusion: How to Filter Like a Pro in Excel
Section 7: Excel Array Logic & Array Constants in Excel Formulas
Debugging Trick & Excel Array Syntax - Comma versus Semicolon (7:33)
Named Array Constants for Quick Report Setup in Excel (2:32)
FILTER Function with Array Constant for IF EMPTY Argument (2:51)
Top 3 Values with Excel LARGE Function (4:43)
Custom Dynamic Sort with Multiple Columns in Excel (3:30)
Quiz: Working Correctly with Array Constants in Excel
Conclusion: Use the Correct Array Syntax in Excel Formulas
Section 8: Interactive Reports Made Easy with Excel Dynamic Arrays
Introduction - Interactive Excel Reports Made Easy (0:45)
Easy Pivot-Table-Style Reports with Dynamic Arrays (12:28)
Pivot-Style Report with Row & Column Selection (INDEX MATCH & Dynamic Arrays) (13:29)
Interest Paid per Month & Year with Excel SEQUENCE Function (8:47)
Loan Amortization Table (Interest, Principal) with SEQUENCE in Excel (7:05)
Dynamic Excel Calendar with SEQUENCE (11:44)
Shrinking Data Validation - Hide Used Items in Excel (7:50)
Searchable Data Validation Drop-down List in Excel (9:07)
Challenge: Interactive KPI Selection Excel Report
Answer: Interactive KPI Selection Excel Report (7:46)
Section 9: Advanced Data Analysis in Excel
Introduction - Advanced Data Analysis with Excel Dynamic Arrays (1:04)
Multiple Match Results Across Columns (Excel FILTER Function) (3:18)
Multiple Match Results in Single Cell (Excel TEXTJOIN) (3:54)
Lookup Approximate Match with Excel FILTER Function (6:17)
Find Top n Largest Categories & Values (FILTER) in Excel (5:45)
Challenge: Top 3 Salaries & Employees by Department
Answer: Top 3 Salaries & Employees by Department (7:01)
Section 10: Advanced to Expert: How Excel Arrays Really Work
Introduction - How Arrays Work in Excel (1:22)
Lifting & Pairwise Lifting (Excel Arrays) (5:20)
Broadcasting Excel Array Behavior (4:57)
Implicit Intersection - The Main Legacy Excel Problem (1:55)
Are Excel CSE Formulas No Longer Needed? (1:51)
Quiz: How Excel Arrays Work Under the Hood
Section 11: More Advanced Excel Dynamic Array Examples
Introduction - More Advanced Excel Dynamic Array Examples (1:18)
Unique Values From Nonadjacent Columns in Excel (6:24)
FILTER for BOTH Criteria or EITHER Criteria (AND, OR Together in Excel) (8:20)
Split Text & Numbers From 1 Cell to 2 Columns in Excel (9:50)
Challenge: Filter Values From Non-Adjacent Columns in Excel
Answer: Filter Values From Non-Adjacent Columns in Excel (3:51)
BONUS! Detailed Manual
DOWNLOAD the Dynamic Arrays Manual
Final Words & More Learning
Final Words (1:35)
Leave a Review
Share Your Success with Me!
More Challenges & Practice Activities
Lecture content locked
If you're already enrolled,
you'll need to login
.
Enroll in Course to Unlock