Course Details
Course Duration - 1 - 2 days
Course Objectives
Microsoft PowerPivot is an add on for Excel that provides Business Intelligence functionality & reporting within the familiar environment of Excel. PowerPivot provides the real power to crunch & analyse data on a scale previously unimaginable with pivot tables. Now it is possible to process millions of rows of data in Excel enabling deeper business insight and shorter decision making cycles.
This course is suitable for any existing Excel user who has to manipulate, analyse and report on massive amounts of data. At the end of this course delegates will have the skills required to start using PowerPivot. Learn how to leverage their existing PivotTable knowledge in Excel with PowerPivot and understand how PowerPivot might be applied back in their workplace.
The course will show delegates how to leverage their existing PivotTable knowledge in Excel with PowerPivot.; Enable delegates to better understand how PowerPivot might be applied back in their workplace.
1. Getting started with PowerPivot
- Start Excel & launch the PowerPivot window
- Navigating the PowerPivot window
- Exploring the PowerPivot tab and field list in Excel
2. Adding data to PowerPivot
- Data sources and types supported in PowerPivot workbooks
- Importing data (from pre-prepared file)
- Add Data by using Excel Linked Tables
- Using Power Query
3. Preparing data for analysis
- Working with Tables and Columns
- Filtering and Sorting Data
- Creating Relationships Between Tables
- Creating and Working with calculations
4. Data; formatting and layout
- Creating, deleting a table
- Rename a Table or Column
- Set the Data Type of a Colum
- Hide or Freeze Columns
- Udo or Redo an Action
- Sorting and filtering data in a table
5. PowerPivot and relationships
- Understanding Relationships
- Create a Relationship Between Two Tables
- View and Edit Relationships
- Delete Relationships
- Troubleshoot Relationship
6. Calculations in PowerPivot
- Overview of Data Analysis Expressions (DAX) language
- Building Formulas for Calculated Columns and Measures
- Understanding the Use of Relationships and Lookups in Formulas
- Understanding Aggregations in Formulas
- Filtering Data in Formulas
- Recalculating Formulas
7. Creating PivotTables, Charts & Reports
- Create a PivotTable or PivotChart Report
- Create a Reporting Services Report with PowerPivot Data
- Create a Measure in a PivotTable or PivotChart
- Create and Change the Field Layout in a PivotTable or PivotChart Report
- Delete a PivotTable or PivotChart Report
- Filter Data using Slicers
- Work with Relationships in PivotTables
- Change the Display Language
8. Overview of Key Performance Indicators (KPI’s) in PowerPivot
- Retail sales KPI case study
- Sales per hour, average
- Conversion rates
- Wage to sales ratio
After This Course
You will have the skills required to start using PowerPivot. The course will you how to leverage their existing PivotTable knowledge in Excel with PowerPivot. You will understand how PowerPivot can be applied back in your workplace.
Prerequisites
A very good knowledge of Excel, including a working knowledge of PivotTables and an understanding of nested functions.