Advanced MS Excel (Level 2)

Designed for users who already know the basic and intermediate features of Excel but need to handle more complex reporting and analysis.
About this Event

Advanced Excel Training Level 2 is designed for users who already know the basic and intermediate features of Excel but need to handle more complex reporting, analysis, automation, and data preparation tasks. This course focuses on strengthening formula logic, improving lookup and criteria-based calculations, using modern MS365 functions, building better PivotTable reports, preparing data through PowerQuery and PowerPivot, and introducing macros for task automation. The training is practical and output-based, with emphasis on solving real workplace spreadsheet problems more efficiently and accurately.
By the end of the training, participants should be able to:

• Troubleshoot common Excel errors and solve complex spreadsheet problems.
• Build advanced logical formulas using nested IFs, AND, OR, and formula-based conditional formatting.
• Apply advanced lookup techniques using XLOOKUP, INDEX/MATCH, INDIRECT, OFFSET, and wildcards.
• Create advanced criteria-based calculations using SUMIFS, COUNTIFS, date formulas, and wildcard conditions.
• Use arrays and modern MS365 functions to shorten and simplify long formulas.
• Prepare, clean, and combine data using PowerQuery and PowerPivot.
• Create advanced PivotTable reports, dashboards, and alternative summary outputs.
• Understand macro security and record basic macros for task automation.

​
Who This Training Is For
This training is ideal for Excel users who regularly prepare reports, consolidate data, analyze transactions, monitor performance, or build reusable spreadsheet templates. It is best suited for staff, supervisors, analysts, finance personnel, HR personnel, operations personnel, administrative professionals, and team leaders who already know basic Excel and want to improve speed, accuracy, and confidence in handling complex workbooks.
Methodologies
Guided Demonstration and Formula Walkthroughs
The trainer will demonstrate each major function, tool, and technique using sample workplace scenarios. Participants will see how formulas are built step by step, how errors are diagnosed, and how different solutions can be compared.
Hands-On Spreadsheet Exercises
Participants will complete practical exercises involving lookup problems, criteria-based summaries, date calculations, arrays, PivotTables, PowerQuery, PowerPivot, dashboards, and macro recording. Activities will focus on realistic Excel tasks that participants can adapt to their own work.
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

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.