Excel 2016: PivotTable Data Crunching

Description PivotTables may be Excel's most powerful feature, but Microsoft has estimated that only 15% of Excel users take advantage of them. That's because PivotTables (and their companion feature, PivotCharts) have a reputation for being difficult to learn. This course goes beyond merely explaining PivotTables and PivotCharts and includes step-by-step lessons using realistic business scenarios that any Excel user can understand.

Learning Objectives

• Creating PivotTables
• Customizing PivotTables
• Changing the way you view PivotTables
• Performing calculations
• Using PivotCharts and other visualizations
• Analyzing multiple data sources
• Sharing PivotTables with others
• Working with and analyzing OLAP data
• Making the most of Excel 2010's powerful new PowerPivot feature
• Using Excel 2013's Slicer to dynamically filter PivotTables
• Enhancing PivotTables with macros and VBA code

1.Pivot Table Fundamentals

• Value, Row, Column and Report Filter Area
• Pivot Tables Behind the Scenes
• Limitations of Pivot Table reports

2.Creating a Pivot table

• Preparing Data for Pivot Table Reporting
• Ensure Data Is in a Tabular Layout
  
• Apply Appropriate Type formatting to Fields
  
• Summary of good Data Source Design
  
• Adding Fields to Report
  
• Adding Layers to a Pivot Table
  
• Creating a report Filter
  
• Slicers
  
• Data Sources Range has Expanded
  
• Sharing the Pivot Cache
  
• Saving Time with Pivot Table Tools
  

3.Customizing a Pivot Table

• Making common Cosmetic Changes
  
• Customizing the Pivot Table Appearance with Styles and Themes
  
• Changing Summary Calculations
• Adding and Removing Sub totals
• Using Running Total, % Rank Options

4.Grouping Sorting, and Data Filtering Pivot Data

• Grouping Pivot Fields
• Looking at the Pivot Tables Field List
• Sorting in a Pivot Table
• Filtering the Pivot Table
• Filtering using the Report Filter Area

5.Performing Calculations within Pivot Tables

• Calculated Fields and Calculated Items
• Creating calculated Fields
• Creating calculated Item
• Managing and Maintaining Pivot Table Calculations

6.Using Pivot Charts and Other Visualizations

• How changes in underlying Pivot Tables Affect Pivot Charts
• Examining Alternatives to Using Pivot Charts
• Use Cells Linked to the Pivot Table as the Source Data
• Turn the Pivot Table into Hard Values

7.Analyzing Disparate Data Sources with the Pivot Tables

• Analyzing the Anatomy of a Multiple Consolidation Range Pivot Table
• Building a Pivot Table Using External Data Sources
• Building a Pivot Table with the Microsoft Access Data

8.Working with and Analyzing OLAP Data

• Understanding the structure of OLAP Cube
• Understanding the Limitations of OLAP Pivot Tables
• Connecting to an OLAP Cube
• Creating off-line Cubes
• Breaking out the Pivot Table Mold with Cube Functions

9.Mashing Up data with PowerPivot

• Benefits and Drawbacks to PowerPivot
• Installing PowerPivot
• Building a PowerPivot Report
• Two Kinds of DAX Calculations
• Slicers in PowerPivot
• Add Excel Data By Linking
• Define relationships
• Mix in Those Amazing Time Intelligence Functions
• Getting Your Data into PowerPivot with SQL Server
• Report Formatting