Udemy
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
Development
Web Development Data Science Mobile Development Programming Languages Game Development Database Design & Development Software Testing Software Engineering Development Tools No-Code Development
Business
Entrepreneurship Communications Management Sales Business Strategy Operations Project Management Business Law Business Analytics & Intelligence Human Resources Industry E-Commerce Media Real Estate Other Business
Finance & Accounting
Accounting & Bookkeeping Compliance Cryptocurrency & Blockchain Economics Finance Finance Cert & Exam Prep Financial Modeling & Analysis Investing & Trading Money Management Tools Taxes Other Finance & Accounting
IT & Software
IT Certification Network & Security Hardware Operating Systems Other IT & Software
Office Productivity
Microsoft Apple Google SAP Oracle Other Office Productivity
Personal Development
Personal Transformation Personal Productivity Leadership Career Development Parenting & Relationships Happiness Esoteric Practices Religion & Spirituality Personal Brand Building Creativity Influence Self Esteem & Confidence Stress Management Memory & Study Skills Motivation Other Personal Development
Design
Web Design Graphic Design & Illustration Design Tools User Experience Design Game Design Design Thinking 3D & Animation Fashion Design Architectural Design Interior Design Other Design
Marketing
Digital Marketing Search Engine Optimization Social Media Marketing Branding Marketing Fundamentals Marketing Analytics & Automation Public Relations Advertising Video & Mobile Marketing Content Marketing Growth Hacking Affiliate Marketing Product Marketing Other Marketing
Lifestyle
Arts & Crafts Beauty & Makeup Esoteric Practices Food & Beverage Gaming Home Improvement Pet Care & Training Travel Other Lifestyle
Photography & Video
Digital Photography Photography Portrait Photography Photography Tools Commercial Photography Video Design Other Photography & Video
Health & Fitness
Fitness General Health Sports Nutrition Yoga Mental Health Dieting Self Defense Safety & First Aid Dance Meditation Other Health & Fitness
Music
Instruments Music Production Music Fundamentals Vocal Music Techniques Music Software Other Music
Teaching & Academics
Engineering Humanities Math Science Online Education Social Science Language Teacher Training Test Prep Other Teaching & Academics
AWS Certification Microsoft Certification AWS Certified Solutions Architect - Associate AWS Certified Cloud Practitioner CompTIA A+ Cisco CCNA Amazon AWS CompTIA Security+ AWS Certified Developer - Associate
Graphic Design Photoshop Adobe Illustrator Drawing Digital Painting InDesign Character Design Canva Figure Drawing
Life Coach Training Neuro-Linguistic Programming Mindfulness Personal Development Meditation Personal Transformation Life Purpose Emotional Intelligence Neuroscience
Web Development JavaScript React CSS Angular PHP WordPress Node.Js Python
Google Flutter Android Development iOS Development Swift React Native Dart Programming Language Mobile Development Kotlin SwiftUI
Digital Marketing Google Ads (Adwords) Social Media Marketing Google Ads (AdWords) Certification Marketing Strategy Internet Marketing YouTube Marketing Email Marketing Google Analytics
SQL Microsoft Power BI Tableau Business Analysis Business Intelligence MySQL Data Modeling Data Analysis Big Data
Business Fundamentals Entrepreneurship Fundamentals Business Strategy Online Business Business Plan Startup Freelancing Blogging Home Business
Unity Game Development Fundamentals Unreal Engine C# 3D Game Development C++ 2D Game Development Unreal Engine Blueprints Blender
2021-01-13 14:19:38
30-Day Money-Back Guarantee
Office Productivity Microsoft Excel Formulas and Functions

Master Excel Functions in Office 365 - Excel Dynamic Arrays

Learn to Use Excel's NEW Functions (FILTER, UNIQUE, SORT, XLOOKUP...) to Dramatically Simplify the Work You Do in Excel.
Highest Rated
Rating: 4.8 out of 54.8 (1,090 ratings)
7,061 students
Created by Leila Gharani
Last updated 2/2021
English
English
30-Day Money-Back Guarantee

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
Curated for the Udemy for Business collection

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 results

  • Use Excel's new XLOOKUP function for complex lookups (no more VLOOKUP or Index Match)

  • Create Excel Dashboard reports in MINUTES instead of HOURS

  • 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

  • Use the brand new Excel RANDARRAY Function to randomly sort lists

  • Use VLOOKUP, IF and SUMIFS together with Excel Dynamic Arrays formulas

