Excel 2016 Pivot Table
CLASS DATE(s):
7/10/2020
8/7/2020
9/11/2020
10/2/2020

COURSE LENGTH: 1/2

COURSE COST: $175.00

COURSE TIMES: 9:00am - 12:00pm

Printable version of this course
print
Register for this course
register

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 AND PREREQUISITES

Students taking this course are experienced Excel users who are seeking to advance their data analysis capabilities by using PivotTables.

To ensure your success in this course, you should have experience working with Excel® 2016 and PivotTables. You should already understand spreadsheet concepts and be comfortable creating basic PivotTables.

PREREQUISITE COURSESFOLLOW UP COURSES  

*Course cost listed does not include the cost of courseware or exam . Please contact us at learn@vtec.org or 207-775-0244 for additional pricing information, or if you have any questions. Course is subject to a minimum enrollment to run. Course may run as a virtual instructor led class if the minimum enrollment is not met. Students can connect virtual into a class if travel is an issue. If students connect virtually, VTEC is not responsible for the internet connection. Please verify that you have a good internet connection.

COURSE TOPICS:


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