Microsoft Excel Specialist - Data Analysis with PivotTables, PowerQuery & PowerPivot

Course Overview
Turn raw data into structured models and professional, repeatable reports.
This course focuses on Excel’s modern data analysis tools—PowerQuery, PowerPivot, PivotTables, and PivotCharts—in Microsoft 365 desktop for Windows. It shows how to move beyond manual data preparation and fragile spreadsheets to build reliable, repeatable, and scalable reporting solutions. Learners develop the skills to import and clean business data, connect tables in a structured Data Model, create foundational DAX measures, and analyse and present results clearly.
Practical business examples demonstrate how to check data quality, validate calculations, interpret results correctly, and refresh reports as new data arrives. The course is designed for anyone working with large or recurring datasets who needs greater accuracy, speed, and consistency in their reporting.

________________________________________

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 Excel tables and datasets; prior DAX knowledge is not required
Note: Foundational DAX formulas are included. Advanced worksheet formulas and advanced DAX are outside the scope of this course.
________________________________________

What You Will Learn

PowerQuery – Automate and Clean Your Data
• Understand how PowerQuery, PowerPivot, and PivotTables work together
• Import data from Excel, CSV/text files, PDFs, and web tables
• Navigate the PowerQuery Editor and manage Applied Steps
• Select, filter, clean, split, and standardise data
• Set data types and locale; handle nulls, errors, and duplicates
• Transform dates and create useful reporting periods
• Prepare a complete calendar for date-based analysis
• Add conditional, custom, and index columns
• Group, pivot, and unpivot data for analysis
• Merge tables and validate matches and row counts
• Append tables with consistent column structures
• Combine recurring files from a folder and check file inclusion
• Choose worksheet, connection-only, and Data Model load options
• Manage source paths, query dependencies, and repeatable refreshes

PowerPivot - Build Models and Foundational DAX Measures
• Understand the Data Model and the meaning of each table’s rows
• Identify fact and dimension tables and use a clear star schema
• Check primary keys, foreign keys, and unmatched records
• Create and validate one-to-many relationships
• Recognise duplicate-key and many-to-many modelling problems
• Build a calendar table and organise dates for reporting
• Distinguish measures, calculated columns, and report calculations
• Create and format DAX measures for totals and distinct counts
• Understand row context, filter context, and CALCULATE
• Use SUMX, RELATED, and DIVIDE for business measures and ratios
• Introduce year-to-date and prior-year comparisons; validate totals

PivotTables and PivotCharts – Analyse and Report
• Build PivotTables from the Data Model and reusable measures
• Arrange fields, sort, filter, and summarise results accurately
• Apply number formats, report layouts, and Show Values As
• Use calendar fields and model categories for grouped analysis
• Expand, collapse, and inspect underlying detail appropriately
• Refresh reports and reconcile results against source controls
• Create PivotCharts linked to PivotTables
• Choose clear chart layouts, labels, and meaningful comparisons
• Use column and bar charts for comparisons and lines for trends
• Build a coherent report with consistent filters and clear context
• Slicers and Timelines – Interactive Analysis
• Add Slicers for visual filtering
• Use Timelines for time-based navigation
• Connect slicers to compatible PivotTables and their charts
• 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:
• Import, clean, and combine business data using PowerQuery
• Build and check relational data models inside Excel
• Create foundational DAX measures and interpret filtered totals
• Refresh reports and validate results against source data
• Create interactive PivotTables, charts, slicers, and timelines
• Document reporting logic and maintain a reusable Excel solution
________________________________________

Delivery Format

This is a classroom-based course. Each participant will have access to a fully equipped workstation with Microsoft Excel installed. The instructor will guide the course using live demonstrations, practical exercises, and real-time feedback.
Training Environment Includes:
• Individual PCs with Microsoft 365 for Windows and PowerPivot enabled
• A comprehensive reference manual and supporting business datasets
• 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.