Excel Power Pivots: Advanced Reports

This one-day course is designed for confident, technically skilled Excel users who want to move beyond standard PivotTables and feel fully in control of complex, connected datasets.

You'll discover how to build smarter, faster reports using data models, powerful DAX formulas, and interactive dashboards - all within Excel. Walk away with the skills and confidence to make high-level, data-led decisions with less manual work.

Course Objectives

By the end of this course, you’ll be able to:

• Import and manage data from multiple sources within Excel Power Pivot

• Build data models using relationships and hierarchies

• Create advanced PivotTables and dashboards using slicers and KPIs

• Use DAX to create powerful custom calculations and insights

Who Is This Course For?

This course is ideal for: 

• Confident Excel users who already work with PivotTables

• Professionals who regularly analyse data and want smarter, faster reporting tools

• Anyone with complex or connected datasets who need greater flexibility in reporting

Prerequisites: You should already be comfortable using Excel formulas and PivotTables, and ideally have completed our Excel Intermediate or Excel Advanced course.

Course Outline

Introduction to Power Pivot

• Understand Power Pivot terms and concepts

• Showcase of what's possible with Power Pivot

• What is Business Intelligence (BI) in the context of Excel

• Limitations of standard Excel reporting

• Classic vs Power Pivot-based PivotTables

Connecting to Data Sources

• Import data from Excel, SharePoint Lists, and Data Feeds

• Paste or link data into Power Pivot

• Overview of single vs multi-table data models

Managing Data in Power Pivot

• Navigate the Power Pivot window

• Work with linked tables as data sources

• Create calculated columns and user-defined measures

• Set up KPIs and use AutoSum metrics

Dashboards and Interactivity

• Add slicers and timelines to enhance reports

• Compare Power Pivot slicers vs Excel slicers

• Create interactive dashboards within Excel

Diagram View and Data Relationships

• Use Diagram View to manage table relationships

• Set up and modify relationships

• Build and use hierarchies for easier analysis

Working with DAX (Data Analysis Expressions)

• Learn what DAX is and how it works in Excel

• Write DAX functions for custom calculations

• Use time intelligence functions for advanced analysis

Reflections & Planning

• Review key learnings from the day

• Discuss how Power Pivot applies to your reporting goals

• Plan your next steps and areas for further development