Course Description

PivotTables are powerful data analysis tools, yet most Excel users do not use them to their fullest potential. This class shows you why PivotTables are so versatile for data analysis and how you can leverage PivotTables to rapidly spot trends and make business decisions on mountains of data.

Prerequisites

Experience in using basic Excel.

Course Outline

Module 1: PivotTable

A brief introduction to what a Pivot Table is, when it is used and the limitations of the Pivot Table.

Module 2: Source Data

Perfecting your source data in the right structure is important to minimize problems in creating reports, analyzing data and finding trends.

A host of things can go wrong as a result of bad data preparation, from inaccurate reporting to problems with grouping and sorting.

Module 3: Creating a Basic Pivot Table

Applying the Module 2 concept to set up your source data and use Pivot Tables to create meaningful reports.

Module 4: Introducing Slicers

With Excel 2010 and up, Microsoft introduced a feature called slicers. They are visual controls that allow you to quickly and easily filter your data in an interactive way. Slicers enable you to:

• Solve the (Multiple Items) Dilemma
• See What You’re Doing at All Times
• Filter Multiple PivotTables and Pivot Charts
• Put Together Dashingly Handsome Reports

Module 5: Customizing a Pivot Table and the Fields

There are many powerful settings to tweak the information in your pivot table. These tweaks range from making cosmetic changes to changing the underlying calculation used in the pivot table.

Module 6: Investigating Your Data with Pivot Tables

• “Which products are not selling well?”
• “What are the top 10 product categories?”
• “Who are our top 5 salespeople?”
• “What is the average unit price for each category?”
• “How many orders did we have for each category?”
• “Which categories make up over 50% of our total revenue?”
• “Comparing monthly sales, MoM”
• “Monthly sales vs YTD sales”

Module 7: Grouping

An awesome feature in pivoting, as it allows reports to be summarized even further in special situations.

Module 8: Using Pivot Charts

This module looks at how pivot charts work. A PivotChart is an interactive chart that provides a graphic image of the data contained in a PivotTable.

Finale: Create Interactive Dashboard with Pivot Table and Pivot Chart

Applying all that we have learned to create an interactive dashboard.