One Day • Instructor-Led
This 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 aimed at information workers who need to learn more in-depth techniques of using PivotTables.
Prerequisites:
Before attending this course, students must have:
- Taken the Windows XP Level 1 course or have the basic skills and knowledge of Windows applications
- Be an intermediate user of Microsoft Excel
Objectives:
- 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
Course Outline:
Lesson 1: Make Your Data Work for You
- Maximize the Power of PivotTable
- Ask the Questions
- Get the Answers
Lesson 2: A Quick Tour
- 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
Lesson 3: Manipulating the PivotTable
- Rearrange the Layout
- What We Get
- Add a New Field
- Remove a Field
- Rename a Field
- Format Numbers
- Change the Sort Order and Top 10 AutoShow
- Refresh Data
Lesson 4: Digging Deeper
- Create a Quarterly Group
- Group Data Manually
- Display Subtotals
- Hide/Display Grand total
- Format a PivotTable Report
- Use a Slicer
- Format a Slicer
Lesson 5: Beyond the Basics
- Creating a Second Data Field
- Use Count to Summarize Data
- Display Numerical Data as a Percentage of the Total
- Create a Custom Calculation
- More Functions in Custom Calculation
Lesson 6: Move PivotTable Calculations
- Using Formulas
- Create a Calculated Field
- Create a Calculated Item
- Use PivotTable Data Outside the Report
- Use GETPIIVOTDATA Function
- Turn off GetPivotData Function
Lesson 7: Optional Topics
- Create a PivotChart Report
- Publish PivotTable Reports to the Web
- Use Multiple Ranges as Source Data