Excel 2016: PivotTable Data Crunching
COURSE OUTLINE:
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