Premier Home Page

Home | Training | Solutions |Contact Us

t: +44(0)20 7729 1811

About Us | IT Training | Financial Training | Management Training |Delegate Information

Microsoft Excel 2013 PowerPivot Training

Microsoft PowerPivot is an add on for Excel 2013 that provides Business Intelligence functionality & reporting within the familiar environment of Excel. PowerPivot provides the real power to crunch & analyse data on a scale previously unimagniable with pivot tables. This two day course guides participants through the functions of PowerPivot. Participants will learn how to work with creating data models, reports and custom calculations.
Excel 2010, Excel 2010 training


Module 1 – Introduction to PowerPivot

  • What is PowerPivot?
  • What is a Pivot Table?
  • Examples of good Pivot Tables
  • PowerPivot Set-up
  • Navigating the PowerPivot window
  • Exploring the PowerPivot tab & field list in Excel

Module 2 – Basic PowerPivot Concepts

  • Formatting Number
  • Understanding Calculated Columns
  • Using Lookup Tables
  • Understanding Measures
  • Handling Many Tables
  • Refreshing Data
  • Using Slicers

Module 3 - Adding data to PowerPivot

  • Data sources and types supported in PowerPivot workbooks
  • Importing data (from pre-prepared file)
  • Add Data by using Excel Linked Tables

Module 4 - Preparing data for analysis

  • Working with Tables and Columns
  • Filtering and Sorting Data
  • Creating Relationships Between Tables
  • Creating and Working with calculations

Module 5 - Data; formatting and layout

  • Creating, deleting a table
  • Rename a Table or Column
  • Set the Data Type of a Column
  • Hide or Freeze Columns
  • Undo or Redo an Action
  • Sorting and filtering data in a table

Module 6 – PowerPivot and Relationships

  • Understanding Relationships
  • Create a Relationship Between Two Tables
  • View and Edit Relationships
  • Delete Relationships
  • Troubleshoot Relationships

Module 7 - Calculations in PowerPivot

  • Overview of Data Analysis Expressions (DAX) language
  • Building Formulas for Calculated Columns and Measures
  • Understanding the Use of Relationships and Lookups in Formulas
  • Understanding Aggregations in Formulas
  • Filtering Data in Formulas
  • Recalculating Formulas

Module 8 – Understanding Data Analysis Expressions (DAX)

DAX language is a new PowerPivot formula language that allows users to define custom calculations in PowerPivot tables (calculated columns) and in Excel PivotTables (measures).

  • Calculation Foundations
  • DAX Data types, DAX Operators
  • Calculated Column Examples
  • Measures examples
  • Introduction to the Execution Context
  • Handling Errors in DAX
  • Common DAX Functions Examples

Module 9 – Data Models

  • What is a Data Model
  • Why Power Pivot Users need Data Modelling
  • Physical and Logical Data Models
  • Empty and Default Values
  • Normalisation and Denormalisation
  • Understanding how to denomalise
  • The SQL Query Designer as a Data Modelling Tool - Different types of Joins -Understanding Outer Joins

Module10 –Loading Data and Models

  • Understanding Data Connections
  • Loading Tables from SQL Server
  • Loading Tables from Access
  • Loading Data from Analysis Services
  • Using Linked Tables
  • Loading from Excel workbooks
  • Loading from Text Files
  • Loading from Data Feeds
  • Loading from SharePoint
  • Loading from SharePoint Reports
  • Loading from SharePoint Excel
  • Loading from SharePoint Lists

Module 11 - Understanding the Evaluation Context

  • What is the Row Context
  • What is the Filter Context
  • Filter Context on a Single Table
  • Evaluation Context in Detail
  • Evaluation Context and Relationships

Module 12 - Shaping Reports

  • Defining KPI’s
  • Creating Hierachies
  • Properties and Power View Reports
  • Names Sets in Excel
  • Using Perspectives
  • Drillthrough with Power Pivot

Module 13 - Power View

  • Creating Power View Reports
  • Inserting New Views in the Power View
  • Type of Charts and Maps
  • Applying Filters To Power View Reports
  • Creatintg Tiles

Finance for Non-Financial Managers

Next Scheduled Date:
15th May 2013


Microsoft Gold Partner

4 Ravey Street
London EC2A 4QP
t: +44(0)20 7729 1811
f:+44(0)20 7729 9412