Advanced Excel
Advanced MS Excel Training — Level 2
Solve complex reporting, analysis, data preparation, and automation problems using advanced formulas, modern Microsoft 365 functions, Power Query, Power Pivot, and PivotTables.
Handle complex workbooks with greater speed and control.
Designed for experienced Excel users who prepare reports, consolidate data, analyze transactions, and build reusable spreadsheet solutions.
Troubleshoot errors and build advanced logical formulas.
Apply flexible lookup and criteria-based calculations.
Use modern Microsoft 365 array and productivity functions.
Prepare data, build models, dashboards, and basic automations.
Course overview
A practical, instructor-led program with guided exercises and workplace examples. Open a topic to see the coverage.
Part 1. Review of Excel Functions
- Review of Excel Functions
- Quick Review of Excel Functions
Part 2. Advanced Logical Structures
- Advanced Logical Structures
- Combining AND and OR in One Formula
Part 3. Advanced Lookup Scenarios
- Advanced Lookup Scenarios for XLOOKUP, INDEX/MATCH, INDIRECT
- Looking Up Matrices using OFFSET
Part 4. Advanced Formulas
- Advanced Criteria in SUMIFS, COUNTIFS, etc.
- Dealing with Intermediate to Advanced Date Problems (Tenure, Ageing, etc.)
Part 5. Using Arrays to Shorten Formulas
- Using Arrays to Shorten Formulas
- Array 1: Merging Ranges within a Formula
Part 6. Using New MS365 Functions and Features in Excel
- Using MS365 Functions in Excel
- Shortening Formulas using LET and LAMBDA Function
Part 7. PowerPivot Data Modeling and Data Cleaning Tools
- Connecting Data to Power Pivot
- PowerPivot to Combine Tables into One Pivot Table
Part 8. Advanced PivotTable Customization
- Creating Advanced Calculations in PivotTables
- Creating Dashboards
Part 9. Introduction to Macros
- Introduction to Macros
- Understanding Macro Security
Full course outline
Review the complete legacy course outline, including all detailed subtopics.
View full outline
- Part 1. Review of Excel Functions
- Review of Excel Functions
- Quick Review of Excel Functions
- Troubleshooting Excel Error Messages
- Solving Complex Problems in Excel
- Part 2. Advanced Logical Structures
- Advanced Logical Structures
- Combining AND and OR in One Formula
- Complex Nested IFs
- Advanced Conditional Formatting (Formula-Based)
- Part 3. Advanced Lookup Scenarios
- Advanced Lookup Scenarios for XLOOKUP, INDEX/MATCH, INDIRECT
- Looking Up Matrices using OFFSET
- Using INDIRECT Function to Lookup Several Tabs
- Creating Advanced Dropdowns using INDIRECT
- Using Wildcards to Deal with Inconsistent Arguments/Lookup Values
- Part 4. Advanced Formulas
- Advanced Criteria in SUMIFS, COUNTIFS, etc.
- Dealing with Intermediate to Advanced Date Problems (Tenure, Ageing, etc.)
- Using Wildcards with SUMIFS
- Part 5. Using Arrays to Shorten Formulas
- Using Arrays to Shorten Formulas
- Array 1: Merging Ranges within a Formula
- Array 2: Merging Formulas into one Formula
- Array 3: Combining Criteria in SUMIFS
- Using Arrays to Shorten Formulas
- Part 6. Using New MS365 Functions and Features in Excel
- Using MS365 Functions in Excel
- Shortening Formulas using LET and LAMBDA Function
- Using Modern Functions: TEXTSPLIT, IFS, XLOOKUP, etc.
- Maximizing the FILTER Function
- VSTACK and HSTACK and Their Applications
- WRAPCOLS and WRAPROWS Functions
- Exploring New Charts in Excel
- Map Charts
- Waterfall Charts
- Part 7. PowerPivot Data Modeling and Data Cleaning Tools
- Connecting Data to Power Pivot
- PowerPivot to Combine Tables into One Pivot Table
- Introduction: Using PowerQuery to Extract and Fix Data from Other Sources
- Part 8. Advanced PivotTable Customization
- Creating Advanced Calculations in PivotTables
- Creating Dashboards
- Using PIVOTBY and GROUPBY Alternative to PivotTables
- Part 9. Introduction to Macros
- Introduction to Macros
- Understanding Macro Security
- Exploring Macro Security Settings
- Macro Recording
- Recording and Implementing Macros for Task Automation and Efficiency.