Excel 2016 Data Models with PowerPivot Course Outline
Special Note to New Hampshire ResidentsThis course has not yet been approved by the New Hampshire Department of Education. Please contact us for an update on when the class will be available in New Hampshire.
*** 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.
Overview
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.
Prerequisites
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
WEXB16
Facebook
LinkedIn