Excel 2019 Pivot Tables

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