Course Code: 5714

Excel 2019 Pivot Tables

Class Dates:
1/10/2020
11/1/2019
12/6/2019
2/14/2020
Length:
1/2 Days
Cost:
$175.00
Class Time:
Technology:
office, Business
Delivery:

Overview

  • 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
  • Students taking this course are experienced Excel users who are seeking to advance their data analysis capabilities by using Pivot Tables.

Prerequisites

  • 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.
  • Recommended Courses:

  • Excel 2016 Level 1
  • Excel 2016 Level 2
  • Excel 2016 Level 3

Course Details

  • 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