This is a recording of the October 2019 monthly webinar in the Excel MVP Webinar series. In this free 59-minute webinar, Bill Jelen covers many pivot table techniques:
Table of Contents
(1:36) Traditional Pivot Cache Pivot Tables
(2:45) Setting up data for a successful pivot table
(3:40) Using Alt+Enter for headings
(4:00) Avoiding tiny blank columns between columns
(4:15) Accounting Style Underlines
(5:35) Pivot Table 101 – Your First Pivot Table
(7:55) Changing Number Format in Pivot Tables
(8:35) Moving fields around in a pivot table
(9:40) Drill-down on any pivot table number
(10:45) Refreshing pivot table if the underlying data changes
(11:25) Year-over-Year Pivot Tables – Grouping Dates
(14:05) Adding a formula outside the pivot table and problems with GetPivotData
(16:15) Variance percentage with % Change From Previous
(18:00) Top 10 Pivot Table – Introduction
(18:15) Changing heading from Sum of Revenue
(18:45) Changing the calculation in a pivot table
(19:10) Double-click a field heading to open Field Settings dialog
(19:20) Percentage of Column Total
(19:40) Rank column in a pivot table
(20:00) Running Total in a pivot table
(20:35) Filtering to Top 10
(21:45) Grand Total seems to be wrong after filtering
(22:10) Include Filtered Items in Totals is greyed out
(22:55) Dan in Philadelphia has a solution
(23:25) AutoFiltering a Pivot Table using a Hack
(25:30) Filtering using the Filter drop-zone
(25:45) The pain of (Multiple Items) header and Shrink to Fit
(26:25) Replacing Filter area with Slicers
(26:45) Resizing slicers
(27:30) Replicate a pivot table for each customer (Szilvia)
(28:50) Tabular form instead of Compact Form
(29:55) Suggesting changes to the Excel team with Excel.UserVoice.Com
(30:20) Defaults for all future pivot tables
(31:30) Data Model Pivot Tables – Introduction to Part 2 of Webinar
(32:30) Mash up data from multiple sheets into one pivot tables using the Data Model
(35:55) Count Distinct in a pivot table
(37:35) True Top 10 Report With Correct Total without any hacks
(39:30) Median in a Pivot Table using DAX formula
(41:35) Showing Text in the values area of a Pivot Table
(45:25) One set of Slicers for two different data sets
(48:25) Create a Pivot Table on a Map using 3D Map in Excel
(52:10) Is there a use for GetPivotData?
(57:20) Recap
(58:49) Pitch for next month’s Liam Bastick webinar