PowerPivot forExcel

About the Course

What is PowerPivot?

  • PowerPivot enables users to analyse large data blocks quickly and efficiently, and then report summary data in a user-friendly, visual format.
  • Creation of dashboards for data reporting is faster, and easy to adjust by using Slicers and Sparklines.
  • Database users, such as SAP, Salesforce, Oracle, and other large data generators, can now import more than the 1,048,576 rows of data that Excel is limited to.
  • Joining data in Excel with multiple VLOOKUP functions becomes a thing of the past, as PowerPivot creates joins automatically, allowing data to be reported from many sources effortlessly.
  • PowerPivot is fast becoming a must-have Excel 2010/2013/2016 tool for Excel users. It is a downloadable Add-In for Excel 2010, and is already built into Excel 2013 and 2016.

Course Description:
This 1-day Instructor led course teaches you how to use PowerPivot to access data sources, create relationships, use the PowerPivot Data Analysis Expressions, (DAX), expressions and generate and format dashboard reports.

Who should attend?:
Intermediate or Advanced Excel users, data analysts that are familiar with standard PivotTables in Excel, database users that wish to analyse large volumes of data, or Excel users that are reliant on VLOOKUP functions to join data.

Prerequisites:
Before attending this course, participants must be familiar with creating a basic PivotTable in Excel, be familiar with creating basic functions, such as SUM, MAX, COUNT, and be familiar with creating simple Excel charts.

Course Outline

Lesson 1: PowerPivot Data Sources

  • Supported Data Sources

Loading Data Sources

  • Load Data via Copy and Paste
  • Load Data From Text Files
  • Load Data via Importing from databases

Cleaning Data

  • Cleaning data in PowerPivot

Creating Relationships

  • Create Relationship between Data Sources

After completing Lesson 1, you will be able to:

  • Import various data sources into PowerPivot
  • Understand how to clean imported data

Lesson 2: PowerPivot Functions
Using Functions

  • Use various DATE Function
  • Use AVERAGE
  • Use COUNT
  • Use MAX and MIN Functions
  • Use ALL, and ALLEXCEPT
  • Use CALCULATE
  • Use DISTINCT
  • Use FILTERs
  • Use RELATED, and RELATEDTABLE

After completing this lesson, you will be able to:

  • Describe all the new PowerPivot Expressions
  • Use and apply many of the PowerPivot Expressions

Lesson 3: Building Reports using PowerPivot
Creating Reports

  • Use PivotCharts
  • Use Slicers
  • Use Sparklines

After completing this lesson, you will be able to:

  • Describe the different Report types in PowerPivot
  • Be able to create Slicers and Sparklines for more engaging reports