Excel PowerPivot and Interactive Visualisations Training

The programme

Programme overview

The latest analytics tools in Microsoft Excel empower users to efficiently manipulate and analyse large volumes of data, making it possible to uncover insights, create compelling reports, and build interactive dashboards. This practical, hands-on course focuses on Excel’s advanced data tools—PowerPivot, Power Query, Power View, and Power Map—and demonstrates how to extend Excel’s traditional Pivot Table functionality with robust data modelling, advanced calculations, and immersive data visualizations. By the end of this Excel PowerPivot and Interactive Visualisations Training course, you’ll be equipped to transform raw data into actionable insights and visually engaging reports.

Note: PowerPivot, Power Query, Power View, and Power Map are available as add-ins in Excel 2010 and Excel 2013, and are integrated into Excel 2016 and later.

Who should attend

Who is this programme for?

This Excel PowerPivot and Interactive Visualisations Training course is designed for professionals responsible for producing complex Excel reports, conducting data analysis, and building dashboards.Ideal participants include:

• Business analysts
• Data professionals
• Financial analysts
• Managers
• Anyone who works with large datasets or seeks to automate and enhance Excel reporting

Learning outcomes

What you will learn

By the end of this Excel PowerPivot and Interactive Visualisations Training course, participants will be able to:

• Understand the capabilities and differences of PowerPivot, Power Query, Power View, and Power Map
• Import, clean, and transform data from multiple sources
• Build and manage robust data models in Excel
• Create and use calculated columns, measures, and KPIs with DAX formulas
• Design interactive reports, dashboards, and visualizations using Power View and Power Map
• Automate data refresh and transformation with Power Query
• Effectively communicate data insights using modern visualization techniques

Explore the programme

Detailed programme outline

Lesson 1: Introduction to Excel’s Advanced Analytics Tools

• Overview: PowerPivot, Power Query, Power View, Power Map
• The Power BI ecosystem in Excel
• Comparing standard Excel features vs. PowerPivot
• Demonstrations: Pivot Table vs. PowerPivot; Power View and Power Map samples

Lesson 2: Preparing and Structuring Data

• Working with Excel lists and tables
• Adding helper columns with VLOOKUP and formulas
• Cleaning and normalizing tables for analysis

Lesson 3: Importing Data into PowerPivot

• Supported data types and structures
• Adding Excel and Access tables to PowerPivot
• Maintaining and updating data sources in the model

Lesson 4: Creating and Managing the Data Model

• Understanding data models in Excel
• Defining key fields and relationships
• Creating and managing relationships between tables
• Using linked tables and building hierarchies

Lesson 5: Advanced Calculations in PowerPivot

• Types of calculations: calculated columns, measures (calculated fields)
• Implicit vs. explicit measures
• DAX best practices: rules, syntax, and use cases
• Creating Key Performance Indicators (KPIs)
• When to use calculated columns vs. measures

Lesson 6: Introduction to Data Analysis Expressions (DAX)

• Understanding the purpose and syntax of DAX
• Building basic and advanced DAX formulas
• Using DAX for data manipulation and analysis

Lesson 7: Practical DAX Applications

• Filter functions and context
• Time intelligence functions (e.g., YTD, MTD, QTD calculations)
• Combining multiple functions in a single formula
• Managing multiple data tables with DAX

Lesson 8: Data Analysis with Pivot Tables and Pivot Charts

• Creating Pivot Tables from data models
• Filtering and segmenting data with slicers
• Enhancing reports with Pivot Charts
• Formatting, combining, and managing multiple charts and tables

Lesson 9: Interactive Visualizations with Power View

• Introduction to Power View for data visualization
• Building basic and advanced reports
• Creating tables, matrices, and multiple chart types (bar, column, pie, line, scatter)
• Geographic and map-based visualizations

Lesson 10: Designing Interactive Dashboards in Power View

• Linking and filtering visualizations
• Organizing data with tiles
• Dashboard design and exposure

Lesson 11: Data Loading and Transformation with Power Query

• Importing data from diverse sources (Excel, databases, web, etc.)
• Data cleansing, shaping, and transformation techniques
• Merging, grouping, and aggregating data
• Adding calculated columns within Power Query

Lesson 12: Geospatial Visualization with Power Map

• Preparing data for mapping
• Creating map-based and 3D visualizations
• Heat maps, region maps, and layering data
• Analysing data over time and building interactive tours

Before you book

Entry requirements

Participants should have completed Excel Introduction or possess equivalent skills. Familiarity with basic Excel features (e.g., creating worksheets, basic formulas) is required. Prior experience with Pivot Tables is helpful, but not essential.

Learning approach

How you will learn

Our diverse instructional approaches ensure effective learning:

– Lectures & Presentations: Engage with expert-driven, stimulating content.– Course Material: Access well-crafted supporting resources.– Group Work: Collaborate on discussions and case studies for practical insights.– Workshops & Role-Play: Participate in immersive, scenario-based activities.– Practical Application: Focus on applying theoretical knowledge in real situations.– Post-Training Support: Receive extensive support after training for skill implementation.

Discuss your requirements

Dates, duration & arrangements

Contact Ratomac to confirm dates, duration, delivery format, entry requirements and any applicable assessment or certification before booking.