Excel 2016: PivotTable Data Crunching

COURSE OUTLINE:

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