Leila Gharani – Master NEW Excel Functions in Office 365 & Office 2021 – Excel Dynamic Arrays

Leila Gharani – Master NEW Excel Functions in Office 365 & Office 2021 – Excel Dynamic ArraysUnlock The Power Of The New Excel FunctionsThe way we use Excel has changed.We now have many new Excel Functions like FILTER, SORT, UNIQUE, XLOOKUP, SEQUENCE & More!This change impacts everyone who uses Microsoft 365, Office 2021, or Excel for the web – Beginner to Advanced Excel users.With these new functions you get to dramatically simplify the work you do in Excel.By learning to maximize their potential, you can significantly reduce spreadsheet development time, potentially by up to 90%!Moreover, these skills allow you 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.In this Excel 365 Advanced Formulas course I’ll show you how to maximize the NEW Potential in Excel!Enroll in This Course & You’ll LearnCreate Excel Dashboard reports in MINUTES instead of HOURSUse Excel’s new FILTER Function to find multiple match results and solve Complex Lookup problemsCreate Pivot Table style reports, without using Pivot Tables – no need to press “refresh” this wayQuickly create dynamic loan amortization reports in Excel or dynamic calendarsMaster the new UNIQUE, SORT & SORTBY Excel Functions and combine them with other formulasUse the brand new Excel RANDARRAY Function to randomly sort listsUse 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 formulasCompatibility & how older files behave in the new Excel and New Excel files in the old ExcelDo Excel Dynamic Arrays make our spreadsheets faster or slower?Use dynamic array referencing as chart ranges in ExcelSolve 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 acrossHow is the Course Structured?The Excel 365 Advanced Functions course is designed to have an even balance between talking head explanations and screen-casts.Follow along with organized workbooks to practice as you learn.Example Downloadable FileAlong the way I will challenge you to apply what you learnt to solve a practical problem.Example ChallengeIn this comprehensive course, you’ll receive over 30 pages of detailed handouts. These materials are expertly designed to summarize the most crucial points, ensuring you have quick access to key information at your fingertips.Course Notes for Excel 365 Advanced Functions Course Ready to supercharge your Excel skills? This course will help you master the new set of functions introduced to Excel for Office 365.You’ll create professional-grade reports in a fraction of the time it used to take you.Your CurriculumYou can view the detailed curriculum below. Click on “See a full outline” button below to expand the list of lectures.See anything that jumps out? Which lesson do you think you’d begin with?Section 1: Before You Dive InCourse Structure & OutlineAvailability of Excel Dynamic ArraysSummary of Updates to Excel’s Calculation EngineDOWNLOAD Course Files HERESection 2: Dynamic Arrays & Spill (hash) Behavior (What you need to know)Introduction & Glossary of Modern Excel TermsFormulas Spill & Other Excel Dynamic Array CharacteristicsExcel FILTER Function (The New Excel Power Multi-Lookup)SORT & SORTBY Functions in ExcelUNIQUE Function (Get a Unique List of Values in Excel)Practical Combination of UNIQUE, SORT & FILTER in ExcelSEQUENCE Function in ExcelRANDARRAY Function in Excel@ Prefix for Compatibility (in Excel Formulas)Challenge: Create an Excel Summary Report FASTAnswer: Create an Excel Summary Report FASTQuiz: Test Your Excel Dynamic Array KnowledgeConclusion: Dynamic Array Rules RecapSection 3: How Legacy Formulas are Impacted by Excel’s NEW Calculation EngineIntroduction – DA impact on Excel FormulasBackward & Forward Compatibility with Excel Dynamic ArraysAbsolute & Mixed Cell Referencing – No Longer Needed in Excel?SUMIFS & COUNTIFS Improved! (More than one criteria in the same column)VLOOKUP with Excel Dynamic ArraysIF Function & Excel Dynamic ArraysReferencing Spilled Data Ranges in Other Excel SheetsTRANSPOSE & FREQUENCY Functions Made Easier with Excel DADo Excel Dynamic Arrays Make Spreadsheets Slow?Challenge: Data Analysis with Legacy Excel Formulas & Dynamic ArraysAnswer: Data Analysis with Legacy Excel Formulas & Dynamic ArraysConclusion: Impact of Dynamic Arrays on Legacy Excel FormulasSection 4: How Excel Features are Impacted by Dynamic ArraysIntroduction – DA Impact on Excel FeaturesData 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 ArraysExcel Charts with Dynamic ArraysChallenge: More Dynamic Dependent Data Validation Excel ReportAnswer: More Dynamic Dependent Data Validation Excel ReportConclusion: DA Impact on Excel FeaturesSection 5: Becoming Advanced in Excel Dynamic ArraysCongratulations! You Completed the Fundamentals!More Challenges & Practice ActivitiesQuiz: Challenge Successfully Completed?Challenge AnswersSection 6: The ONE Theory you NEED to Use Excel FILTER Like a ProWhy THIS Excel Theory is ImportantBoolean Logic (TRUE & FALSE) in Excel Formulas ExplainedExcel FILTER to Exclude Blanks, Zero-Value Cells & TextFILTER 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 ProAnswer: Dynamic Array Filter Like a ProConclusion: How to Filter Like a Pro in ExcelSection 7: Excel Array Logic & Array Constants in Excel FormulasDebugging Trick & Excel Array Syntax – Comma versus SemicolonNamed Array Constants for Quick Report Setup in ExcelFILTER Function with Array Constants for IF EMPTY ArgumentTop 3 Values with Excel LARGE FunctionCustom Dynamic Sort with Multiple Columns in ExcelQuiz: Working Correctly with Array Constants in ExcelConclusion: Use the Correct Array Syntax in Excel FormulasSection 8: Interactive Reports Made Easy with Excel Dynamic ArraysIntroduction – Interactive Excel Reports Made EasyEasy Pivot Table-Style Reports with Dynamic ArraysPivot Table-Style Report with Row & Column Selection (INDEX MATCH & Dynamic Arrays)Interest Paid per Month & Year with Excel SEQUENCE FunctionLoan Amortization Table (Interest, Principal) with SEQUENCE in ExcelDynamic Excel Calendar with SEQUENCEShrinking Data Validation – Hide Used Items in ExcelSearchable Data Validation Drop-down List in ExcelChallenge: Interactive KPI Selection Excel ReportAnswer: Interactive KPI Selection Excel ReportSection 9: Advanced Data Analysis in ExcelIntroduction – Advanced Data Analysis with Excel Dynamic ArraysMultiple Match Results Across Columns (Excel FILTER Function)Multiple Match Results in Single Cell (Excel TEXTJOIN)Lookup Approximate Match with Excel FILTER FunctionFind Top n Largest Categories & Values (FILTER) in ExcelChallenge: Top 3 Salaries & Employees by DepartmentAnswer: Top 3 Salaries & Employees by DepartmentSection 10: Advanced to Expert: How Excel Arrays Really WorkIntroduction – How Arrays Work in ExcelLifting & Pairwise Lifting (Excel Arrays)Broadcasting Excel Array BehaviorImplicit Intersection – The Main Legacy Excel ProblemAre Excel CSE Formulas No Longer Needed?Quiz: How Excel Arrays Work Under the HoodSection 11: More Advanced Excel Dynamic Array ExamplesIntroduction – More Advanced Excel Dynamic Array ExamplesUnique Values From Nonadjacent Columns in ExcelFILTER for BOTH Criteria or EITHER Criteria (AND, OR Together in Excel)Split Text & Numbers From 1 Cell to 2 Columns in ExcelChallenge: Filter Values From Non-Adjacent Columns in ExcelAnswer: Filter Values From Non-Adjacent Columns in ExcelSection 12: Final Words & More LearningFinal WordsI’m Leila.Nice to meet you.Each month, I hop on a special video call with the Microsoft team. This is one of the perks of being a Microsoft MVP. They fill us in on all the latest features coming down the pipeline.I love these calls and leave them as excited as a kid on their birthday.But I also ask myself this question: “How are everyday people going to learn this?”For years, I worked as a project manager at a multinational paper & packaging company. Excel was my #1 tool. Without it, I’d be lost. And let me tell you – I sure worked some magic at that company. Some of the Excel automations I had to create… wow, talk about beasts.But you know the saying: What doesn’t kill you makes you stronger. And safe to say I can now use Excel with the best of them.Who Is Right For This CourseYou’re in the right place if …You HAVE OFFICE 365 or 2021 with the NEW Excel functionsBusiness Professionals who use Excel to analyze data & create reportsYou currently use Excel and are familiar with SUM, IF & VLOOKUP functionsYou’d like to learn how to create interactive reports with dynamic array formulasYou’d like a complete course to understand ALL there is to Excel’s NEW Calculation engineWho’s Not Right For This CourseYou’re in the wrong place if …You are BRAND NEW to ExcelYou don’t have Office 365 or Office 2021 with the NEW Excel functionsThe 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).There are no reviews yet.Add a Review Cancel replyYou must be logged in to post a review.