Start a conversation ↗

Description

Advanced Excel and Power Pivot: Modelling, Analysing and Reporting Large Data Volumes

Move beyond the limits of the standard PivotTable by building a genuine data model

  • 4 days — 28 h
  • In-person or virtual
  • Intermediate
  • Up to 6 participants

Beyond a few tens of thousands of rows, the workbook slows down, lookup formulas multiply and the smallest change of source means rebuilding everything. The analyst then spends more time consolidating extracts than answering the questions being asked.

Four days to move from spreadsheet to data model. The programme covers source preparation, building a relational model in Power Pivot, writing measures in the DAX language, time-based analysis and reporting through interactive PivotTables. Every step is applied to a high-volume data set.

Learning objectives

  • Prepare and standardise data sources before import
  • Build a relational model in Power Pivot
  • Write DAX measures that answer precise analytical questions
  • Use time intelligence functions on a date axis
  • Deliver analyses through interactive PivotTables
  • Organise model refresh when the source changes

What makes this programme different

The programme runs on a data set too large for a standard PivotTable
DAX measures are written from the business questions raised by the group
The model built in session is refreshed with a new extract to test its robustness

Programme

1Preparing Data Before the Model

A clean source prevents impossible calculations

  • Structure data into a usable table format
  • Clean and standardise columns coming from extracts
  • Merge several sources without cascading lookup formulas
  • Set up automatic query refresh

2The Power Pivot Data Model

From worksheet tabs to relationships

  • Load tables into the data model
  • Create relationships between fact tables and reference tables
  • Build a date table suited to the analysis
  • Monitor data volume and workbook performance

3Measures in the DAX Language

Calculate correctly whatever the filter

  • Distinguish between calculated columns and measures
  • Write basic aggregations and common ratios
  • Understand filter context and row context
  • Master the functions that alter applied filters

4Time-Based Analysis and Comparisons

Making trends speak

  • Calculate running totals and moving averages
  • Compare a period with the preceding one
  • Handle non-calendar financial years and incomplete periods
  • Build reliable progression indicators

5Reporting and Industrialisation

Deliver a tool that lasts

  • Design PivotTables driven by slicers
  • Build a summary dashboard within the workbook
  • Document the model for a third-party user
  • Organise periodic updates and variance checks

Who is it for

Management controllers, analysts and advanced Excel users dealing with data volumes that the standard spreadsheet can no longer handle comfortably.

Prerequisites

Regular hands-on practice with Excel formulas and PivotTables is essential.

Dates & locations

36 scheduled dates between November 2026 and December 2027. Seats are confirmed in the order enquiries are received.

November 2026

December 2026

January 2027

February 2027

March 2027

April 2027

May 2027

June 2027

September 2027

October 2027

November 2027

December 2027

None of these dates suit you? We open additional sessions on request, and any programme can be run privately for your team.

Practical details

Before the programme
Online positioning questionnaire. Your development objectives are shared with the trainer, who tailors the practical case studies to your context.
Teaching methods
Theoretical input, workshops and practical case studies. Digital course materials and method sheets provided.
Assessment
Multiple-choice tests and role-play exercises. Assessment of learning at the start and end of the programme, with immediate and 60-day follow-up evaluations.
After the programme
One year of access to the e-learning platform. Self-assessment of the skills acquired and a 30-day follow-up session with your trainer.
How to register
Registration online or on the basis of a quotation.
Lead time
11 working days after confirmation of registration.
Accessibility
Accessible to people of determination. Contact our accessibility coordinator to design a suitable solution: contact@mpf-academy.ae
Start dates
Rolling intake: in addition to the scheduled sessions, this programme can start on request.