Pivot Tables – From Novice to Ninja

• Microsoft Excel 2013 and 2010 compliant
• Award winning Microsoft MVP instructor Ken Puls
• Certificate of training upon completing the course
• Accredited with CPD UK
• Bite sized video tutorials, cheat sheets, exercise files and quizzes
• Unlimited testing

Self-paced course can be completed in your own time over 12 months

• Course Syllabus includes 36 tutorials covering beginner, intermediate and advanced levels

Pivot Tables – From Novice to Ninja syllabus

The Very Basics

What is a Pivot Table - and why do you care?

Pivot Tables open up your world to quickly summarize data and build solutions that others can explore.

________________________________________

The Pivot Table Life Cycle

Understanding how the life cycle of a Pivot Table solution is different than an ad-hoc reporting solution.

________________________________________

Creating your first Pivot Table

Creating your first Pivot Table is the first step to unlocking serious Excel power in data optimization.

________________________________________

Field Well Options

Determining the Field Well options you’d like to use to view your Pivot Table data.

Sourcing Your Data

Source Data Best Practices

Setting up your data sets to ensure that they will be able to be consumed by a Pivot Table.

________________________________________

Using Excel ranges

Choosing between building quick and dirty Pivot Tables or future proofing Pivot Tables for future update.

________________________________________

Preparing Source Data

When you need to aggregate multiple tables or create new fields in order to display properly in a Pivot Table.

________________________________________

Using Database Data

Pulling database data into Excel and Pivot Tables.

Laying Out the Pivot Table

Totals and Sub-Totals

Use to turn totals and subtotals on/off where needed in your Pivot Table.

________________________________________

Choosing Pivot Table Layouts

Determining the basic layout you’d like to use to display your Pivot Table data.

________________________________________

Grouping

Use to group different row or column fields together for a more logical display.

Pivot Table Calculations

Aggregation

When you wish to show different aggregation on your Pivot Table fields than what is provided by default.

________________________________________

Running totals

When you need to display a running total on your Pivot Table.

________________________________________

Percent of x Calculations

When you need to display a value as the % of another value on your Pivot Table.

________________________________________

Difference From Calculations

When you need to display values that are a difference from another value in a Pivot. This is very useful for showing growth/decline from previous periods.

________________________________________

Percentage Difference From Calculations

When you need to display values that are a percentage of difference from another value in a Pivot. This is very useful for showing growth/decline from previous periods.

________________________________________

Top and Bottom x Items

When you want to filter your Pivot Table to show the top/bottom x items or top/bottom x% of items in the data set.

________________________________________

Calculated Fields

Understanding how to create calculated fields in a Pivot Table.

________________________________________

Calculated Items

Understanding how to create calculated items in a Pivot Table.

Pivot Table Formatting

Value Field Formats

Various ways to display numbers in the values area of a Pivot Table.

________________________________________

Preserving Cell Formats on Update

How to force a Pivot Table to preserve cell formats and column widths when it is updated.

________________________________________

Pivot Table Styles

Using pre-defined styles with a Pivot Table.

________________________________________

Custom Pivot Table Styles

Creating your own style to use with a Pivot Table.

________________________________________

Conditional Formatting on Pivot Tables

Basics of applying conditional formats to a Pivot Table.

Sorting

Basic Sorting

Basic sorting of row and column headers in a Pivot Table.

________________________________________

Custom Sorting

Creating Custom Sort Orders for a Pivot Table.

________________________________________

Sorting Values

Sorting the values area of a Pivot Table.

Filtering Data

Report Filters

Using Report Filters to narrow down results in a Pivot Table.

________________________________________

Built in Filter Controls

Using row and column filters to narrow down results in a Pivot Table.

________________________________________

Slicers

Slicers are user-friendly filters for Pivot Tables.

________________________________________

Filtering Multiple Pivot Tables

Filtering multiple Pivot Tables at once with Slicers.

Extracting Data

Show Details

Using Show Details to extract details from a summarized value.

________________________________________

GETPIVOTDATA

Using the GETPIVOTDATA function to extract specific fields from a Pivot Table.

Data Visualization

Creating Pivot Charts

Creating Pivot Charts out of a Pivot Table.

Finalization

Automating Refresh

Automating Pivot Table refresh operations.

________________________________________

Pre-release Considerations

Items to consider before releasing your file to users.

This online course is designed to teach people to use Microsoft Pivot Tables more effectively for work and study. It includes 36 beginner, intermediate and advanced level lessons delivered in bite sized, engaging video tutorials by an award-winning Microsoft Excel MVP instructor, Ken Puls.

No prior knowledge of Excel is required

Certificate of achievement

Digital certificate - Included

Certificate of completion

Digital certificate - Included