LiveLessons: Excel PivotTables, PowerPivot, Power View, and Power Map with MrExcel

Lesson 1
Part I: The Basics
Learn how to create your first PivotTable and then learn how to change that PivotTable! You'll look at the tools available and explore the interface. You'll learn how to:

• 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
Part II: Formatting
Discover PivotTable formatting! Learn how to overcome the system defaults to create useful-looking reports. You'll find out how to:

• 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
Part III: Calculations
Thirty minutes is all you need to cover powerful PivotTable calculations like percentage of column and rank! You'll learn how to:

• 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

Lesson 4
Part IV: Sort & Filter
Who knew sorting & filtering could be so powerful! These six videos detail useful ways to manipulate the data, including a new feature called a Slicer. You'll learn how to:

• Present a Pivot Table in High-to-Low Order by Revenue
• 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
Part V: Features
These seven videos explore Bill Jelen's personal favorite features of PivotTables, including an amazing way to create a year-over-year report!

• 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
Part VI: Uses
What can you really use PivotTables for anyway? Sure, they're good for taking 100,000 rows of data and summarizing it down to a one-page report; but what are some real life uses of PivotTables? Bill will guide you through four prime examples in this section. You'll focus on how to:

• 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
Part VII: Tips
These eight tips will save you time and frustration! In just under 40 minutes you'll learn how to:

• 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
Part VIII: Troubleshooting
The five most common pitfalls are handled in this twenty minute segment on Troubleshooting. They are:

• 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
Part IX: PowerPivot
In just over 40 minutes these nine lessons will help you harness the power of PowerPivot! The topics include:

• 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
Part X: DAX Calculated Fields
Data Analysis Expressions! DAX is a new function language that's used with PowerPivot, PowerView, and GeoFlow. It makes Calculated Fields in PivotTables look sophomoric., The problem is, it's hard to learn. It's like going to the dentist. Everyone knows they have to do it, they just dont want to do it. Well, in these lessons, Bill Jelen gives you enough sample calculated fields, called Measures, to get you started.

• 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
Part XI: Power View
Who knew that PowerView could be explained in about 45 minutes! These twleve videos guide you through it easily by covering the following topics:

• 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
Part XII: Power Map
In just over 15 minutes, you'll explore the following facets of GeoFlow:

• Adding data to GeoFlow
• Flying through the map
• Animating GeoFlow
• Building a tour in GeoFlow