Mastering Excel Data Analysis: Power Query, Power Pivot and Advanced Reporting Course (Online / Remote)
1Summary
Most Excel users only ever touch a fraction of what the programme can do — and the tools they are missing are exactly the ones built for serious data work. This course focuses on two of the most powerful: Power Query, which cleans and reshapes messy data before it ever reaches a report, and Power Pivot, which lets pivot tables handle millions of rows that would normally bring a spreadsheet to its knees.
Rather than teaching Excel from the ground up, the course assumes a working knowledge of the programme and pushes straight into the functions, techniques and DAX formulas that turn Excel into a genuine data-analysis platform — one capable of manipulating, analysing and reporting on large, messy, multi-source datasets.
2Objectives and target group
Who Should Attend?
- Accountants and finance professionals working with large datasets
- Business analysts and researchers needing advanced pivot table skills
- Advanced Excel users ready to move into Power Query and Power Pivot
- Any staff who must consolidate, clean or analyse data for reporting
Knowledge and Benefits
By the end of the course, participants will be able to:
- Apply Excel functions to prepare clean data for pivot table analysis
- Build, customise and troubleshoot pivot tables for management reporting
- Use Power Query to clean, transform and merge data from multiple sources
- Analyse millions of rows using Power Pivot and DAX formulas
- Produce interactive, decision-ready dashboards and reports
3Course Content
Module 1: Power Query — Cleaning Data at the Source
- What Power Query is and where it fits among Excel's data tools
- Connecting Excel to external sources: files, folders, the web and SQL
- Creating, editing and combining queries (merge and append)
- Practical cleanup: unpivoting data, handling merged cells, splitting and renaming columns, filtering rows
Module 2: Preparing Data for Reporting
- Formatting data as proper Excel tables
- Using lookup and text functions to shape raw data
- Naming cells and ranges for cleaner formulas
Module 3: Building and Customising Pivot Tables
- Number and cell formatting inside pivot tables
- Report layout options and calculated value fields
- Grouping and ungrouping fields for clearer summaries
Module 4: Sorting, Filtering and Custom Reporting
- Sorting using custom lists and creating calculated fields
- Filtering with slicers and timelines
- Connecting multiple pivot tables to one set of slicers
- Customising reports with the GetPivotData function
Module 5: Analysing Multiple Data Sources
- Using the pivot table wizard and internal data model
- Building pivot tables from external data sources
Module 6: Power Pivot and DAX for Big Data
- Benefits and limitations of Power Pivot
- Merging data from multiple tables without VLOOKUP
- Writing DAX formulas and calculated fields
- Using CALCULATE and RELATED functions for advanced analysis