Autoplay
Autocomplete
Previous Lesson
Complete and Continue
Master NEW Excel Functions in Office 365 & Office 2021 - Excel Dynamic Arrays
Section 1: Before You Dive In
Course Structure & Outline (3:11)
⚙️How to Use the Platform for Best Learning Experience
❗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 (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 (1:07:51)
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) (9:53)
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)
🎁 Bonus: Interactive Excel Dashboards with Radio Buttons (13:15)
📝 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
🆕 Enjoying the Course so far? Refer a friend and get 30% off your next XelPlus purchase!
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)
Note on Array Syntax for European & Non-US Regional Settings
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:30)
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)
🆕 Recently Added Dynamic Array Functions
Overview of the New Text and Array Manipulation Functions (with practice file)
Append Data from Multiple Sheets with VSTACK and 3D References
Master the TEXTSPLIT Function (cool tricks included!)
TEXTBEFORE & TEXTAFTER for a Complex Scenario
TAKE Function - Real-World Examples
GROUPBY and PIVOTBY for Pivot Table-Like Reports in One Dynamic Array Formula
🎁 BONUS! Detailed Manual
🔻DOWNLOAD the Dynamic Arrays Manual
Final Words & More Learning (0:01:35)
🎆 Final Words (1:35)
✍️ Leave a Review
My Favorite Resources
Access to Bonus Items
🏅 Share Your Digital Certificate (0:40)
Searchable Data Validation Drop-down List in Excel
Load more
Load more