Free Download Master NEW Excel Functions in Office 365 & Office 2021
Last updated 2/2024
MP4 | Video: h264, 1920x1080 | Audio: AAC, 44.1 KHz
Language: English | Size: 3.63 GB | Duration: 7h 8m
Learn to Use Excel's NEW Functions (FILTER, UNIQUE, SORT, XLOOKUP...) to Dramatically Simplify the Work You Do in Excel.
What you'll learn
Master Excel's NEW Formulas (FILTER, UNIQUE, XLOOKUP, SORT, SORTBY, SEQUENCE, RANDARRAY & More Dynamic Array Functions)
Learn how to think outside the box to solve ANY Excel challenge you come across
Be the FIRST to unlock the power of Microsoft Excel's BRAND-NEW calculation engine
Add INTERACTIVITY to your Excel reports as NEVER seen before (Create Pivot Table style reports, without using Pivot Tables)
Maximize the NEW potential in Excel and learn how to apply the new DYNAMIC ARRAYS Formulas to make YOUR files better
Get lifetime access to a proven and State-Of-The-Art Excel course including challenges, quizzes & exercises
Requirements
You HAVE Microsoft 365 & the NEW Excel Dynamic Array formulas (see the 3rd Preview lecture)
You currently use Excel and are familiar with SUM, IF & VLOOKUP functions
MAC USERS: Excel Dynamic arrays are also available in Excel for Microsoft 365 for Mac. Please note: 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.
Description
Data analysis in Excel for Microsoft 365 has become simpler than ever. We have NEW Excel functions like XLOOKUP, FILTER, SORT, UNIQUE, SEQUENCE and MORE!PLUS: Microsoft has changed Excel's calculation engine.This change impacts EVERYONE who uses Microsoft 365 version: Beginner to Advanced Excel users.There are MULTIPLE ways you can apply Dynamic Arrays and the NEW Excel Functions to YOUR Files. This course shows you how to MAXIMIZE the NEW Potential in Excel 365!For example:Use Excel's new FILTER Function to find multiple match resultsUse Excel's new XLOOKUP function for complex lookups (no more VLOOKUP or Index Match)Create Excel Dashboard reports in MINUTES instead of HOURSCreate 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 FunctionsUse the brand new Excel RANDARRAY Function to randomly sort listsUse VLOOKUP, IF and SUMIFS together with Excel Dynamic Arrays formulasIn addition you'll learn:How to add Interactivity to your Excel reports as never done beforeHow to use the new Dynamic Arrays formulas in your filesHow Excel's calculation engine has changed and how this impacts all existing formulasCompatibility & how older files behave in the new Excel and New Excel files in the old ExcelDo Dynamic Arrays make our spreadsheets faster or slower?Use Dynamic Array referencing as Excel chart rangesSolve difficult problems - such as split text and numbers from alphanumeric values & much more...Most importantly - how to think outside the box to solve any challenges you come acrossBut First:*** HOW TO CHECK IF YOUR EXCEL VERSION HAS DYNAMIC ARRAYS ***Do you have Excel for Microsoft 365? Then you already have dynamic arrays and the new Excel functions. Check out the free preview video called "Do you have Dynamic Arrays?" and do the test outline in the video.If you don't have dynamic arrays BUT you have Excel for Microsoft 365 run the latest update to install the new features.But please note: Excel dynamic arrays will NOT be a part of Excel 2010, Excel 2013, Excel 2016 or EVEN Excel 2019. Only in Excel 365 or Excel on the web.*** WHY TAKE THIS SPECIFIC EXCEL COURSE? ***Excel Dynamic Arrays are brand-new. Complex tasks will now be done with SIMPLE Excel formulas. Everything you wished Excel could do, can now be done!In this course you'll solve dozens of practical real-world examples. They'll help you think outside the box so you can work smarter not harder. In the course you will get: Excel Practice & Solutions Workbooks for each section Course notes outlining the MOST IMPORTANT Concepts Quiz questions to test your understanding*** WHY WOULD YOU CHOOSE TO LEARN EXCEL? ***Excel can do so much for your career. It's just one program but it's the one hiring managers are interested in.That's why basic Excel skills is a mandatory requirement for most office-based professionals today.Superior Microsoft Excel skills can get you a promotion. Some jobs require that to begin with.*** WHICH VERSION OF EXCEL IS USED IN THE COURSE? ***The course is applicable ONLY for Excel for Microsoft 365 (Also available for Excel on the Web). Dynamic Arrays will NOT be part of Excel 2019 perpetual license.*** Feedback from our students ***★★★★★ "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★★★★★ "This course is magical. I am sure everyone using Excel will have to upgrade their knowledge soon. Thanks for the enlightening journey." Dheeraj★★★★★ "Very well organized and implemented course. It was easy to follow along yet challenging enough to learn. As an example, I had never used the CHOOSE function which was your solution in one of the advanced challenges. Still wanting to try and figure out the solution on my own (without seeing yours first), I tried watching a video from another YouTube instructor on that specific function. Unfortunately, I didn't really get it until I watched your instruction. 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★★★★★ "I give you a big 10 for the explanation of this course, I learned a lot from it." JosThere's a 100% money back guarantee. You have nothing to lose. Check out the lectures inside and see if you can apply the techniques to your work.The course comes with lifetime access. Buy now. Watch anytime.
Overview
Section 1: Before You Dive In
Lecture 1 Course Structure & Outline
Lecture 2 Do You Have Excel Dynamic Arrays? DO THIS TEST!
Lecture 3 Availability of Dynamic Arrays & NEW EXCEL Functions (FILTER, XLOOKUP, SORT...)
Lecture 4 Summary of Updates to Excel's Calculation Engine
Lecture 5 DOWNLOAD Course Files HERE
Lecture 6 Course Outline for Quick Reference
Section 2: Dynamic Arrays & Spill (hash) Behavior (What you need to know)
Lecture 7 Introduction & Glossary of Modern Excel Terms
Lecture 8 Formulas Spill & Other Excel Dynamic Array Characteristics
Lecture 9 Excel FILTER Function (The New Excel Power Multi-Lookup)
Lecture 10 SORT & SORTBY Functions in Excel
Lecture 11 UNIQUE Function (Get a Unique List of Values in Excel)
Lecture 12 Practical Combination of UNIQUE, SORT & FILTER in Excel
Lecture 13 SEQUENCE Function in Excel
Lecture 14 RANDARRAY Function in Excel
Lecture 15 '@ Prefix for Compatibility (in Excel Formulas)
Lecture 16 Challenge: Create an Excel Summary Report FAST
Lecture 17 Answer: Create an Excel Summary Report FAST
Lecture 18 NEW XLOOKUP in 2020 for Excel Office 365
Lecture 19 XLOOKUP Basics - The New Power Lookup Formula in 2020
Lecture 20 XLOOKUP - 5 Practical Examples
Lecture 21 Conclusion: Dynamic Array Rules Recap
Section 3: How Legacy Formulas are Impacted by Excel's NEW Calculation Engine
Lecture 22 Introduction - DA impact on Excel Formulas
Lecture 23 Backward & Forward Compatibility with Excel Dynamic Arrays
Lecture 24 Absolute & Mixed Cell Referencing - No Longer Needed in Excel?
Lecture 25 SUMIFS & COUNTIFS Improved! (More than one criteria in the same column)
Lecture 26 VLOOKUP with Excel Dynamic Arrays
Lecture 27 IF Function & Excel Dynamic Arrays
Lecture 28 Referencing Spilled Data Ranges in Other Excel Sheets
Lecture 29 TRANSPOSE & FREQUENCY Functions Made Easier with Excel DA
Lecture 30 Do Excel Dynamic Arrays Make Spreadsheets Slow?
Lecture 31 Challenge: Data Analysis with Legacy Excel Formulas & Dynamic Arrays
Lecture 32 Answer: Data Analysis with Legacy Excel Formulas & Dynamic Arrays
Lecture 33 Conclusion: Impact of Dynamic Arrays on Legacy Excel Formulas
Section 4: How Excel Features are Impacted by Dynamic Arrays
Lecture 34 Introduction - DA Impact on Excel Features
Lecture 35 Data Validation List using Spill Range (and Excel Table)
Lecture 36 Excel Drop-down List (Excludes Blank Cells)
Lecture 37 Dependent Drop-down Excel Data Validation (Unique & Sorted)
Lecture 38 Conditional Formatting & Excel Dynamic Arrays
Lecture 39 Excel Charts with Dynamic Arrays
Lecture 40 Challenge: More Dynamic Dependent Data Validation Excel Report
Lecture 41 Answer: More Dynamic Dependent Data Validation Excel Report
Lecture 42 Bonus: Interactive Excel Dashboards with Radio Buttons
Lecture 43 Conclusion: DA Impact on Excel Features
Section 5: Becoming Advanced in Excel Dynamic Arrays
Lecture 44 Congratulations! You Completed the Fundamentals!
Lecture 45 More Challenges & Practice Activities
Lecture 46 Challenge Answers
Section 6: THE ONE Theory you NEED to Use Excel FILTER Function Like a Pro
Lecture 47 Why THIS Excel Theory is Important
Lecture 48 Boolean Logic (TRUE & FALSE) in Excel Formulas Explained
Lecture 49 Excel FILTER to Exclude Blanks, Zero-Value Cells & Text
Lecture 50 FILTER Function for Multiple Criteria (AND Logical Test in Excel)
Lecture 51 FILTER Formula for Either Value (OR Logical Test in Excel)
Lecture 52 Challenge: Dynamic Array Filter Like a Pro
Lecture 53 Answer: Dynamic Array Filter Like a Pro
Lecture 54 Conclusion: How to Filter Like a Pro in Excel
Section 7: Excel Array Logic & Array Constants in Excel Formulas
Lecture 55 Debugging Trick & Excel Array Syntax - Comma versus Semicolon
Lecture 56 Note on Array Syntax for European & None-US Regional Settings
Lecture 57 Named Array Constants for Quick Report Setup in Excel
Lecture 58 FILTER Function with Array Constants for IF EMPTY Argument
Lecture 59 Top 3 Values with Excel LARGE Function
Lecture 60 Custom Dynamic Sort with Multiple Columns in Excel
Lecture 61 Conclusion: Use the Correct Array Syntax in Excel Formulas
Section 8: Interactive Reports Made Easy with Excel Dynamic Arrays
Lecture 62 Introduction - Interactive Excel Reports Made Easy
Lecture 63 Easy Pivot Table-Style Reports with Dynamic Arrays
Lecture 64 Pivot Table (Style) Report with Row & Column Selection (INDEX MATCH & DAs)
Lecture 65 Interest Paid per Month & Year with Excel SEQUENCE Function
Lecture 66 Loan Amortization Table in Excel (Interest, Principal) with SEQUENCE
Lecture 67 Dynamic Excel Calendar with SEQUENCE
Lecture 68 Shrinking Data Validation - Hide Used Items in Excel
Lecture 69 Searchable Data Validation Drop-down List in Excel
Lecture 70 Challenge: Interactive KPI Selection Excel Report
Lecture 71 Answer: Interactive KPI Selection Excel Report
Section 9: Advanced Data Analysis in Excel
Lecture 72 Introduction - Advanced Data Analysis with Excel Dynamic Arrays
Lecture 73 Multiple Match Results Across Columns (Excel FILTER Function)
Lecture 74 Multiple Match Results in Single Cell (Excel TEXTJOIN)
Lecture 75 Lookup Approximate Match with Excel FILTER Function
Lecture 76 Find Top n Largest Categories & Values (FILTER) in Excel
Lecture 77 Challenge: Top 3 Salaries & Employees by Department
Lecture 78 Answer: Top 3 Salaries & Employees by Department
Section 10: Advanced to Expert: How Excel Arrays Really Work
Lecture 79 Introduction - How Arrays Work in Excel
Lecture 80 Lifting & Pairwise Lifting (Excel Arrays)
Lecture 81 Broadcasting Excel Array Behavior
Lecture 82 Implicit Intersection - The Main Legacy Excel Problem
Lecture 83 Are Excel CSE Formulas No Longer Needed?
Section 11: More Advanced Excel Dynamic Array Examples
Lecture 84 Introduction - More Advanced Excel Dynamic Array Examples
Lecture 85 Unique Values From Nonadjacent Columns in Excel
Lecture 86 FILTER for BOTH Criteria or EITHER Criteria (AND, OR Together in Excel)
Lecture 87 Split Text & Numbers From 1 Cell to 2 Columns in Excel
Lecture 88 Challenge: Filter Values From Non-Adjacent Columns in Excel
Lecture 89 Answer: Filter Values From Non-Adjacent Columns in Excel
Section 12: Final Words & More Learning
Lecture 90 Final Words
Lecture 91 SPECIAL BONUS: Discover Exciting Offers!
Business Professionals who use Excel to analyze data & create reports,You'd like a complete course to understand ALL there is to Excel's NEW Calculation engine,You'd like to learn how to create interactive reports with Excel dynamic array formulas
Homepage
Code:
Bitte
Anmelden
oder
Registrieren
um Code Inhalt zu sehen!
Recommend Download Link Hight Speed | Please Say Thanks Keep Topic Live
Code:
Bitte
Anmelden
oder
Registrieren
um Code Inhalt zu sehen!
