Welcome to ONLC Training Centers

Excel 2016 Data Models with PowerPivot Course Outline

 (2 days)

*** WARNING: High Volume Class, Sign Up Early ***
Excel is our most popular series classes. A green flag beside a date on the right means that a session has met the minimum enrollment levels and is running. However, it is common for dates for this course to fill several weeks in advance. Even if a date has a green flag, you might not be able to get into that session if it is full. To avoid disappointment, it is best to register for this class at least three weeks in advance.

PowerPivot works closely with the Excel Data Model to allow users to create pivot tables from multiple data sources. These PowerPivot tables can be significantly more flexible that ordinary pivot tables with the ability to create calculated columns and fields (measures) that change depending on how the data as filtered. Time Intelligence functions can also be used to easily create year-over-year comparisons, for example.

Excel Versions
PowerPivot is an add-in programs for Excel 2010 and Excel 2013. PowerPivot is integrated into Excel 2016. This course is taught using Excel 2016 software platform. The concepts between versions are the same; minor differences in the interface between versions are explained.

Target Student
This course is for individuals whose job responsibilities include complex Excel reports and analysis. If you need that additional flexibility in reporting not available in regular pivot tables, then this class focusing on Excel Data Models and PowerPivot is for you.

Students should have taken the 1-day Database Features and Pivot Table class (or have equivalent pivot table experience) prior to taking this class. For details on this prerequisite class, go to
Excel 2016 Database Features and Pivot Tables

Course Outline

Understanding Power Pivot
What is Power Pivot
Excel versus Power Pivot
Pivot Table Examples with Excel Data
Power Pivot Examples with a Data Model

Working with Data
Working with an Excel List
Using Vlookup to add Helper Columns
Working with an Excel Table
Cleaning up Tables
Working with Normalized Tables

Importing Data into Power Pivot
Understanding Acceptable Data Types
Adding Excel Tables to Power Pivot
Importing Access Tables
Saving the File
Adding and Maintaining Data in Power Pivot

Creating the Data Model
What is a Data Model
Understanding Key Fields
Creating Relationships between Tables in the Model
Managing Relationships
Create and Use Linked Tables
Create and Use Hierarchies

Using Calculations in Power Pivot
Types of Calculations in Power Pivot
Creating a Calculated Column
Creating a Calculated Field (Measures)
Implicit Calculated Field
Explicit Calculated Field
Understanding DAX Measure Rules and Best Practices
Choosing Between Calculated Columns and Measures
Creating a Key Performance Indicator (KPI)

Using Data Analysis Expressions
Understanding Data Analysis Expressions (DAX) Formulas
Where to use DAX formulas
Understanding the DAX syntax
How to create DAX formulas
Types of operations you can perform with DAX

Working with DAX Formulas
Working with Filter Function
Working with Time Intelligence Functions
Using Multiple Functions is a Formula
Using Multiple Data Tables

Data Analysis with Pivot Tables and Pivot Charts
Creating Pivot Tables
Filtering Data using Slicers
Add Visualizations to a Pivot Table
Creating Pivot Charts
Formatting Pivot Charts
Using Multiple Charts and Tables

Loading and Transforming Data with Power Query
Importing Data
Transforming, cleansing and Filtering Data
Merging and Shaping Data
Grouping and Aggregating Data
Inserting Calculated Columns

View outline in Word


Attend hands-on, instructor-led Excel 2016 Data Models with PowerPivot training classes at ONLC's more than 300 locations. Not near one of our locations? Attend these same live classes from your home/office PC via our Remote Classroom Instruction (RCI) technology.

For additional training options, check out our list of Courses and select the one that's right for you.

Microsoft Gold Partner
Need a price quote?

Follow the link to our self-service price quote form to generate an email with a price quote.

Email Alert

Receive an email when this class is available as "Ready to Run" or "Early Notice" status.

Attend from your office or home

If you have high-speed internet and two computers you can likely take this class from your office or home.

Need a class for a group?

We can deliver this class for a private group at your location. Follow the link to request more information.

Attend computer classes from ONLC Training Centers Request a copy via mail


Class Format
Class Policies
Student Reviews

Bookmark and Share

First Name

Last Name