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.

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

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