Module 3: Pivot Tables and Power Query

Markdown

View as Markdown

Module Overview

This module focuses on using Pivot Tables and Power Query in Microsoft Excel to summarise, transform and analyse large datasets.

Learners will explore how to create customised Pivot Tables, apply interactive filters, work with calculated fields and measures, and import data from different sources.

The module also covers data merging, appending, cleaning and shaping techniques, helping participants prepare structured datasets and streamline business reporting processes.

What Will Be Covered

3.1 Creating and Customizing Pivot Tables

  • Understanding the purpose and applications of Pivot Tables.

  • Creating Pivot Tables from structured datasets.

  • Customising Pivot Table layouts and fields.

  • Summarising large datasets for meaningful analysis.

  • Organising information to compare business performance across categories.

3.2 Using Slicers and Timelines with Pivot Tables

  • Understanding slicers and timelines in Pivot Tables.

  • Adding slicers for interactive data filtering.

  • Using timelines to analyse date-based information.

  • Connecting filters to Pivot Tables.

  • Creating interactive reports for different business periods and categories.

3.3 Calculated Fields and Measures

  • Understanding calculated fields and measures in Pivot Tables.

  • Creating calculated fields for additional analysis.

  • Performing advanced calculations within Pivot Tables.

  • Using calculated values to evaluate business performance.

  • Extending reports with customised metrics.

3.4 Power Query Introduction and Data Import

  • Understanding Power Query and its role in data preparation.

  • Importing data from different sources into Excel.

  • Connecting to external datasets.

  • Exploring the Power Query interface and available transformation options.

  • Preparing imported data for analysis and reporting.

3.5 Transforming Data with Power Query

  • Understanding data transformation techniques.

  • Cleaning and organising imported datasets.

  • Merging datasets from different sources.

  • Appending multiple datasets into a single table.

  • Applying transformation steps to prepare data for reporting.

3.6 Merging and Appending Data

  • Understanding the difference between merging and appending datasets.

  • Combining related tables using merge operations.

  • Adding records from multiple datasets using append operations.

  • Consolidating information from different sources.

  • Preparing unified datasets for business analysis.

3.7 Data Cleaning and Shaping Techniques

  • Identifying and handling inconsistencies in datasets.

  • Removing unnecessary information and unwanted records.

  • Structuring data for analysis.

  • Applying data-cleaning techniques using Power Query.

  • Preparing accurate and organised datasets for reporting.

Skills You Will Gain

  • Creating and customising Pivot Tables for data summarisation.

  • Applying slicers and timelines for interactive filtering.

  • Performing calculations using calculated fields and measures.

  • Importing data from multiple sources using Power Query.

  • Transforming and organising datasets.

  • Merging and appending data from different sources.

  • Cleaning and shaping datasets for accurate analysis.

  • Preparing structured data for business reporting.

Learning Outcomes

By the end of this module, learners will be able to:

  • Build customised Pivot Tables to summarise large business datasets and identify key performance trends.

  • Use slicers and timelines to create interactive reports for analysing business performance across different periods and categories.

  • Apply calculated fields and measures to evaluate business metrics and support performance analysis.

  • Import and transform data from multiple sources using Power Query for consolidated reporting.

  • Merge and append datasets to combine business information from different departments or systems.

  • Apply data-cleaning techniques to improve data accuracy, consistency and reliability.

  • Streamline data preparation and reporting processes, reducing repetitive manual work.

Certification Alignment

This module develops data summarisation, transformation and analysis skills that support the Microsoft Excel certification pathway. The concepts covered help learners work with large datasets, prepare structured reports and apply Excel tools to practical business analysis tasks.

Continue to the Next Module

Continue to Module 4: Advanced Data Analysis to learn how to apply statistical functions, perform regression analysis, use Goal Seek and Solver, and conduct what-if analysis for business planning and decision-making.

Was this article helpful?

Still need help?

Contact us