Introduction
Elements of this syllabus are subject to change.
This one-day instructor-led course provides students with the knowledge and skills to examine and manipulate raw data through the use of Microsoft Office Excel PivotTables and PivotCharts.
Audience
This course is intended for Information Workers.
At Course Completion
After completing this course, students will be able to:
Understand the benefits of PivotTable Reports.
Convert raw data in an Excel worksheet into meaningful PivotTables and PivotCharts.
Manipulate the PivotTable layout.
Use grouping.
Create custom calculations.
Use formulas and create formulas outside of a report.
Create a PivotChart and publish it to a Web site.
Lesson 1: Make Your Data Work for You
This module provides an overview of the benefits of PivotTable reports and how you can create one with ease.
Topics
Maximize the Power of PivotTable
Ask the Questions
Get the Answers
Review
After completing this module, students will be able to:
Understand what needs to be done before creating a PivotTable.
Understand the ways you can use PivotTables to get the type of data you need.
Lesson 2: A Quick Tour
This module explains how to take raw data in an Excel worksheet and make it more meaningful with PivotTables.
Topics
Determine What You Need to Know
The Source is Important
Ready, Set, Go
The PivotTable Layout
What Goes Where
Result of Our PivotTable
Viewing Our Data Differently
Review
Practice
Practice 2
:: Course 50090A ::
After completing this module, students will be able to:
Prepare an Excel worksheet before using the PivotTable wizard.
Use the PivotTable and PivotChart wizard.
Understand the PivotTable layout, and which fields need to be dragged to which area of the layout.
Perform a simple PivotTable manipulation to change the way data is presented.
Lesson 3: Manipulating the PivotTable
This module explains how to rearrange the PivotTable layout, work with fields, and refresh data.
Topics
Rearrange the Layout
What We Get
Add a New Field
Remove a Field
Add or Remove a Field Using the Wizard
Rename a Field
Format Numbers
Change the Sort Order and Top 10 AutoShow
Refresh Data
Review
Practice
Practice 3
After completing this module, students will be able to:
Manipulate fields of a PivotTable to view raw data in different ways.
Change the format of data in a PivotTable.
Use the Sort and Top 10 command.
Update a PivotTable report when the source data changes.
Lesson 4: Digging Deeper
This module explains how to use grouping to further summarize PivotTable information, how to work with subtotals and grand totals, and how to format the finished PivotTable.
Topics
Create a Quarterly Group
Group Data Manually
Display Subtotals
Hide/Display Grand Total
Format a PivotTable Report
Review
Practice
Practice 4
After completing this module, students will be able to:
Group and ungroup data.
Group data manually.
Create a subtotal field.
Hide and display grand totals.
Format a finished PivotTable for readability.
Lesson 5: Beyond the Basics
This module explains how to create additional data fields and create and use custom calculations.
Topics
Creating a Second Data Field
Use Count to Summarize Data
Display Numerical Data as a Percentage of the Total
Create a Custom Calculation
:: Course 50090A ::
More Functions in Custom Calculation
Review
Practice
Practice 5
After completing this module, students will be able to:
Add data fields.
Use the Count function to get more detailed information about sum totals.
Display numerical data as a percentage of the total.
Create custom calculations.
Lesson 6: More PivotTable Calculations
This module explains how to create new fields and items using the Calculated Field and Calculated Item formulas, and how to create formulas outside of a report.
Topics
Using Formulas
Create a Calculated Field
Create a Calculated Item
Use PivotTable Data Outside the Report
Use the GETPIVOTDATA Function
Turn Off the GETPIVOTDATA Function
Review
Practice
Practice 6
After completing this module, students will be able to:
Use formulas to create new fields and items.
Create and use calculated fields.
Create and use calculated items.
Use PivotTable data outside the report.
Use the GETPIVOTDATA function.
Lesson 7: Optional Topics
This module explains how to create a PivotChart report and publish it to a Web site, add fields to a PivotTable browser, use a PivotTable list, and use multiple ranges as source data.
Topics
Create a PivotChart Report
Publish PivotTable Reports to the Web
Add Fields to a PivotTable Browser
Use a PivotTable List
Use Multiple Ranges as Source Data
Review
Practice
Practice 7
After completing this module, students will be able to:
Use the PivotTable and PivotChart wizard to create a PivotChart.
Enhance the look of a PivotChart by using the Vary Colors By Point option.
Share a PivotChart with colleagues by publishing it to a Web site.
Describe the difference between a PivotTable and a PivotTable List.
Use data from different worksheets and ranges.
Programme scope, delivery arrangements and suitability are confirmed during enquiry.