Excel Pivot Tables - Data Analysis with Power Query and Power Pivot

Revolutionise Your Reporting. Automate Your Data. Drive Insights.

This course is designed for Excel professionals who need to prepare, combine, clean, and analyse data from multiple sources—without relying on manual formulas or macros.

You will learn how to work smarter using Power Query and Power Pivot, two of Excel’s most powerful but underutilised tools.
Transform Your Excel Skills. Automate, Cleanse and Analyse Data with Confidence.

Are you spending too much time manually cleaning, merging, or refreshing data in Excel?
Are your reports prone to error, duplication, or inconsistency?

Power Query and Power Pivot are modern Excel tools designed for professionals who want to:
• Automate repetitive data preparation tasks
• Combine data from multiple sources
• Build scalable, accurate, and dynamic reports
• Deliver faster business insights with confidence

This course teaches you how to unlock Excel’s full data analysis potential without relying on VBA or advanced formulas.

Who Should Attend?
This course is for anyone who needs to manipulate or interrogate large data sets. It does not focus on any formulas, so is not designed for someone who wants to use lots of formulas or functions. The course is aimed at those who need to create end-user systems which will be used by other people. Learners should already have a good understanding of Excel basics and should be able to create basic spreadsheets, format cells and manipulate columns, rows, pictures and charts. Ideally, learners would have already attended Level 1 – Introduction and Level 2 – Intermediate courses.

Course Outline
The course content will include the items listed below. Items not listed here are covered at another level. Some items may not be covered due to time constraints, but all will be covered in the notes.

Power Query – Automate and Clean Your Data
Power Query is a powerful data connection and transformation tool embedded within Excel. It allows you to extract, clean, and load data from multiple sources without manual intervention.

Key Concepts Covered:
• What is Power Query?
Understand how Power Query fits into Excel’s data workflow.

• Exploring the Power Query Editor
Learn to navigate and apply step-by-step transformations using the intuitive interface.

• Common Power Query Transformations
Clean and re-shape data using built-in tools for:
o Basic Table Transformations (remove columns, sort, filter)
o Text Tools (trim, clean, extract)
o Numerical Tools (rounding, operations, replacing)
o Date & Time Tools (extracting month, year, day, etc.)
o Rolling Calendars and date-based calculations

• Advanced Query Techniques:
o Create Index Columns and Conditional Columns for logic-based automation
o Pivot and Unpivot Columns to reshape data
o Merge Queries to combine tables based on keys
o Append Queries to stack datasets vertically
o Import multiple files from a folder for batch loading

• Best Practices for Power Query:
o Manage query dependencies
o Understand data source paths
o Avoid unnecessary complexity
o Ensure performance and reusability
________________________________________
Power Pivot – Build Scalable Data Models in Excel
Power Pivot allows you to build relational data models inside Excel, using large datasets without performance issues, and connect multiple tables with relationships.

Key Concepts Covered:
• What is a Data Model?
Understand how Power Pivot turns multiple flat tables into a single relational structure.

• Relational Database Theory:
o Database Normalisation: Reduce redundancy and improve structure
o Fact and Dimension Tables: Identify which tables hold metrics vs. attributes
o Primary & Foreign Keys: Understand join keys and referential integrity

• Table Relationships:
o Creating Relationships between tables
o Managing & Editing Relationships
o Exploring Relationship Cardinality (One-to-Many, Many-to-Many)
o Connecting Multiple Fact Tables appropriately

• Data Model Structure:
o Understand Star vs. Snowflake Schemas
o Hide irrelevant fields in Report View
o Create intuitive Hierarchies (e.g. Year → Quarter → Month)

• Best Practices for Data Models:
o Keep your model lean and readable
o Use naming conventions and documentation
o Validate joins and keys before building reports
________________________________________

PivotTables and PivotCharts—allow you to build interactive, business-ready reports and dashboards directly within the Excel environment.
This module focuses on professional presentation and analytical storytelling, enabling you to deliver clear, actionable insights to your stakeholders.

Key Topics Covered:
• PivotTables – Analyse with Precision
o Understand the structure and behaviour of PivotTables
o Use the Field List and Field Settings panel effectively
o Rearrange, filter and group data for dynamic views
o Group data by dates, number ranges, and categories
o Apply calculated fields within the PivotTable
o Summarise large datasets using aggregation tools (SUM, AVERAGE, COUNT)
o Drill down into detailed source data behind results
o Use Refresh to keep your analysis up to date

• PivotCharts – Visualise the Model
o Create PivotCharts directly from PivotTables
o Select the appropriate chart type for your analysis (Column, Bar, Line, Pie, etc.)
o Connect charts to slicers and filters for interactivity
o Apply consistent formatting and design best practices
o Combine multiple charts into a cohesive visual report

• Slicers and Timelines – Interactive Filtering
o Add Slicers to filter your PivotTables and PivotCharts visually
o Use Timelines to create intuitive time-based navigation
o Connect multiple PivotTables to a single slicer for unified filtering
o Customise slicer layout and styles for visual impact