Practical Combination of UNIQUE, SORT & FILTER in Excel

Master NEW Excel Functions in Office 365 & Office 2021 - Excel Dynamic Arrays

Section 1: Before You Dive In (0:07:23)

Section 2: Dynamic Arrays & Spill / hash Behavior (Excel's NEW Calculation Engine) (1:25:04)

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 (0:55:36)

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 (0:01:26)

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 (0:30:41)

Section 7: Excel Array Logic & Array Constants in Excel Formulas (0:21:09)

Section 8: Interactive Reports Made Easy with Excel Dynamic Arrays (1:19:01)

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 (0:27:19)

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 (0:15:27)

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 (0:29:43)

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)

🆕 Dynamic Array Functions Added in 2022 (0:43:18)

🎁 BONUS! Detailed Manual

Final Words & More Learning (0:01:35)

Lesson content locked

If you're already enrolled, you'll need to login.