Excel TV Academy
Xtreme Pivot Tables

Data Wrangling

Video/Text

Xtreme Pivot Tables

11 Modules 189 Lessons Easy

About this course

Go from 0 to Pivot Tables. If you’ve ever wanted to become awesome a Pivot Tables, this course is for you.

Course Structure

ONLY FOR MEMBERS

1 Lesson

Get The Course Files

Download the Course Files

ONLY FOR MEMBERS

46 Lessons

Design & Customize Your Pivot Tables

Tabular Format

No Gaps

Formatting

Tables

Clean Your Data Set

Inserting a Pivot Table

Field List

Field List Areas

Drill Down to Audit

Sort Field List from A to Z

Double Click on any Label to Show More Fields

Defer Layout Update

Pivot Cache

Refresh

Refresh All

Refresh External Data

Import from Access Database

Change Data Source

Clear Filter and Clear Pivot

Select and Format

Move a Pivot Table

Pivot Table Styles

Customize Styles

Use a Customized Style in Another Workbook

Subtotals

Grand Totals

Report Layout

Blank Rows

TIPS - Show the Classic Pivot Table Layout

Expand and Collapse Buttons

Move and Remove Fields and Items

Show or Hide Field List

Show or Hide Field Field Headers

Change Count Of to Sum Of

Number Formatting

Field Name Formatting

Predetermined Number Formatting

TIPS - Change SUM Views in Label Areas

Indent Rows in Compact Layouts

Change Layout of a Report Filter

Format Error Values

Format Empty Cells

Keep Column Widths Upon Refresh

Automatically Refresh a Pivot Table

TIPS - Printing a Pivot Table on Two Pages

TIPS - Show Report Filters on Multiple Pages

ONLY FOR MEMBERS

13 Lessons

Value Field Settings > Summarize Values By

Create Multiple Subtotals

Count

Average

Maximum

MINimum

Product

Count Numbers

StdDevp

VarP

Show Various Grand Totals

Shortcuts to Value Field Settings

See All Pivot Items

Show a Unique Count

ONLY FOR MEMBERS

17 Lessons

Value Field Settings > Show Values As

Percentage of Grand Total

Percentage of Column Total

Percentage of Row Total

Percentage Of

Percentage of Parent Row Total

Percentage of Parent Column Total

Percentage of Parent Total

Difference From

Percentage Difference From

Running Total In

Percentage Running Total In

Rank Smallest to Largest

Rank Largest to Smallest

Index

Shortcuts to Show Values As

Percentage of Revenue

Actual v Plan Variation

ONLY FOR MEMBERS

17 Lessons

Grouping

Group by Date

Grouping by Months

Grouping by Quarters and Years

Group by Sales Ranges

Group by Text Fields

Group by Time

Shortcuts to Grouping

Grouping by Half Years

Grouping by a Date that starts on a Monday

Grouping by a Custom Date

Grouping by a Fiscal Year and Quarter

Errors when Grouping by Dates

Group Two Pivot Tables in the Same Workbook

Fixing the Problem of Counting Grouped Sales

Display Dates that Have No Data

Quarterly Comparative Report

FINANCIAL Min and Max Bank Balance

ONLY FOR MEMBERS

9 Lessons

Sort

Sorting by Largest or Smallest

Sort an Item Row

Sort Manually

Sort using a Custom List

Override a Custom List Sort

Sort rows from A to Z and values by Z to A

Sort New Items Added to Your Data Source

TIPS Clear a Sort

TIPS Sort Largest to Smallest Grand Totals

ONLY FOR MEMBERS

13 Lessons

Slicers (New in Excel 2010)

Insert a Slicer

Slicer Styles

Creating a Custom Style

Copying a Custom Style in to a New Workbook

Slicer Settings

Slicer Size and Properties

Slicer Connections for Multiple Pivot Tables

Different ways to Filter a Slicer

Use One Slicer for Two Pivot Tables

Lock the Workbook but not the Slicer

Interactive Employee Photos with Slicers

Select a Monthly PnL Report with a Slicer

Base, Best and Worst Case Forecast

ONLY FOR MEMBERS

16 Lessons

Calculated Fields & Items

Creating a Calculated Field

Use an Existing Calculated Field

Editing a Calculated Field

Excel formulas and Calculated Fields

Creating a Calculated Item

Use an Existing Calculated Item

Editing a Calculated Item

Excel Formulas and Calculated Items

Calculated Item on Column Labels

Shortcomings of Calculated Items

Solve Order for Calculated Items

List Formulas

Remove a Calculated Field temporarily

Order of Operations

Creating a PnL Pivot Table Report

Actual v Plan with Calculated Fields

ONLY FOR MEMBERS

21 Lessons

Pivot Charts

Insert a Pivot Chart

Insert a Slicer with a Pivot Chart

Pivot Chart Designs

Pivot Chart Layouts

Pivot Chart Formats

Limitations of Pivot Charts and Workarounds

Saving a Pivot Chart Template

Shortcuts to formatting a Pivot Chart

TIPS Link Chart Title To a Pivot Cell

Copying a Second Chart

Put a Chart on a Separate Page with F11

Insert Pivot Chart Straight from the Data Source

Paste Pivot Chart to your Email as a Picture

TIPS Paste Pivot Chart to Powerpoint

TIPS Printing a Pivot Chart

Include a Sparkline with Your Pivot Table

Chart Do's and Dont's

FUN Use Slicers to Change Chart Types

Workaround to creating a Scatter Graph

PnL Pivot table report with Graphs

Pivot Table Slicer and Chart Dashboard

ONLY FOR MEMBERS

17 Lessons

Conditional Formatting of Pivot Tables

Intro to Conditional Formatting

Highlight Cell Rules based on Values

Highlight Cell Rules based on Text Labels

Highlight Cell Rules based on Date Labels

Top and Bottom Rules

Data Bars, Color Scales and Icon Sets

Format cells that contain - For Bonuses

Format Top Ranked Values

Format Values Above the Average

Use a Formula to Determine Which Cells to Format

TIPS Use Selected Cells to Format Multiple Fields

All Cells Showing Values

Control Conditional Formatting with Slicers

TIP Show text in the Pivot Table Values Area

Conditionally Formatting Blank Cells

Accounts Receivable Ageing Report Matrix

Conditionally Format your sales results