Microsoft Excel – Advanced

Content

This course will cover:

• Module 1: Power Query (ETL): Using Get & Transform Data (Power Query) to connect to external sources, clean, shape, and merge data (Extract, Transform, Load principles).
• Module 2: Power Pivot & DAX: Introduction to the Data Model, creating relationships between tables, and writing basic Data Analysis eXpressions (DAX) measures.
• Module 3: What-If Analysis & Solver: Utilising Goal Seek, Data Tables (One-Variable, Two-Variable), and the Solver add-in for scenario planning, optimisation, and back-solving complex problems.
• Module 4: Advanced PivotTables: Using calculated fields/items, leveraging the “Show Values As” feature, and connecting Pivot Tables to external data sources (Cubes/ODBC/Data Model).
• Module 5: Dynamic Array Formulas: Introduction to modern Dynamic Array functions (e.g., UNIQUE, SORT, FILTER) and the concept of spill ranges.
• Module 6: Advanced Charting: Creating dynamic charts (using named ranges) and advanced chart types (Gantt, Waterfall), focusing on high-impact visualisation.
• Module 7: Advanced Functions: Utilising combined functions (INDEX/MATCH, Aggregate) for robust reporting, mastering conditional logic, and formula auditing tools.

Outcomes

• Efficiently connect to, clean, and consolidate disparate data sources without relying on manual copy/paste or complex formulas.
• Build small-scale relational databases within Excel and define powerful measures for deep analytical reporting, independent of the worksheet grid.
• Perform complex forecasting, sensitivity analysis, and resource optimisation to support strategic business decisions.
• Extend the capabilities of Pivot Tables beyond simple summation by creating custom metrics and reporting on external data models.
• Leverage Dynamic Array Formulas to perform advanced data extraction and manipulation tasks with single, efficient formulas.
• Build professional, interactive dashboards and complex charts to visualise key performance indicators (KPIs) and data trends.
• Write highly flexible, non-volatile, and powerful lookup/calculation formulas, and efficiently troubleshoot existing workbooks.

Who Should Attend

This course is ideal for professionals in roles that require complex data handling, analysis, and reporting. This includes, but is not limited to, people working in:

• Data Engineering and BI/Data Analysis.
• Operations, Forecasting, Finance, and Modeling.
• Anyone responsible for consolidating, cleaning, and reporting on large, messy, or complex datasets.

Ideally for ASO4 – SAES or equivalent, this course is primarily aimed at Advanced users, but some content (Dynamic Array Formulas) is also suitable for Intermediate users.

Benefits to You and Your Organisation 

• Individual Benefit: Gain mastery over Excel’s cutting-edge tools, transitioning from a report consumer to a powerful data architect and analyst. 
• Organisational Benefit: Drive better strategic business decisions through complex forecasting, optimisation, and sensitivity analysis capabilities. 
• Individual Benefit: Reduce manual work and errors by automating data cleanup (Power Query) and creating single, efficient formulas (Dynamic Arrays). 
• Organisational Benefit: Improve the reliability and professionalism of internal reporting, leading to higher confidence in KPI and data trend visualisation.