In addition you'll learn:

  • How to add Interactivity to your Excel reports as never done before

  • How to use the new Dynamic Arrays formulas in your files

  • How Excel's calculation engine has changed and how this impacts all existing formulas

  • Compatibility & how older files behave in the new Excel and New Excel files in the old Excel

  • Do Dynamic Arrays make our spreadsheets faster or slower?

  • Use Dynamic Array referencing as Excel chart ranges

  • Solve 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 across

But 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.” Jos


There'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.


Who this course is for:

  • 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

Featured review

Joe Smith
Joe Smith
161 courses
49 reviews
Rating: 5.0 out of 5a year ago
One word - Unreal!! very good info with a superb trainer, This is my second course by Leila, and I have one more to complete. I will definitely complete all the courses she has on offer. If you want to learn Excel these are the cources for you. Whats also good is her Youtube channel, and web site with all the infomation you need if you dont get something.

Course content

12 sections • 89 lectures • 6h 54m total length

  • Preview03:11
  • Preview00:52
  • Preview00:16
  • Summary of Updates to Excel's Calculation Engine
    02:15
  • DOWNLOAD Course Files HERE
    01:11
  • Course Outline for Quick Reference
    00:13

  • Introduction & Glossary of Modern Excel Terms
    02:11
  • Formulas Spill & Other Excel Dynamic Array Characteristics
    07:40
  • Preview09:27
  • SORT & SORTBY Functions in Excel
    09:42
  • UNIQUE Function (Get a Unique List of Values in Excel)
    07:26
  • Practical Combination of UNIQUE, SORT & FILTER in Excel
    06:47
  • SEQUENCE Function in Excel
    08:12
  • RANDARRAY Function in Excel
    07:03
  • '@ Prefix for Compatibility (in Excel Formulas)
    04:47
  • Preview00:19
  • Answer: Create an Excel Summary Report FAST
    03:14
  • Preview00:35
  • XLOOKUP Basics - The New Power Lookup Formula in 2020
    06:39
  • XLOOKUP - 5 Practical Examples
    11:56
  • Quiz: Test Your Excel Dynamic Array Knowledge
    4 questions
  • Conclusion: Dynamic Array Rules Recap
    00:48

  • Introduction - DA impact on Excel Formulas
    03:31
  • Backward & Forward Compatibility with Excel Dynamic Arrays
    10:06
  • Absolute & Mixed Cell Referencing - No Longer Needed in Excel?
    05:46
  • SUMIFS & COUNTIFS Improved! (More than one criteria in the same column)
    07:34
  • VLOOKUP with Excel Dynamic Arrays
    05:43
  • IF Function & Excel Dynamic Arrays
    04:09
  • Referencing Spilled Data Ranges in Other Excel Sheets
    06:35
  • TRANSPOSE & FREQUENCY Functions Made Easier with Excel DA
    05:31
  • Do Excel Dynamic Arrays Make Spreadsheets Slow?
    05:56
  • Challenge: Data Analysis with Legacy Excel Formulas & Dynamic Arrays
    00:33
  • Answer: Data Analysis with Legacy Excel Formulas & Dynamic Arrays
    08:30
  • Conclusion: Impact of Dynamic Arrays on Legacy Excel Formulas
    02:11

  • Introduction - DA Impact on Excel Features
    01:11
  • Data Validation List using Spill Range (and Excel Table)
    05:53
  • Excel Drop-down List (Excludes Blank Cells)
    05:07
  • Dependent Drop-down Excel Data Validation (Unique & Sorted)
    04:49
  • Conditional Formatting & Excel Dynamic Arrays
    10:57
  • Excel Charts with Dynamic Arrays
    07:46
  • Challenge: More Dynamic Dependent Data Validation Excel Report
    00:25
  • Answer: More Dynamic Dependent Data Validation Excel Report
    05:11
  • Conclusion: DA Impact on Excel Features
    01:27

  • Congratulations! You Completed the Fundamentals!
    01:26
  • More Challenges & Practice Activities
    00:07
  • Quiz: Challenge Successfully Completed?
    5 questions
  • Challenge Answers
    00:01

  • Why THIS Excel Theory is Important
    01:37
  • Boolean Logic (TRUE & FALSE) in Excel Formulas Explained
    04:29
  • Excel FILTER to Exclude Blanks, Zero-Value Cells & Text
    05:55
  • FILTER Function for Multiple Criteria (AND Logical Test in Excel)
    07:33
  • FILTER Formula for Either Value (OR Logical Test in Excel)
    03:18
  • Challenge: Dynamic Array Filter Like a Pro
    00:37
  • Answer: Dynamic Array Filter Like a Pro
    07:50
  • Conclusion: How to Filter Like a Pro in Excel
    00:12

  • Debugging Trick & Excel Array Syntax - Comma versus Semicolon
    07:33
  • Named Array Constants for Quick Report Setup in Excel
    02:32
  • FILTER Function with Array Constants for IF EMPTY Argument
    02:51
  • Top 3 Values with Excel LARGE Function
    04:43
  • Custom Dynamic Sort with Multiple Columns in Excel
    03:30
  • Quiz: Working Correctly with Array Constants in Excel
    5 questions
  • Conclusion: Use the Correct Array Syntax in Excel Formulas
    00:08

  • Preview00:45
  • Easy Pivot Table-Style Reports with Dynamic Arrays
    12:28
  • Pivot Table (Style) Report with Row & Column Selection (INDEX MATCH & DAs)
    13:30
  • Interest Paid per Month & Year with Excel SEQUENCE Function
    08:47
  • Loan Amortization Table in Excel (Interest, Principal) with SEQUENCE
    07:05
  • Dynamic Excel Calendar with SEQUENCE
    11:44
  • Shrinking Data Validation - Hide Used Items in Excel
    07:50
  • Preview09:07
  • Challenge: Interactive KPI Selection Excel Report
    00:29
  • Answer: Interactive KPI Selection Excel Report
    07:46

  • Introduction - Advanced Data Analysis with Excel Dynamic Arrays
    01:04
  • Multiple Match Results Across Columns (Excel FILTER Function)
    03:18
  • Multiple Match Results in Single Cell (Excel TEXTJOIN)
    03:54
  • Lookup Approximate Match with Excel FILTER Function
    06:17
  • Find Top n Largest Categories & Values (FILTER) in Excel
    05:45
  • Challenge: Top 3 Salaries & Employees by Department
    00:27
  • Answer: Top 3 Salaries & Employees by Department
    07:01

  • Introduction - How Arrays Work in Excel
    01:22
  • Lifting & Pairwise Lifting (Excel Arrays)
    05:20
  • Broadcasting Excel Array Behavior
    04:57
  • Implicit Intersection - The Main Legacy Excel Problem
    01:55
  • Are Excel CSE Formulas No Longer Needed?
    01:51
  • Quiz: How Excel Arrays Work Under the Hood
    4 questions

