IIRF Online > Office Productivity > Microsoft > Excel Formulas and Functions > Master NEW Excel Functions in Office 365 & Office 2021
Master NEW Excel Functions in Office 365 & Office 2021 by Udemy
Learn to Use Excel's NEW Functions (FILTER, UNIQUE, SORT, XLOOKUP...) to Dramatically Simplify the Work You Do in Excel.
Course Highlights
- 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
Skills you will learn!
Curriculum
6 Topics
Course Structure & Outline
Do You Have Excel Dynamic Arrays? DO THIS TEST!
Availability of Dynamic Arrays & NEW EXCEL Functions (FILTER XLOOKUP SORT...)
Summary of Updates to Excel's Calculation Engine
DOWNLOAD Course Files HERE
Course Outline for Quick Reference
16 Topics
Introduction & Glossary of Modern Excel Terms
Formulas Spill & Other Excel Dynamic Array Characteristics
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
NEW XLOOKUP in 2020 for Excel Office 365
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
12 Topics
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 Functions 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
10 Topics
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
4 Topics
Congratulations! You Completed the Fundamentals!
More Challenges & Practice Activities
Quiz: Challenge Successfully Completed?
Challenge Answers
8 Topics
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
8 Topics
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 Constants 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
10 Topics
Introduction - Interactive Excel Reports Made Easy
Easy Pivot Table-Style Reports with Dynamic Arrays
Pivot Table (Style) Report with Row & Column Selection (INDEX MATCH & DAs)
Interest Paid per Month & Year with Excel SEQUENCE Function
Loan Amortization Table in Excel (Interest Principal) with SEQUENCE
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
7 Topics
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
6 Topics
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
6 Topics
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
2 Topics
Final Words
SPECIAL BONUS: Discover Exciting Offers!
Master NEW Excel Functions in Office 365 & Office 2021
Related Excel Formulas and Functions Courses
Thank You!
Your review has been submitted successfully.