Advanced Excel: Functions, Power Query and Power Pivots

Program Overview

Modern Excel includes a full self-service analytics toolkit that many users never touch. This programme covers its three pillars: advanced formulas including dynamic arrays such as FILTER, SORT, UNIQUE and XLOOKUP together with LET and LAMBDA; Power Query for repeatable import, cleaning and combining of data from files, folders and databases; and Power Pivot for building a data model with relationships and DAX measures that handle large datasets. Participants automate a monthly reporting process from raw data to final PivotTables.

Learning Outcomes

• ✓Build dynamic array formulas with FILTER, SORT, UNIQUE and XLOOKUP that update automatically as data changes.
• ✓Simplify complex calculations with LET and create reusable custom functions with LAMBDA.
• ✓Create Power Query queries that import, clean and combine files from a folder without manual steps.
• ✓Build a Power Pivot data model with relationships between fact and lookup tables.
• ✓Write DAX measures for totals, ratios and time comparisons and use them in PivotTables.
• ✓Automate a recurring report so that a single refresh updates all outputs.

Target Audience

• Experienced Excel users and analysts
• Finance and accounting professionals
• Reporting and MIS officers
• Operations and supply chain analysts

Detailed Program Modules

Training Methodology

Applied Project & Assessment