Instructor

Leila Gharani
Microsoft MVP, Bestselling Instructor
Leila Gharani
  • 4.7 Instructor Rating
  • 55,604 Reviews
  • 181,939 Students
  • 9 Courses

Leila Gharani is a Microsoft MVP (Most Valuable Professional) dedicated to helping you create outstanding tools and reports in Excel & Google Sheets. Her courses are for those who want to shine at work, accelerate their careers and achieve more in less time.

Leila' background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert & Project Manager. For over 15 years she has worked in the Finance and IT departments implementing reporting systems based on Oracle and SAP. All the while using Excel VBA to automate and improve processes.

Since 5 years, Leila runs her own site and has an active YouTube channel with many engaging, high quality videos. She provides customized spreadsheet dashboard seminars and enhanced visualization techniques to large corporations. She also teaches at the University of Applied Sciences in Upper Austria.

Her courses include examples based on her experience as well as  the consulting work she has done for international companies. Many of the Excel tips and tricks she shares is based on the questions received from experienced professionals in her Excel training sessions.

She places great value on keeping her courses not only informative but also engaging. For this reason, students will find enough challenges, exercises and downloadable files to practice so they can maximize the value gained from each course.

Leila's passion is learning, sharing and solving difficult data analysis problems.

  • Udemy for Business
  • Teach on Udemy
  • Get the app
  • About us
  • Contact us
  • Careers
  • Blog
  • Help and Support
  • Affiliate
  • Terms
  • Privacy policy
  • Cookie settings
  • Sitemap
  • Featured courses
Udemy
© 2021 Udemy, Inc.