Excel 2019 Pivot Tables
CLASS DATE(s):
Request a Class

COURSE LENGTH: 1/2

COURSE COST: $175.00

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

Printable version of this course
print

COURSE OVERVIEW

In this 1/2 day course you will learn how to use the Excel tool to make a pivot table.

A pivot table is a table of statistics that summarizes the data of a more extensive table such as a database, spreadsheet, or business intelligence program. This summary might include sums, averages, or other statistics, which the pivot table groups together in a meaningful way.

Pivot tables are a technique in data processing. They enable a person to arrange and rearrange (or "pivot") statistics in order to draw attention to useful information.

With a pivot table, you can transform one million rows of transactional data into a summary report in seconds. If you can drag a mouse, you can create a pivot table. In addition to quickly summarizing and calculating data, pivot tables enable you to change your analysis on the fly by simply moving fields from one area of a report to another.

AUDIENCE AND PREREQUISITES

Students taking this course are experienced Excel users who are seeking to advance their data analysis capabilities by using Pivot Tables. To ensure your success in this course, you should have experience with Excel 2016 or Excel 2019 and Pivot Tables. You should already understand spreadsheet concepts and be comfortable creating basic Pivot Tables.

PREREQUISITE COURSESFOLLOW UP COURSES  

Course cost listed does not include the cost of the courseware. The course to run is subject to a minimum enrollment. This course may run as a live on line instructor led course if the minimum enrollment is not met. For more information, please contact: learn@vtec.org or call: 207-775-0244.

COURSE TOPICS:


1. Pivot table fundamentals
Why you should use a pivot table
When to use a pivot table
Anatomy of a pivot table
Pivot tables behind the scenes
Pivot table backward compatibility

2. Creating a basic pivot table
How to create a basic pivot table
Understanding the Recommended Pivot Table and the Ideas features
Using slicers
Keeping up with changes in the data source
Sharing the pivot cache
Side effects of sharing a pivot cache
Saving time with new PivotTable tools

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
changing the calculation in a value field
Adding and removing subtotals
Formatting one cell is new in Office 365

4. Grouping, sorting, and filtering pivot data
Using the PivotTable Fields list
Sorting a pivot table
Filtering a pivot table: an overview
Using filters for row and column fields
Filtering using the Filters area
Grouping and creating hierachies in a pivot table
Creating hierarchies

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

6. Using pivot charts and other visualizations
What is a pivot chart?
Creating a pivot chart
Keeping pivot chart rules in mind
Examining alternatives to using pivot charts
Using conditional formatting with pivot tables
Creating custom conditional formatting rules

7. Analyzing disparate data sources with pivot tables
Using the Data Model
Building a pivot table using external data sources
Leveraging Power Query to extract and transform data

8. sharing dashboards with Power BI
Getting started with Power BI Desktop
building an interactive report with Power BI Desktop
Publishing to Power BI
Designing a workbook as an interactive web page
Sharing a link to a web workbook

9. Using cube formulas with the Data Model or OLAP data
Converting your pivot table to cube formulas
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 mode with cube functions
Adding calculations to OLAP pivot tables

10. Unlocking features with Data Model and Power Pivot
Replacing VLOOKUP with the Data Model
Unlocking hidden features with the Data Model
Processing big data with Power Query
Using advanced Power Pivot techniques
Overcoming limitations of the Data Model

11. Analyzing geographic data with 3D Map
Preparing data for 3D Map
Geocoding data
Building a column chart in 3D Map
Navigating through the map
Labeling individual points
Building pie or bubble charts on a map
Exploring 3D Map settings
Using heat maps and region maps
fine-tuning 3D Map
Combining two data sets
Animating data over time
Building a tour, Creating a video from 3D Map

12. Enhancing pivot table reports with macros
Using macros with pivot table reports
Recording a macro
Creating a user interface with the form controls
Altering a recorded macro to add functionality
Creating a macro using Power Query

13. Using VBA to create pivot tables
Enable VBA in your copy of Excel
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 VBA
Dealing 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
Using the Data Mode in Excel 2019

14. Advanced pivot table tips and techniques
Force pivot tables to refresh automatically
Refresh all pivot tables in a workbook at the same time
Sort data items in a unique order, not ascending or descending
Using a custom list for sorting your pivot table
Use pivot table defaults to change the behavior of all future pivot tables
Turn pivot tables into hard data
Fill the empty cells left by row fields
Add a rank number filed to a pivot table
Reduce the size of pivot table reports
Create an automatically expanding data range
Compare tables using a pivot table
AutoFilter a pivot table, Force two number formats in a pivot table