Learn how to use a reverse PivotTable to restructure datasets that aren't suitable for PivotTable source data, along with an introduction to Pivot Charts and some useful techniques to create PivotTables using macros.
Class outline/topics
Setting PivotTable field options (review)
Calculated fields and items
- Why use calculated fields?
- Creating, modifying and deleting calculated fields
- Limitations of calculated fields
- Calculated items vs grouping items
- Limitations of calculated items
Conditional formatting in PivotTables
- Special considerations for PivotTables
Using the GETPIVOTDATA function
- When to use it
- GETPIVOTDATA errors, facts and rules
- Using sparklines
Using external source data
- When to use external datasets
- Pivot Cache and external source data
- Create a PivotTable from to a database table
Using multiple worksheets as source data
- PivotTable Wizard
- Nuances with multiple-source PivotTables
Reverse PivotTables
- Create a simple reverse PivotTable
- Create a reverse PivotTable with multiple row fields
- Using CONCATENATE and text-to-columns in a reverse PivotTable
Introducing Pivot Charts
- What is a Pivot Chart?
- PivotTables and Pivot Charts
- Creating a Pivot Chart
- Adding chart elements
Use macros to create PivotTables
- The macro recorder
- Limitations of the macro recorder
- Create macros to create a PivotTable and a Pivot Chart
- Test and debug the macro
- Run with a keyboard shortcut
Prerequisites
Class: Mastering Excel PivotTables Level 1: PivotTable Fundamentals, or participants should be proficient in and comfortable with at least 7 of the following 10 Excel skills:
- Create and format PivotTables with multiple row, column and value fields
- Refresh a PivotTable after changing its source data
- Use the PivotTable Group-field feature
- Use the SUM and COUNT functions
- Format cells using the Format Cells dialog box
- Use AutoFilter with various criteria including greater than, less than and begins with
- Convert numbers stored as text to numbers stored as numbers
- Format cells using conditional formatting rules
- Create and format an Excel chart
- Create formulas with multiple, nested functions
