Microsoft Excel 2013 PowerPivot

Code: 55142
Category: Microsoft Office Application Training
Course duration: 1 days
Price:

55142 - Microsoft Excel 2013 PowerPivot (1 Day)

About this Course

PowerPivot is a powerful data exploration tool based on in-memory computing technologies that provides unmatched analytical performance to process billions of rows almost instantaneously. In this Microsoft Excel 2013 PowerPivot Training course, students will learn how to use PowerPivot to import and manipulate data, create PowerPivot reports, use DAX functions, create key performance indicators (KPI) and use slicers and hierarchies to enhance the visualization of data. Exercises accompany each lesson to enhance the learning process. The courseware covers the buttons on the PowerPivot tab and accompanying tabs for creating and editing tables and charts. The power in PowerPivot is the ability to import data from multiple sources. The course provides examples using an Access database, copy and paste, text file and Excel file. In addition, calculated fields and columns are defined and used to enhance your PivotTable.

Audience Profile

This course is intended for students with a sound working knowledge of Microsoft Excel 2013 and general computing proficiency, including those who will be using Excel to make business decisions.

At Course Completion

After completing this course, students will be able to:

  • Become familiar with the PowerPivot application.
  • Develop data model while importing data from multiple sources.
  • Manipulate data in a PowerPivot worksheet.
  • Create reports using PowerPivot data.
  • Use DAX functions in PowerPivot.
  • Define KPIs (Key Performance Indicators).
  • Use slicers to enhance data visualization.

Course Outline

Module 1: Introduction to PowerPivot 2013

This module explains how to enable PowerPivot in Excel 2013. It also reviews the PowerPivot workspace and gives a brief explanation of the various actions available in the workspace.

Lessons

  • Review PowerPivot 2013
  • Explore the PowerPivot workspace
  • Compare Excel and PowerPivot

Lab : Enable PowerPivot

  • Enable PowerPivot within Microsoft Excel 2013

After completing this module, students will be able to:

  • Enable PowerPivot

Module 2: Building Data Models

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.

Lessons

  • Review Data Models
  • Create Data Models

Lab : Create a data model

  • 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

Module 3: Create a PivotTable and PivotChart

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.

Lessons

  • Determine chart or table
  • Create a PivotTable
  • Create a PivotChart

Lab : Create a basic PivotTable

  • Insert a PivotTable
  • Add field to values drop zone
  • Add field to columns drop zone
  • Add fields to rows drop zone

Lab : Use the analyze tab to change your PivotTable

  • Change the name of the PivotTable
  • Change the way amounts display
  • Explore the Show group and its effects on your PivotTable

Lab : Use the analyze tab to change your PivotTable

  • Change the name of the PivotTable
  • Change the way amounts display
  • Explore the Show group and its effects on your PivotTable

Lab : Create a basic PivotChart

  • Insert a PivotChart
  • Add field to values drop zone
  • Add field to legend drop zone

Lab : Use the analyze tab to change your PivotChart

  • Change the name of the PivotChart
  • Explore the Show group and its effects on your PivotTable

Lab : Use the design tab to change your PivotChart

  • Apply Chart Layouts
  • Apply Chart Styles
  • Apply Chart Type

Lab : Use the format tab to change your PivotChart

  • 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.

Module 4: Use DAX in PowerPivot

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.

Lessons

  • Review and define DAX
  • Discuss context used in DAX
  • Define calculated columns
  • Define calculated fields

Lab : Create a calculated column with data from the same table

  • Create a calculated column
  • Enter formula
  • Rename the column from the default name
  • Review the effect of the column on the PivotTable

Lab : Create a calculated column with data from a different table

  • 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

Lab : Create a calculated field

  • 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.

Module 5: Enhance PivotTables and PivotChartsThis 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.Lessons

  • Define key performance indicators (KPIs)
  • Define slicers
  • Define hierarchies

Lab : Create a KPI

  • Create a KPI
  • Create a new PivotTable to use the KPI

Lab : Create slicers

  • Create a new slicer
  • Move the slicer
  • Use the slicer to update the PivotTable
  • Use the slicer to update the PivotTable

Lab : Create hierarchies

  • 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.

Prerequisites:

Before attending this course, students must have:

· Some experience using Excel, including creating and using formulas and some exposure to PivotTables.

