Excel Pivot Tables, Power Query & Power Pivot

Course Overview

Turn raw data into structured models and professional, repeatable reports.
This course focuses on Excel’s modern data analysis tools: PowerQuery, PowerPivot, and PivotTables. It shows how to move beyond manual data preparation and fragile spreadsheets and instead build clean, automated, and scalable reporting solutions. Learners develop the skills needed to prepare data properly, model it correctly, and analyse it efficiently using industry best practices. This course is critical for anyone who works with large or recurring datasets and needs accuracy, speed, and consistency.

________________________________________

Who Should Attend?

This course is ideal for:
• Excel users responsible for consolidating, preparing, or analysing large volumes of data
• Analysts or reporting professionals who need to build systems others can use and maintain
• Users managing regular data refreshes who require automation and consistency
• Professionals who want to replace manual, error-prone processes with structured workflows

Required Knowledge:

Participants should:
• Be confident with Excel basics, including formatting, charts, sorting, filtering, and general data handling
• Ideally have completed Microsoft Excel Level 1 – Introduction and Level 2 – Intermediate
• Have experience working with datasets and spreadsheets
Note: This course does not focus on formulas. Users needing complex formula logic should attend Microsoft Excel Level 3 – Advanced.

________________________________________

What You Will Learn

PowerQuery – Automate and Clean Your Data
• Understand the role of PowerQuery in Excel’s modern data stack
• Navigate and use the PowerQuery Editor confidently
• Remove, reorder, sort, and filter columns and rows
• Clean, split, and standardise text data
• Perform mathematical and statistical transformations
• Extract and derive date and time elements such as Month, Quarter, and Year
• Build rolling calendar logic
• Add Index Columns and Conditional Columns
• Pivot and Unpivot data for reshaping
• Merge queries to join data across tables
• Append queries to stack data vertically
• Import and process multiple files from a folder
• Manage query dependencies and document data logic
• Optimise queries for performance and reuse

PowerPivot – Build Scalable Data Models
• Understand what a Data Model is and why it matters
• Apply basic relational database design concepts
• Identify Fact and Dimension tables
• Use Primary and Foreign Keys correctly
• Create and manage relationships between tables
• Understand One-to-Many and Many-to-Many relationships
• Connect multiple fact tables to shared dimensions
• Design Star and Snowflake schemas
• Hide fields and create hierarchies such as Year → Quarter → Month
• Apply naming conventions and documentation standards
• Optimise models for performance and long-term maintenance

PivotTables and PivotCharts – Analyse and Report
• Build PivotTables from relational data models
• Rearrange, group, filter, and summarise data accurately
• Create calculated fields
• Group data by dates, ranges, and custom categories
• Drill down into underlying source data
• Refresh and maintain PivotTables across reporting cycles
• Create PivotCharts directly from PivotTables
• Apply best-practice chart design and layout
• Use Column, Bar, Line, and Pie charts effectively
• Build multi-chart dashboards with consistent filtering
• Slicers and Timelines – Interactive Analysis
• Add Slicers for visual filtering
• Use Timelines for time-based navigation
• Connect multiple PivotTables and Charts to a single control
• Format slicers and timelines for professional presentation

________________________________________

Exclusions:

This course does not cover:
• Complex Excel formulas or advanced function logic
• VBA, macros, or custom automation scripts
• Cell-based modelling outside the Data Model
• Advanced DAX measures and calculations
• Dashboard design theory beyond Pivot-based reporting
These topics are addressed in separate advanced courses.

________________________________________

Key Learning Outcomes

By the end of the course, participants will be able to:
• Automate data cleaning and preparation using PowerQuery
• Build structured, relational data models inside Excel
• Design reports that refresh reliably with new data
• Analyse large datasets without manual rework
• Create interactive PivotTables, charts, slicers, and timelines
• Develop Excel solutions that are scalable, maintainable, and professional

________________________________________

Delivery Format

This is a classroom-based course. Each participant will have access to a fully equipped workstation with Microsoft Excel, PowerQuery & PowerPivot installed. The instructor will guide the course using live demonstrations, practical exercises, and real-time feedback.
Training Environment Includes:
• Individual PCs with Microsoft Excel, PowerQuery & PowerPivot
• Course materials and online notes
• Comfortable classroom setting conducive to learning

Participants are not required to bring their own laptop.

________________________________________

Recommended Progression Pathway

After completing this course, learners should consider:
• Microsoft Excel – DAX and Advanced Data Models
• Advanced Power BI Data Modelling and Reporting
• Excel Dashboard Design and Visual Storytelling
These courses extend the data modelling and reporting skills developed here into advanced analytics and enterprise-level reporting.

________________________________________

Learner Support

Participants will have indefinite online access to all practice files and notes at https://www.dti.ie. Email support will also be offered on items covered during this course at support@dti.ie.