Excel 2019 Pivot Tables
Request a Class
9:00am - 12:00pm
In this 1/2 day course you will learn how to use the Excel tool to make a pivot table.
AUDIENCE AND PREREQUISITES
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.
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: firstname.lastname@example.org or call: 207-775-0244.
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 compatibility2. 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 tools3. 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 3654. 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 hierarchies5. 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 calculations6. 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 rules7. 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 data8. 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 workbook9. 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 tables10. 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 Model11. 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 Map12. 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 Query13. 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 201914. 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