55142A - Microsoft Excel 2013 PowerPivot (1 Day)

About this Course

PowerPivot is a powerful data exploration tool based on in-memory computing technologies that provides unmatched analytical performance to process billions of rows almost instantaneously. In this Microsoft Excel 2013 PowerPivot Training course, students will learn how to use PowerPivot to import and manipulate data, create PowerPivot reports, use DAX functions, create key performance indicators (KPI) and use slicers and hierarchies to enhance the visualization of data. Exercises accompany each lesson to enhance the learning process. The courseware covers the buttons on the PowerPivot tab and accompanying tabs for creating and editing tables and charts. The power in PowerPivot is the ability to import data from multiple sources. The course provides examples using an Access database, copy and paste, text file and Excel file. In addition, calculated fields and columns are defined and used to enhance your PivotTable.

Audience Profile

This course is intended for students with a sound working knowledge of Microsoft Excel 2013 and general computing proficiency, including those who will be using Excel to make business decisions.

At Course Completion

After completing this course, students will be able to:

  • Become familiar with the PowerPivot application.
  • Develop data model while importing data from multiple sources.
  • Manipulate data in a PowerPivot worksheet.
  • Create reports using PowerPivot data.
  • Use DAX functions in PowerPivot.
  • Define KPIs (Key Performance Indicators).
  • Use slicers to enhance data visualization.

Course Outline

Module 1: Introduction to PowerPivot 2013

This module explains how to enable PowerPivot in Excel 2013. It also reviews the PowerPivot workspace and gives a brief explanation of the various actions available in the workspace.

Lessons

  • Review PowerPivot 2013
  • Explore the PowerPivot workspace
  • Compare Excel and PowerPivot

Lab : Enable PowerPivot

  • Enable PowerPivot within Microsoft Excel 2013

After completing this module, students will be able to:

  • Enable PowerPivot

Module 2: Building Data Models

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.

Lessons

  • Review Data Models
  • Create Data Models

Lab : Create a data model

  • 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

Module 3: Create a PivotTable and PivotChart

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.

Lessons

  • Determine chart or table
  • Create a PivotTable
  • Create a PivotChart

Lab : Create a basic PivotTable

  • Insert a PivotTable
  • Add field to values drop zone
  • Add field to columns drop zone
  • Add fields to rows drop zone

Lab : Use the analyze tab to change your PivotTable

  • Change the name of the PivotTable
  • Change the way amounts display
  • Explore the Show group and its effects on your PivotTable

Lab : Use the analyze tab to change your PivotTable

  • Change the name of the PivotTable
  • Change the way amounts display
  • Explore the Show group and its effects on your PivotTable

Lab : Create a basic PivotChart

  • Insert a PivotChart
  • Add field to values drop zone
  • Add field to legend drop zone

Lab : Use the analyze tab to change your PivotChart

  • Change the name of the PivotChart
  • Explore the Show group and its effects on your PivotTable

Lab : Use the design tab to change your PivotChart

  • Apply Chart Layouts
  • Apply Chart Styles
  • Apply Chart Type

Lab : Use the format tab to change your PivotChart

  • 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.

Module 4: Use DAX in PowerPivot

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.

Lessons

  • Review and define DAX
  • Discuss context used in DAX
  • Define calculated columns
  • Define calculated fields

Lab : Create a calculated column with data from the same table

  • Create a calculated column
  • Enter formula
  • Rename the column from the default name
  • Review the effect of the column on the PivotTable

Lab : Create a calculated column with data from a different table

  • 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

Lab : Create a calculated field

  • 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.

Module 5: Enhance PivotTables and PivotChartsThis 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.Lessons

  • Define key performance indicators (KPIs)
  • Define slicers
  • Define hierarchies

Lab : Create a KPI

  • Create a KPI
  • Create a new PivotTable to use the KPI

Lab : Create slicers

  • Create a new slicer
  • Move the slicer
  • Use the slicer to update the PivotTable
  • Use the slicer to update the PivotTable

Lab : Create hierarchies

  • 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.

Prerequisites:

Before attending this course, students must have:

· Some experience using Excel, including creating and using formulas and some exposure to PivotTables.

Course Reviews

No reviews found for this course.

Be the first to write a review