PowerPivot Fundamentals

PowerPivot Courses & Corporate Training in NYC

This PowerPivot group class concentrates on how to create powerful Pivot Tables through multiple table relationships.

By the end of the Excel PowerPivot training course, you will feel confident setting up relationships, creating and displaying complex Pivot Tables and using Calculated DAX columns.

This class is perfect for those that have a fundamental understanding of Excel and using Excel Pivot tables and want to see more correlation between data. Those in the financial, real-estate or data analysis field will especially find this class enjoyable.

Pivot Tables & PowerPivot

$299 7 Hours In-person classroom training

Corporate training available

Call 212-658-1918 or email us

What You'll Learn

  • Benefits of PowerPivot    
  • Creating Relationships
  • Creating 4 or more related pivot tables
  • Asymmetrical Reports
  • icon for small classesSmall classes
  • icon for small classesComputer provided
  • icon for small classesBook included
  • icon for small classesFree retake

Full Course Syllabus

Download PDF Outline

Why PowerPivot?

Benefits of PowerPivot

Installing PowerPivot

Overview of PowerPivot Ribbon

Working with different data sources

Importing Data

Creating Relationships

Linking Tables

Calculated Columns

Difference between Excel's Compact Pivot Tables and Power Pivot's Flat File Tables

Using Slicers

Creating 4 or more related pivot tables

Available functions and their power

Advanced Filtering

Asymmetrical Reports

Other PowerPivot capabilities

What is PowerPivot and why should I learn it?

PowerPivot is an Excel add-in that allows the user to analyze much greater quantities of data, as well as build sophisticated data models.  

PowerPivot provides several unique advantages over using Excel's standard features.  A few of these include:

Multiple Sources for Pivot Tables

Generally speaking, in Excel, we are limited to using a single dataset as the source of a Pivot Table (we will exclude the internal data model from this discussion).  But with PowerPivot, you can select different data sets, create relationships (effectively creating a relational database), and build a single Pivot Table from these multiple sources.

Power to Handle More Data

There are approximately 1,048,576 rows (and 16,384 columns) available on every Excel worksheet.  This is more than most of us would ever need, but for some, it is not enough.  Excel users that work with large sets of data generally agree that once data goes beyond 700,000 rows, you run the risk of Excel crashing on you.  PowerPivot, on the other hand, increases row capacity up to 100 million rows of data, thereby vastly increasing the amount of data that can be collected and analyzed.  The more data collected, the more accurate your projection.

Special DAX functions

PowerPivot incorporates both Excel functions, as well as DAX (Data Analysis eXpressions) functions.  DAX is a special type of programming that increases the specificity of potential results.  One example of a DAX function is CALCULATE, which works like a SUMIFS in Excel, except you can expand beyond the standard SUM operation to include any function with the filter effect added to it.

Simplicity of Calculations

In PowerPivot, two types of calculations are possible: Calculated Columns and Measures.  A Calculated Column is similar to creating a Calculated Field in your Pivot Table.  Calculated Fields is an area of Excel that never received much investment by Microsoft, so it remains somewhat archaic, unpolished, and largely ignored by most Excel users.  Calculated Columns in PowerPivot, on the other hand, are much more progressive.  Measures are aggregate calculations that exist solely for Pivot Tables

With both Calculated Columns and Measures, PowerPivot employs a very easy-to-use auto-population method.  Not only does the function name appear in a dropdown when you begin to type (like regular Excel), but the table name and column name appear in a similar fashion, making it incredibly easy to enter calculations.

The Engine for BI

Data Analytics is a new, trendy method of gathering large amounts of transactional data and then performing powerful analyses on this data for Business Intelligence (BI) purposes.  PowerPivot serves as Microsoft’s BI engine.  PowerPivot collects, organizes, and analyzes your data.  You can then create Pivot Tables and Pivot Charts or go beyond this and use Power BI to create amazing visuals and dashboards.

The list of advantages that PowerPivot offers is a long one, but we will stop here for now.  Remember, that PowerPivot is a free add-in for Excel that works closely with Excel to create powerful analyses.  Enabling PowerPivot means that you can open up your data to more variety of sources, greater quantities, specialty functions, ease of calculating, and BI-related visuals.  PowerPivot does not replace Excel, it works with Excel and greatly enhances it.