Microsoft Excel 2013 PowerPivot

This module begins with defining a data model and how it is used to create PivotTables and PivotCharts. The Manage Data Model window is discussed in detail in this module. In addition, the various methods for adding data to the model are covered.

Review Data Models

Create Data Models

Import data from multiple data sources.

Use the manage data model tab to maintain the data model.

Refresh data used in PivotTables and PivotCharts.

After completing this module, students will be able to:

Import data from multiple data sources.

Use the manage data model tab to maintain the data model.

Refresh data used in PivotTables and PivotCharts

This module covers the difference between tables and charts and the various options that are available. In addition, this module will walk the user through creating a basic PivotTable and PivotChart. PivotTable Tools and PivotChart Tools contextual tabs are covered in this module. These tabs allow further customization to the basic PivotChart and PivotTable.

Determine chart or table

Create a PivotTable

Create a PivotChart

Insert a PivotTable

Add field to values drop zone

Add field to columns drop zone

Add fields to rows drop zone

Change the name of the PivotTable

Change the way amounts display

Explore the Show group and its effects on your PivotTable

Change the name of the PivotTable

Change the way amounts display

Explore the Show group and its effects on your PivotTable

Insert a PivotChart

Add field to values drop zone

Add field to legend drop zone

Change the name of the PivotChart

Explore the Show group and its effects on your PivotTable

Apply Chart Layouts

Apply Chart Styles

Apply Chart Type

Apply Shape Fill format

Apply Shape Effects format

Apply WordArt Style format

After completing this module, students will be able to:

Create and update a PivotTable.

Create and update a PivotChart.

Use the contextual tabs to customize PivotTables and PivotCharts.

This module introduces DAX - Data Analysis eXpressions language and its use in creating complex calculations to be used in PivotTables or PivotCharts. DAX operators and formulas are discussed. Calculated columns and calculated fields are defined and used in this module.

Review and define DAX

Discuss context used in DAX

Define calculated columns

Define calculated fields

Create a calculated column

Enter formula

Rename the column from the default name

Review the effect of the column on the PivotTable

Create a calculated column

Use Related expression to link data from another table

Rename the column from the default name

Review the effect of the column on the PivotTable

Add calculated field using AutoSum

Add calculated field by entering formula

After completing this module, students will be able to:

Use DAX to create complex calculations.

Create and use a calculated column in a PivotTable/PivotChart.

Create and use a calculated field in a PivotTable/PivotChart.

Understand the various functions that DAX provides.

This module covers using KPIs (Key Performance Indicators), slicers and hierarchies to further enhance your PivotCharts and PivotTables. In addition the slicer tools options contextual tab is covered. This tab allows customization to the display of the slicers.

Define key performance indicators (KPIs)

Define slicers

Define hierarchies

Create a KPI

Create a new PivotTable to use the KPI

Create a new slicer

Move the slicer

Use the slicer to update the PivotTable

Use the slicer to update the PivotTable

Create a new hierarchy using two different methods

Add the hierarchies to the PivotTable

Review the effect of the hierarchies on the PivotTable

After completing this module, students will be able to:

Create and use KPIs in a PivotTable/PivotChart.

Create and use slicers in a PivotTable/PivotChart.

Create and use hierarchies in a PivotTable/PivotChart.