Duration
2 days
Course Description
This course is intended for users of Microsoft Office Excel who want to learn about the features of Microsoft Office Excel PivotTable and users that want to gain familiarity with PowerPivot to build compelling reports.
Module 1: Overview
A simple introduction module.
After completing this module, students will be able to:
Understand your course, classroom, classmates, facility and instructor.
Module 2: Manipulating the PivotTable
This module explains how to rearrange the layout of the PivotTable.
Lessons
Rearranging the Layout
Adding New Field
Renaming a Field
Formatting Numbers
Changing Sorting Order
Showing Top 10
Refreshing Data
After completing this module, students will be able to:
Rearrange the layout of the PivotTable
Adding, renaming New Field within the PivotTable
Formatting Number in the PivotTable reports
Changing Sorting Order
Showing Top 10 values
Refreshing Source data within the PivotTable
Module 3: Digging Deeper
This module explains how to group item, display subtotals and grand totals within the PivotTable. It also introduces the feature of Slicer to display item in PivotTable.
Lessons
Grouping Items
Displaying Subtotals
Hiding/Showing Grand Totals
Formatting PivotTable
Using Slicer
Formatting Slicer
After completing this module, students will be able to:
Display grouping and subtotals for PivotTable
Formatting PivotTable
Organize PivotTable through the use of Slicer
Module 4: Beyond the Basics
This module explains how to create a second data Field and changing the summary function with the PivotTable.
Lessons
Creating Second Data Field
Changing the Summary Function
Displaying Numerical Value as a Percentage of the Total
Creating a Custom Calculation
More Function in Custom Calculation
After completing this module, students will be able to:
Create a Second Data Field within the PivotTable
Changing Summary Function
Displaying Numerical Value as a Percentage of the Total
Create custom calculation within the PivotTable
Module 5: More PivotTable Calculations
This module explains how to insert formula within the PivotTable.
Lessons
Using Formulas
Creating a Calculated Field
Creating a Calculated Item
Using GetPivotData Function
Turn o GetPivotData Function
After completing this module, students will be able to:
Use Formulas with the PivotTable
Create a calculated eld and item
Using GetPivotData Function
Turn o GetPivotData Function
Module 6: PowerPivot Introduction
In this module we take a look at the lessons of the module.
Lessons
PowerPivot Introduction
Lab : Installing PowerPivot
Install PowerPivot for Excel
After completing this module, students will be able to:
Install PowerPivot for Excel
Be able to navigate the PowerPivot windows
Understand the new features of Excel 2010
Module 7: PowerPivot Data Sources
In this module we take a look at the lessons of the module.
Lessons
Supported Data Sources
Lab : Loading Data Sources
Load Data From SQL Server
Load Data From Text Files
Load Data via Copy and Paste
Load Data via Data Feeds
Load Data via Reporting Services
Lab : Cleaning Data
Cleaning data in PowerPivot
Lab : Creating Relationships
Create Relationship between Data Sources
After completing this module, students will be able to:
Import various data sources into PowerPivot
Understand how to clean imported data
Module 8: PowerPivot Functions
In this module we take a look at the lessons of the module.
Lessons
PowerPivot Expressions
Lab : Create a Time Table
Create a Time Table for future labs
Lab : Using DATE Functions
Use DATE Function
Use DATEVALUE Function
Use the DAY, MONTH, YEAR, HOUR, MINUTE, SECOND Functions
Use EDATE Function
Use EOMONTH Function
Use NOW and TODAY Functions
Use TIME and TIMEVALUE Functions
Lab : Using TIME INTELLIGENCE Functions
Use FIRST* and LAST* Functions
Use DATE* Functions
Use STARTOF* and ENDOF* Functions
Use CLOSING* and OPENING* Functions
Use LASTOF* and FIRST* Functions
Use PREVIOUS* and NEXT* Functions
Lab : Using STATISTICAL Functions
Use AVERAGE* Functions
Use COUNT* Functions
Use MAX* and MIN* Functions
Lab : Using VALUE Functions
Use ALL, ALLEXCEPT and ALLNOBLANKROW
Use CALCULATE, CALCULATETABLE
Use DISTINCT
Use EARLIER and EARLIEST
Use FILTER
Use REALTED, RELATEDTABLE
Use VALUES
Lab : Using LOGICAL and INFORMATION Functions
Use Logical Functions (AND, FALSE, IF, IFERROR, NOT, OR, TRUE)
Use Information Functions (ISBLANK, ISERROR, ISLOGICAL, ISNONTEXT, ISNUMBER, ISTEXT)
Lab : Using TEXT Functions
Use TEXT Functions
After completing this module, students will be able to:
Be able to describe all the new PowerPivot Expressions
Use and apply many of the PowerPivot Expressions
Module 9: Building Reports using PowerPivot
In this module we take a look at the lessons of the module.
Lessons
PowerPivot Reports
Lab : Creating Reports
Use PivotCharts
Use Slicers
Use Sparklines
After completing this module, students will be able to:
Describe the di erent Report types in PowerPivot
Be able to create Slicers and Sparklines for more engaging reports
Programme scope, delivery arrangements and suitability are confirmed during enquiry.