Session 1: Pivot Table Concepts
Participants will develop a solid understanding of the requirements to use pivot tables. Foundational elements participants need to understand to get the most out of pivot tables will be covered as well as the sometimes-intimidating Pivot Table Fields task pane and pivot table-specific Ribbon tabs.
Session 2: Creating a Pivot Table
Not all data is created equal. Participants will learn what’s required to make sure data is organized well enough to be used in a pivot table. Example of a very common issue that occurs when the source data changes or expands will be provided, and how to easily eliminate that issue in just a few quick clicks of the mouse.
Session 3: Pivot Table Analyze and Design Tabs
Working efficiently with pivot tables requires knowing where to find the proper pivot table feature without searching high and low for it. Most pivot table features reside on one of the two pivot table-specific Ribbon tabs. Knowing how the different features are grouped and what each one provides is a must.
Session 4: Pivot Table Formatting Inside and Out
Formatting a pivot table report can be challenging if participants do not know all the secrets. As such, how to format cells and numbers, as well as how to handle empty cells or errors in the source data will be discussed. In addition, participants will get some exposure to renaming fields and how to sort and filter data within the pivot table report.
Session 5: Pivot Table Options and Field Settings
Most users are not aware there are certain options and settings that provide little-known but extremely useful features. Most of these features are accessed through the pivot table Ribbon tabs, but some are not. For this reason, how to use some of the more obscure settings in the Pivot Table Options and Field Settings dialog boxes will be covered.
Session 6: Value Field Settings
Similar to the Field Settings, the Values section of a pivot table report have unique settings and summarization options. For most people, simply summing or counting the data within the Values section is enough. But once participants learn how to use these lesser-known settings, they will go from an average pivot table user to an expert.
Session 7: Grouping Pivot Table Fields
A pivot table is ideal for analyzing and summarizing data. By default, the table does an excellent job at summarizing the data within the various fields, but there is a way to summarize the data even further. Participants will group ordinary pivot table fields, which will create new fields that they can use within the pivot table or as a Slicer. In addition, participants will learn how to expand and collapse fields to help in summarizing the pivot table data.
Session 8: Data Integrity Checks and Report Filters
A pivot table is a great way to locate data anomalies in large data sets that would otherwise be hard to find scanning through the source table. In addition, the proper use of report filters can assist in any data investigation, as well as providing a great tool to create multiple reports from a single pivot table report.
Session 9: Cloning a Pivot Table and the Wonderful World of Slicers
Pivot tables are extremely useful and flexible for data analysis and reporting. It’s easy to add, remove, or simply move fields to create different-looking reports from the same source data. Routinely, it’s necessary to create different versions of reports, but the best way to do so isn’t as straightforward as one might think.
Session 10: Calculating Fields and Items
Pivot tables are great for analyzing and reporting information, but they are limited in their functionality when your source data is missing needed information. Or, perhaps the information is not needed in the source data and is only needed for analysis for a short time. Excel’s Calculated Items and Fields feature provides a way to create items that are missing from the source data or needed for a one-time analysis.
Session 11: Working with Slicers and Pivot Charts
Pivot table is not complete without a Slicer and pivot chart. In order to create an effective dashboard, a pivot table and pivot chart controlled by multiple Slicers is a must. Creating and managing Slicers and Pivot Charts is really quite simple and a lot of fun.
Session 12: Timelines and Dashboard Development
There are many interactive tools and techniques available to make pivot tables and pivot charts fun to use. Similar to a Slicer, Excel offers a tool called Timelines. Timelines are available when a field available in your pivot table or chart is a date. Within a dashboard, using the Timeline feature can be useful and eye catching to the users.