Excel 2013: Pivot Tables

Introduction

• Welcome
• What you should know before watching this course
• Using the exercise files

1. Creating and Pivoting PivotTables

• Introducing PivotTables
• Formatting data for use in a PivotTable
• Creating a PivotTable
• Creating a Recommended PivotTable
• Pivoting a PivotTable
• Configuring a PivotTable
• Connecting to an external data source
• Consolidating data from multiple sources
• Managing PivotTables

2. Summarizing PivotTable Data

• Managing subtotals and grand totals
• Changing the data field summary operation
• Summarizing more than one data field
• Creating a calculated field
• Grouping PivotTable fields
• Using PivotTable data in a formula
• Drilling down to the underlying data

3. Sorting and Filtering PivotTable Data

• Sorting PivotTable data
• Creating a custom sort order
• Filtering a PivotTable field by selection
• Filtering a PivotTable by rule
• Filtering a PivotTable using a search filter
• Filtering a PivotTable using slicers
• Formatting slicers
• Filtering with report filter fields
• Clearing and reapplying PivotTable filters

4. Formatting PivotTables

• Applying a PivotTable style
• Creating a PivotTable style
• Changing the PivotTable layout
• Changing the data field number format

5. Applying Conditional Formats to PivotTables

• Highlighting cells by applying a rule
• Highlighting the top or bottom values in a PivotTable
• Formatting cells using data bars
• Formatting cells using color scales
• Formatting cells using icon sets
• Editing a conditional formatting rule
• Controlling how multiple rules are applied
• Deleting a conditional formatting rule

6. Creating and Manipulating PivotCharts

• Creating a PivotChart
• Pivoting a PivotChart
• Filtering a PivotChart
• Formatting a PivotChart
• Changing a PivotChart's layout
• Changing a PivotChart's chart type
• Adding a trendline to a PivotChart

7. Printing PivotTables

• Printing a PivotTable
• Printing headers at the top of each printed page
• Printing each item on its own page
• Printing a PivotChart

8. Manipulating PivotTables Using Macros

• Recording and reviewing an Excel macro
• Running an Excel macro
• Creating a simple PivotTable presentation kit

9. Starting with PowerPivot and the Data Model

• Enabling PowerPivot in Office 2013 Professional Plus
• Introducing PowerPivot
• Importing PowerPivot data
• Adding tables to the data model
• Managing table columns
• Creating relationships between tables
• Creating a PowerPivot PivotTable
• Filtering data using a timeline

10. Introducing Data Analysis Expressions (DAX)

• Introducing the DAX language
• Using DAX operators
• Surveying DAX functions
• Adding calculated columns
• Adding calculated fields
• Creating aggregate calculations
• Creating filtered calculations

11. Visualizing Power View Data Using Matrices, Cards, and Tiles

• Starting out with Power View
• Installing Microsoft Silverlight
• Creating a table or matrix
• Creating a card
• Creating a tile
• Filtering Power View objects
• Formatting Power View objects

12. Visualizing Power View Data Using Charts and Maps

• Creating a column or bar chart
• Creating a pie chart
• Creating a line chart
• Creating a map
• Creating maps with multivalue series
• Creating chart multiples

Conclusion

• Next steps