Excel 2016 Pivot Table

Course Details

• Pivot Table Fundamentals
• Defining a Pivot Table
• When to Use a Pivot Table
• Anatomy of a Pivot Table
• Pivot Tables Behind the Scenes
• Pivot Table Backward Compatibility
• Next Steps
• Lesson 2: Creating a Basic Pivot Table
• Preparing Data for Pivot Table Reporting
• How to Create a Basic Pivot Table
• Understanding the Recommended Pivot Table Feature
• Using Slicers
• Keeping Up with Changes in the Data Source
• Sharing the Pivot Cache
• Saving Time with New Pivot Table Tools
• Lesson 3: Customizing a Pivot Table
• Making Common Cosmetic Changes
• Making Report Layout Changes
• Customizing a Pivot Table’s Appearance with Styles and Themes
• Changing Summary Calculations
• Adding and Removing Subtotals
• Changing the Calculation in a Value Field
• Lesson 4: Grouping, Sorting, and Filtering Pivot Data
• Automatically Grouping Dates
• Using the PivotTable Fields List
• Sorting in a Pivot Table
• Filtering a Pivot Table: An Overview
• Using Filters for Row and Column Fields
• Filtering Using the Filters Area
• Lesson 5: Performing Calculations in Pivot Tables
• Introducing Calculated Fields and Calculated Items
• Creating a Calculated Field
• Creating a Calculated Item
• Understanding the Rules and Shortcomings of Pivot Table Calculations
• Managing and Maintaining Pivot Table Calculations
• Lesson 6: Using Pivot Charts and Other Visualizations
• What Is a Pivot Chart...Really?
• Creating a Pivot Char
• Keeping Pivot Chart Rules in Mind
• Examining Alternatives to Using Pivot Charts
• Using Conditional Formatting with Pivot Tables
• Creating Custom Conditional Formatting Rules
• Lesson 7: Analyzing Disparate Data Sources with Pivot Tables
• Using the Internal Data Model
• Building a Pivot Table Using External Data Sources
• Leveraging Power Query to Extract and Transform Data
• Lesson 8 Sharing Pivot Tables with Others
• Designing a Workbook as an Interactive Web Page
• Sharing a Link to a Web Workbook
• Sharing with Power BI
• Lesson 9: Working with and Analyzing OLAP Data
• Introduction to OLAP
• Connecting to an OLAP Cube
• Understanding the Structure of an OLAP Cube
• Understanding the Limitations of OLAP Pivot Tables
• Creating an Offline Cube
• Breaking Out of the Pivot Table Mold with Cube Functions
• Adding Calculations to OLAP Pivot Tables
• Lesson 10: Mashing Up Data with Power Pivot
• Understanding the Benefits and Drawbacks of Power Pivot and the Data Model
• Joining Multiple Tables Using the Data Model in Regular Excel 2016
• Using the Power Pivot Add-in Excel 2016 Pro Plus
• Understanding Differences Between Power Pivot and Regular Pivot Tables
• Using DAX Calculations
• Lesson 11: Dashboarding with Power View and 3D Map
• Preparing Data for Power View
• Creating a Power View Dashboard
• Replicating Charts Using Multiples
• Showing Data on a Map
• Using Images
• Changing a Calculation
• Animating a Scatter Chart over Time
• Some Closing Tips on Power View
• Analyzing Geographic Data with 3D Map
• Lesson 12:` Enhancing Pivot Table Reports with Macros
• Why Use Macros with Pivot Table Reports
• Recording a Macro
• Creating a User Interface with Form Controls
• Altering a Recorded Macro to Add Functionality
• Lesson 13: Using VBA to Create Pivot Tables
• Enabling VBA in Your Copy of Exce
• Using a File Format That Enables Macros
• Visual Basic Editor
• Visual Basic Tools
• The Macro Recorder
• Understanding Object-Oriented Code
• Learning Tricks of the Trade
• Understanding Versions
• Building a Pivot Table in Excel VBADealing with Limitations of Pivot Tables
• Pivot Table 201: Creating a Report Showing Revenue by Category
• Calculating with a Pivot Table
• Using Advanced Pivot Table Techniques
• 14 Advanced Pivot Table Tips and Techniques
• Tip 1: Force Pivot Tables to Refresh Automatically
• Tip 2: Refresh All Pivot Tables in a Workbook at the Same Time
• Tip 3: Sort Data Items in a Unique Order, Not Ascending or Descending
• Tip 4: Turn Pivot Tables into Hard Data
• Tip 5: Fill the Empty Cells Left by Row Fields
• Tip 6: Add a Rank Number Field to a Pivot Table
• Tip 7: Reduce the Size of Pivot Table Reports
• Tip 8: Create an Automatically Expanding Data Range
• Tip 9: Compare Tables Using a Pivot Table
• Tip 10: AutoFilter a Pivot Table
• Tip 11: Force Two Number Formats in a Pivot Table
• Tip 12: Create a Frequency Distribution with a Pivot Table
• Lesson 15: Dr. Jekyll and Mr. GetPivotData
• Avoiding the Evil GetPivotData Problem
• Using GetPivotData to Solve Pivot Table Annoyances