*** Proof of Product ***
Exploring the Essential Features of “Leila Gharani – Master NEW Excel Functions in Office 365 & Office 2021 – Excel Dynamic Arrays”
Master NEW Excel Functions in Office 365 & Office 2021 – Excel Dynamic Arrays
Excel has Changed Forever! DON’T MISS OUT!
IMPORTANT: THESE FUNCTIONS ARE ONLY AVAILABLE IN OFFICE 365 & OFFICE 2021
BE THE FIRST to Unlock the Power of the NEW Functions in Excel
Excel’s NEW Calculation Engine has changed the way we use Excel.
We now have New Excel Functions like FILTER, SORT, UNIQUE, XLOOKUP, SEQUENCE & More!
This change impacts EVERYONE who uses Office 365 & Office 2021 – Beginner to Advanced Excel users.
With the new Excel Functions like FILTER, SORT, UNIQUE, XLOOKUP & SEQUENCE you get to Dramatically Simplify the Work you do in Excel.
Once you Learn to Maximize their Potential you can reduce spreadsheet development time by up to 90%!
Plus you get to create dynamic reports so you don’t need to refresh your results.
There are MULTIPLE ways you can apply the NEW Excel Formulas to YOUR Files.
Maximize the NEW Potential in Excel!
YOU’LL LEARN
- Create Excel Dashboard reports in MINUTES instead of HOURS
- Use Excel’s new FILTER Function to find multiple match results and solve Complex Lookup problems
- Create Pivot Table style reports, without using Pivot Tables – no need to press “refresh” this way
- Quickly create dynamic loan amortization reports in Excel or dynamic calendars
- Master the new UNIQUE, SORT & SORTBY Excel Functions and combine them with other formulas
- Use the brand new Excel RANDARRAY Function to randomly sort lists
- Use the new XLOOKUP function to simplify your lookup tasks (includes 5 practical examples with XLOOKUP)
- Use VLOOKUP, IF and SUMIFS together with Excel Dynamic Arrays formulas
- Compatibility & how older files behave in the new Excel and New Excel files in the old Excel
- Do Excel Dynamic Arrays make our spreadsheets faster or slower?
- Use dynamic array referencing as chart ranges in Excel
- Solve difficult Excel problems – such as split text and numbers from alphanumeric values & much more…
- Most importantly – how to think outside the box to solve any Excel challenges you come across
“I highly recommend this well-organized course for anyone whose work/projects involve data analysis. The knowledge I’ve gained on the new dynamic array functions will raise the level of my analysis while making my work easier at the same time. The new functions are well explained with business examples, and the course notes will be a great reference!”
– Jennifer Farry
“I give you a big 10 for the explanation of this course, I learned a lot from it.”
– Jos de Boer
“Very well organized and implemented course. It was easy to follow along yet challenging enough to learn. The way you break down the components of a complex formula, showing various examples of how they work, before building up to the complete solution is a great method of teaching. Your advice for spilling component results next to the source data is an excellent method for troubleshooting formula problems…if I could only get the hang of it and remember to do it!”
– Stephanie Perkins
Course Curriculum
Section 1: Before You Dive In
- Course Structure & Outline
- ❗Availability of Excel Dynamic Arrays
- Summary of Updates to Excel’s Calculation Engine
- 🔻DOWNLOAD Course Files HERE
- 🛣️ Course Outline for Quick Reference
Section 2: Dynamic Arrays & Spill / hash Behavior (Excel’s NEW Calculation Engine)
- Introduction & Glossary of Modern Excel Terms
- Formulas Spill & Other Dynamic Array Characteristics – Watch before diving in
- Excel FILTER Function (The New Excel Power Multi-Lookup)
- SORT & SORTBY Functions in Excel
- UNIQUE Function (Get a Unique List of Values in Excel)
- Practical Combination of UNIQUE, SORT & FILTER in Excel
- SEQUENCE Function in Excel
- RANDARRAY Function in Excel
- @ Prefix for Compatibility (in Excel Formulas)
- 💪 Challenge: Create an Excel Summary Report FAST
- Answer: Create an Excel Summary Report FAST
- XLOOKUP Basics – The New Power Lookup Formula in 2020
- XLOOKUP – 5 Practical Examples
- 🤔 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
- Backward & Forward Compatibility with Excel Dynamic Arrays
- Absolute & Mixed Cell Referencing – No Longer Needed in Excel?
- SUMIFS & COUNTIFS Improved! (More than one criteria in the same column)
- VLOOKUP with Excel Dynamic Arrays
- IF Function & Excel Dynamic Arrays
- Referencing Spilled Data Ranges in Other Excel Sheets
- TRANSPOSE & FREQUENCY Made Easier with Excel DA
- Do Excel Dynamic Arrays Make Spreadsheets Slow?
- 💪 Challenge: Data Analysis with Legacy Excel Formulas & Dynamic Arrays
- Answer: Data Analysis with Legacy Excel Formulas & Dynamic Arrays
- 📝 Conclusion: Impact of Dynamic Arrays on Legacy Excel Formulas
Section 4: How Excel Features are Impacted by Dynamic Arrays
- Introduction – DA Impact on Excel Features
- Data Validation List using Spill Range (and Excel Table)
- Excel Drop-down List (Excludes Blank Cells)
- Dependent Drop-down Excel Data Validation (Unique & Sorted)
- Conditional Formatting & Excel Dynamic Arrays
- Excel Charts with Dynamic Arrays
- 💪 Challenge: More Dynamic Dependent Data Validation Excel Report
- Answer: More Dynamic Dependent Data Validation Excel Report
- 🎁 Bonus: Interactive Excel Dashboards with Radio Buttons
- 📝 Conclusion: DA Impact on Excel Features
Section 5: Becoming Advanced in Excel Dynamic Arrays
- Congratulations! You Completed the Fundamentals!
- 💪 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
- Boolean Logic (TRUE & FALSE) in Excel Formulas Explained
- Excel FILTER to Exclude Blanks, Zero-Value Cells & Text
- FILTER Function for Multiple Criteria (AND Logical Test in Excel)
- FILTER Formula for Either Value (OR Logical Test in Excel)
- 💪 Challenge: Dynamic Array Filter Like a Pro
- Answer: Dynamic Array Filter Like a Pro
- 📝 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
- Note on Array Syntax for European & None-US Regional Settings
- Named Array Constants for Quick Report Setup in Excel
- FILTER Function with Array Constant for IF EMPTY Argument
- Top 3 Values with Excel LARGE Function
- Custom Dynamic Sort with Multiple Columns in Excel
- 🤔 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
- Easy Pivot-Table-Style Reports with Dynamic Arrays
- Pivot-Style Report with Row & Column Selection (INDEX MATCH & Dynamic Arrays)
- Interest Paid per Month & Year with Excel SEQUENCE Function
- Loan Amortization Table (Interest, Principal) with SEQUENCE in Excel
- Dynamic Excel Calendar with SEQUENCE
- Shrinking Data Validation – Hide Used Items in Excel
- Searchable Data Validation Drop-down List in Excel
- 💪 Challenge: Interactive KPI Selection Excel Report
- Answer: Interactive KPI Selection Excel Report
Section 9: Advanced Data Analysis in Excel
- Introduction – Advanced Data Analysis with Excel Dynamic Arrays
- Multiple Match Results Across Columns (Excel FILTER Function)
- Multiple Match Results in Single Cell (Excel TEXTJOIN)
- Lookup Approximate Match with Excel FILTER Function
- Find Top n Largest Categories & Values (FILTER) in Excel
- 💪 Challenge: Top 3 Salaries & Employees by Department
- Answer: Top 3 Salaries & Employees by Department
Section 10: Advanced to Expert: How Excel Arrays Really Work
- Introduction – How Arrays Work in Excel
- Lifting & Pairwise Lifting (Excel Arrays)
- Broadcasting Excel Array Behavior
- Implicit Intersection – The Main Legacy Excel Problem
- Are Excel CSE Formulas No Longer Needed?
- 🤔 Quiz: How Excel Arrays Work Under the Hood
Section 11: More Advanced Excel Dynamic Array Examples
- Introduction – More Advanced Excel Dynamic Array Examples
- Unique Values From Nonadjacent Columns in Excel
- FILTER for BOTH Criteria or EITHER Criteria (AND, OR Together in Excel)
- Split Text & Numbers From 1 Cell to 2 Columns in Excel
- 💪 Challenge: Filter Values From Non-Adjacent Columns in Excel
- Answer: Filter Values From Non-Adjacent Columns in Excel
Final Words & More Learning
- 🎆 Final Words
Your Instructor
Leila Gharani
My courses & tutorials are for ambitious beginners and professionals who want to upgrade their data analysis skills. If you hate doing things manually, you’ll love my approach.
I don’t just want to show you solutions to specific problems, but to teach you to find solutions to ALL your future data analysis problems.
On my YouTube channel, I share Office Productivity tips & tricks based on questions I get from our online community & professionals at my training sessions.
I am a Certified Microsoft Excel Expert and was given the Microsoft MVP award in 2017. I have over 15 years of experience implementing and training users on Management Information Systems of different sizes and nature – these include SAP BW and Oracle HFM.
My background is: Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert & Project Manager. My passion is teaching and solving difficult Excel problems. I am also addicted to learning and enjoy taking online courses on a variety of topics.
I founded my online learning company, XelPlus, in 2015. Our aim is to help you gain the knowledge you need so you can create useful tools, solve problems and get more done.
Let’s start now.
Who’s RIGHT for this course
- You HAVE OFFICE 365 & the NEW Dynamic Array formulas
- Business Professionals who use Excel to analyze data & create reports
- You currently use Excel and are familiar with SUM, IF & VLOOKUP functions
- You’d like to learn how to create interactive reports with dynamic array formulas
- You’d like a complete course to understand ALL there is to Excel’s NEW Calculation engine
Who’s NOT RIGHT for this course
- You are BRAND NEW to Excel
- You don’t have Office 365 INSIDER AND the new Dynamic array functions
- The course is taught for Excel on Windows. If you have a Mac, I wouldn’t be able to help you if you run into problems (I’ve never worked with a Mac).
Please see the full list of alternative group-buy courses available here: https://lunacourse.com/shop/
