Course Code: 1330

Excel 2016 Pivot Table

Class Dates:
1/4/2019
12/7/2018
2/8/2019
3/8/2019
Length:
1/2 Days
Cost:
$175.00
Class Time:
Technology:
Office
Delivery:

Overview

  • Course Overview
  • Advances in technology have made it possible to store ever increasing amounts of data. Along with this, the need to analyze that data and gain actionable insight is greater than ever. You already have experience working with Excel and creating basic PivotTables to summarize data. But, Excel is capable of doing much more. You will not only be able to summarize data for you to analyze, but also organize the data in a way that can be meaningfully presented to others. This leads to data-driven business decisions that have a better chance for success for everyone involved.

    Course Objectives:
    • Upon successful completion of this course, you will be able to use Excel 2016 advanced PivotTable functionality to analyze your raw data.
    • Prepare data for PivotTable reporting and create PivotTables from various data sources.
    • Analyze Data Using PivotTables.
    • Work with PivotCharts.
  • Audience
  • Students taking this course are experienced Excel users who are seeking to advance their data analysis capabilities by using PivotTables.

Prerequisites

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
  • 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