Video description

Table of Contents

Introduction

Introduction to Excel PivotTables, PowerPivot, Power View, and GeoFlow with MrExcel LiveLessons

Lesson 1: Basics

Learning objectives

Use a Pivot Table to Summarize Detailed Data

Your Manager Wants Your Report Changed

Add or Remove Fields from an Existing Pivot Table

Summarize Pivot Table Data by Three Fields

Why Does the Pivot Table Field List Dialog Keep Disappearing?

Move or Change Part of a Pivot Table

Update Data Behind a Pivot Table

Why Does This Look Different from Excel 2003? Return to the Classic Pivot Table Layout

Lesson 2: Formatting

Learning objectives

Compare Four Ways to Show Two Values Fields in a Pivot Table

Kill Compact View

Eliminate Blanks in the Outline Format of a Pivot Table

Suppress Grand Totals in a Pivot Table

Replace Blanks in a Pivot Table with Zeros

Specify a Number Format for a Pivot Table Field

Format Pivot Tables with the Gallery

Add a Format to Gallery

Selecting Parts for Formatting

Icon Set in a Pivot Table

Lesson 3: Calculations

Learning objectives

Create a Report That Shows Count, Min, Max, Average, Etc

Percent of Parent, Rank, Running Total In

Calculated Fields in a Pivot Table

Add a Calculated Item for Budget - Actuals

Instead of Using Calculated Items Group Text Fields

Present a Pivot Table in High-to-Low Order by Revenue

Lesson 4: Sort Filter

Learning objectives

Manually Re-sequence the Order of Data in a Pivot Table

Limit a Pivot Report to Show Just the Top 12 Customers

Filtering a Pivot Table

Add Visual Filters to a Pivot Table with Slicers

Timelines vs Multiple Slicers

Lesson 5: Features

Learning objectives

See Detail Behind One Number in a Pivot Table

Group Daily Dates by Month in a Pivot Table

Create a Year-over-Year Analysis

Stop GetPivotData from Appearing

Group by Week in a Pivot Table

Group Numbers to Create Frequency Chart

Grouping one pivot table groups all pivot tables; new Cache

Lesson 6: Uses

Learning objectives

Create a Unique List of Customers with a Pivot Table

Use a Pivot Table to Compare Two Lists

Fixing Customer Name problems Using a Pivot Table

Show Text Fields for a Pareto Chart

Lesson 7: Tips

Learning objectives

Base your pivot table on a Table When New Data Might Be Added

Show Yes/No As the Values in a Pivot Table

Create Perfectly Formatted Pivot Tables Using GETPIVOTDATA

Create a Worksheet for Every Customer Using Show Report Filter Pages

Controlling Multiple Pivot Tables with One Set of Slicers

Reduce Pivot Table Workbook Size by 50% Before Sending

Create a Pivot Table from Access Data

Create a chart from a pivot table with Pivot Charts

Lesson 8: Troubleshooting

Learning objectives

Compatibility between versions

Force Missing Months to Show in a Pivot Table

Whatever Happened to Multiple Consolidation Ranges in Pivot Tables?

Cleaning up bad data before creating a pivot table

Pivot Data Where the Source Data Has Dates Stretching Across the Columns

Lesson 9: PowerPivot

Learning objectives

Using the Data Model in Excel 2013 to Join Two Tables

Distinct Count in Excel 2013

True Top Five in Excel 2013

Using Named Sets for Asymmetric Reports

Getting PowerPivot

Load Data into PowerPivot

Define Relationships in the PowerPivot Window or Diagram View

Creating the Pivot Table - Compare Choices

Changing the Calculation from Sum Other Differences

Lesson 10: DAX Calculated Fields

Learning objectives

DAX Measures

Use DAX Measures to Calculate a Range

Use DAX Measures to un-apply a Filter

Using a Calendar Table for Time Intelligence

Time Intelligence Functions

Disconnected Parameter Tables with FILTER()

Actuals vs Budget

Using IF(VALUES in a Measure

Build Slicers on Lookup Tables

First Initial Slicer (and turning off cross-slicer filtering)

Convert the Pivot Table to Cube Formulas

Lesson 11: Power View

Learning objectives

Preparing your data for PowerView

Adding a Power View Worksheet

Adding a Dashboard Element as a Table

Converting Table to a Chart

Explicit and Implicit Slicers

Understanding the Filter Pane

Using Tile Boxes

Replicating Charts Using Multiples

Showing Data on a Map

Showing Images in Table or Card View

Formatting Power View

Animating a Scatter Chart over Time

Lesson 12: GeoFlow

Learning objectives

Adding data to GeoFlow

Flying through the map

Animating GeoFlow

Building a tour in GeoFlow

Summary

Summary of Excel PivotTables, PowerPivot, Power View, and GeoFlow with MrExcel LiveLessons