Excel TV

Academy course

Xtreme Pivot Tables

231 lessons across 18 modules · John Michaloudis

Xtreme Pivot Tables

What’s in this course

Get The Course Files

  • Download the Course Files

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

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

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

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

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

Filter

  • Filter by Dates
  • Filter by Labels - Text
  • Filter by Labels - Numerical Text
  • Filter by Values
  • Filter by Values - Top or Bottom 10 Items
  • Filter by Values - Top or Bottom Percent
  • Filter by Values - Top or Bottom Sum
  • Filter by Report Filter
  • Shortcuts to Filter
  • Keep or Hide Selected Items
  • Filter by Text Wildcards
  • TIPS Filter by Multiple Fields
  • Apply Multiple Filters
  • TIPS Filter by Multiple Values
  • Include New Items in Manual Filter
  • Clear Filters with One Click
  • TIPS Add a Filter for the Column Items
  • Top 5 Expense Report
  • Top 25 percent of Channel Partners

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

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

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

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

GETPIVOTDATA Formula

  • Intro to GET PIVOT DATA
  • Create a custom report with GET PIVOT DATA
  • Reference Dates with GET PIVOT DATA
  • Data validation with GET PIVOT DATA
  • Shortfalls of GET PIVOT DATA
  • Grand Totals to the left of the pivot table
  • Live forecasting with GET PIVOT DATA
  • Channel analysis with GET PIVOT DATA

Macros

  • Adding the Developer tab
  • Record a simple macro to Refresh a Pivot Table
  • Date filter macro
  • Different Pivot Table Views Macro
  • Top 10 macro
  • Add macro to quick access toolbar

Data Management

  • Reducing File Memory by Copying Existing Pivot Table
  • Reducing File Memory by Deleting the Data Source
  • Reducing file memory by saving file as XLSB
  • Reducing file memory by keeping data in Access
  • Compatibility Issues with Excel 2007 and Excel 2010
  • Sharing a Pivot Table via OneDrive

Bonus Videos

  • Sales Forecasting with Calculated Fields
  • Consolidate with a Pivot Table
  • Frequency Distribution with a Pivot Table
  • Break Even Model with a Pivot Table
  • Several slicer custom styles for you to use
  • Interactive Balance Sheet Pivot Table
  • Monthly Sales Manager Performance
  • Reconciling customer payments

New Excel 2013 Pivot Table Features

  • Cosmetic Changes
  • Recommended Pivot Tables
  • Distinct Count
  • Timeline Slicer
  • Data Model

New Excel 2013 Pivot Table Features

  • Group Periods
  • Multiselect Slicers
  • Pivot Chart Expand and Collapse
  • 3D Maps

New Excel 2019 & Office 365 Pivot Table Features

  • Which Excel Version
  • Personalize the default Pivot Table layout
  • Automatic relationship detection
  • Automatic time grouping
  • Search in the Pivot Table

Curriculum

Get The Course Files 1 lesson
  1. 1. Download the Course Files
Design & Customize Your Pivot Tables 46 lessons
  1. 1. Tabular Format
  2. 2. No Gaps
  3. 3. Formatting
  4. 4. Tables
  5. 5. Clean Your Data Set
  6. 6. Inserting a Pivot Table
  7. 7. Field List
  8. 8. Field List Areas
  9. 9. Drill Down to Audit
  10. 10. Sort Field List from A to Z
  11. 11. Double Click on any Label to Show More Fields
  12. 12. Defer Layout Update
  13. 13. Pivot Cache
  14. 14. Refresh
  15. 15. Refresh All
  16. 16. Refresh External Data
  17. 17. Import from Access Database
  18. 18. Change Data Source
  19. 19. Clear Filter and Clear Pivot
  20. 20. Select and Format
  21. 21. Move a Pivot Table
  22. 22. Pivot Table Styles
  23. 23. Customize Styles
  24. 24. Use a Customized Style in Another Workbook
  25. 25. Subtotals
  26. 26. Grand Totals
  27. 27. Report Layout
  28. 28. Blank Rows
  29. 29. TIPS - Show the Classic Pivot Table Layout
  30. 30. Expand and Collapse Buttons
  31. 31. Move and Remove Fields and Items
  32. 32. Show or Hide Field List
  33. 33. Show or Hide Field Field Headers
  34. 34. Change Count Of to Sum Of
  35. 35. Number Formatting
  36. 36. Field Name Formatting
  37. 37. Predetermined Number Formatting
  38. 38. TIPS - Change SUM Views in Label Areas
  39. 39. Indent Rows in Compact Layouts
  40. 40. Change Layout of a Report Filter
  41. 41. Format Error Values
  42. 42. Format Empty Cells
  43. 43. Keep Column Widths Upon Refresh
  44. 44. Automatically Refresh a Pivot Table
  45. 45. TIPS - Printing a Pivot Table on Two Pages
  46. 46. TIPS - Show Report Filters on Multiple Pages
Value Field Settings > Summarize Values By 13 lessons
  1. 1. Create Multiple Subtotals
  2. 2. Count
  3. 3. Average
  4. 4. Maximum
  5. 5. MINimum
  6. 6. Product
  7. 7. Count Numbers
  8. 8. StdDevp
  9. 9. VarP
  10. 10. Show Various Grand Totals
  11. 11. Shortcuts to Value Field Settings
  12. 12. See All Pivot Items
  13. 13. Show a Unique Count
Value Field Settings > Show Values As 17 lessons
  1. 1. Percentage of Grand Total
  2. 2. Percentage of Column Total
  3. 3. Percentage of Row Total
  4. 4. Percentage Of
  5. 5. Percentage of Parent Row Total
  6. 6. Percentage of Parent Column Total
  7. 7. Percentage of Parent Total
  8. 8. Difference From
  9. 9. Percentage Difference From
  10. 10. Running Total In
  11. 11. Percentage Running Total In
  12. 12. Rank Smallest to Largest
  13. 13. Rank Largest to Smallest
  14. 14. Index
  15. 15. Shortcuts to Show Values As
  16. 16. Percentage of Revenue
  17. 17. Actual v Plan Variation
Grouping 17 lessons
  1. 1. Group by Date
  2. 2. Grouping by Months
  3. 3. Grouping by Quarters and Years
  4. 4. Group by Sales Ranges
  5. 5. Group by Text Fields
  6. 6. Group by Time
  7. 7. Shortcuts to Grouping
  8. 8. Grouping by Half Years
  9. 9. Grouping by a Date that starts on a Monday
  10. 10. Grouping by a Custom Date
  11. 11. Grouping by a Fiscal Year and Quarter
  12. 12. Errors when Grouping by Dates
  13. 13. Group Two Pivot Tables in the Same Workbook
  14. 14. Fixing the Problem of Counting Grouped Sales
  15. 15. Display Dates that Have No Data
  16. 16. Quarterly Comparative Report
  17. 17. FINANCIAL Min and Max Bank Balance
Sort 9 lessons
  1. 1. Sorting by Largest or Smallest
  2. 2. Sort an Item Row
  3. 3. Sort Manually
  4. 4. Sort using a Custom List
  5. 5. Override a Custom List Sort
  6. 6. Sort rows from A to Z and values by Z to A
  7. 7. Sort New Items Added to Your Data Source
  8. 8. TIPS Clear a Sort
  9. 9. TIPS Sort Largest to Smallest Grand Totals
Filter 19 lessons
  1. 1. Filter by Dates
  2. 2. Filter by Labels - Text
  3. 3. Filter by Labels - Numerical Text
  4. 4. Filter by Values
  5. 5. Filter by Values - Top or Bottom 10 Items
  6. 6. Filter by Values - Top or Bottom Percent
  7. 7. Filter by Values - Top or Bottom Sum
  8. 8. Filter by Report Filter
  9. 9. Shortcuts to Filter
  10. 10. Keep or Hide Selected Items
  11. 11. Filter by Text Wildcards
  12. 12. TIPS Filter by Multiple Fields
  13. 13. Apply Multiple Filters
  14. 14. TIPS Filter by Multiple Values
  15. 15. Include New Items in Manual Filter
  16. 16. Clear Filters with One Click
  17. 17. TIPS Add a Filter for the Column Items
  18. 18. Top 5 Expense Report
  19. 19. Top 25 percent of Channel Partners
Slicers (New in Excel 2010) 13 lessons
  1. 1. Insert a Slicer
  2. 2. Slicer Styles
  3. 3. Creating a Custom Style
  4. 4. Copying a Custom Style in to a New Workbook
  5. 5. Slicer Settings
  6. 6. Slicer Size and Properties
  7. 7. Slicer Connections for Multiple Pivot Tables
  8. 8. Different ways to Filter a Slicer
  9. 9. Use One Slicer for Two Pivot Tables
  10. 10. Lock the Workbook but not the Slicer
  11. 11. Interactive Employee Photos with Slicers
  12. 12. Select a Monthly PnL Report with a Slicer
  13. 13. Base, Best and Worst Case Forecast
Calculated Fields & Items 16 lessons
  1. 1. Creating a Calculated Field
  2. 2. Use an Existing Calculated Field
  3. 3. Editing a Calculated Field
  4. 4. Excel formulas and Calculated Fields
  5. 5. Creating a Calculated Item
  6. 6. Use an Existing Calculated Item
  7. 7. Editing a Calculated Item
  8. 8. Excel Formulas and Calculated Items
  9. 9. Calculated Item on Column Labels
  10. 10. Shortcomings of Calculated Items
  11. 11. Solve Order for Calculated Items
  12. 12. List Formulas
  13. 13. Remove a Calculated Field temporarily
  14. 14. Order of Operations
  15. 15. Creating a PnL Pivot Table Report
  16. 16. Actual v Plan with Calculated Fields
Pivot Charts 21 lessons
  1. 1. Insert a Pivot Chart
  2. 2. Insert a Slicer with a Pivot Chart
  3. 3. Pivot Chart Designs
  4. 4. Pivot Chart Layouts
  5. 5. Pivot Chart Formats
  6. 6. Limitations of Pivot Charts and Workarounds
  7. 7. Saving a Pivot Chart Template
  8. 8. Shortcuts to formatting a Pivot Chart
  9. 9. TIPS Link Chart Title To a Pivot Cell
  10. 10. Copying a Second Chart
  11. 11. Put a Chart on a Separate Page with F11
  12. 12. Insert Pivot Chart Straight from the Data Source
  13. 13. Paste Pivot Chart to your Email as a Picture
  14. 14. TIPS Paste Pivot Chart to Powerpoint
  15. 15. TIPS Printing a Pivot Chart
  16. 16. Include a Sparkline with Your Pivot Table
  17. 17. Chart Do's and Dont's
  18. 18. FUN Use Slicers to Change Chart Types
  19. 19. Workaround to creating a Scatter Graph
  20. 20. PnL Pivot table report with Graphs
  21. 21. Pivot Table Slicer and Chart Dashboard
Conditional Formatting of Pivot Tables 17 lessons
  1. 1. Intro to Conditional Formatting
  2. 2. Highlight Cell Rules based on Values
  3. 3. Highlight Cell Rules based on Text Labels
  4. 4. Highlight Cell Rules based on Date Labels
  5. 5. Top and Bottom Rules
  6. 6. Data Bars, Color Scales and Icon Sets
  7. 7. Format cells that contain - For Bonuses
  8. 8. Format Top Ranked Values
  9. 9. Format Values Above the Average
  10. 10. Use a Formula to Determine Which Cells to Format
  11. 11. TIPS Use Selected Cells to Format Multiple Fields
  12. 12. All Cells Showing Values
  13. 13. Control Conditional Formatting with Slicers
  14. 14. TIP Show text in the Pivot Table Values Area
  15. 15. Conditionally Formatting Blank Cells
  16. 16. Accounts Receivable Ageing Report Matrix
  17. 17. Conditionally Format your sales results
GETPIVOTDATA Formula 8 lessons
  1. 1. Intro to GET PIVOT DATA
  2. 2. Create a custom report with GET PIVOT DATA
  3. 3. Reference Dates with GET PIVOT DATA
  4. 4. Data validation with GET PIVOT DATA
  5. 5. Shortfalls of GET PIVOT DATA
  6. 6. Grand Totals to the left of the pivot table
  7. 7. Live forecasting with GET PIVOT DATA
  8. 8. Channel analysis with GET PIVOT DATA
Macros 6 lessons
  1. 1. Adding the Developer tab
  2. 2. Record a simple macro to Refresh a Pivot Table
  3. 3. Date filter macro
  4. 4. Different Pivot Table Views Macro
  5. 5. Top 10 macro
  6. 6. Add macro to quick access toolbar
Data Management 6 lessons
  1. 1. Reducing File Memory by Copying Existing Pivot Table
  2. 2. Reducing File Memory by Deleting the Data Source
  3. 3. Reducing file memory by saving file as XLSB
  4. 4. Reducing file memory by keeping data in Access
  5. 5. Compatibility Issues with Excel 2007 and Excel 2010
  6. 6. Sharing a Pivot Table via OneDrive
Bonus Videos 8 lessons
  1. 1. Sales Forecasting with Calculated Fields
  2. 2. Consolidate with a Pivot Table
  3. 3. Frequency Distribution with a Pivot Table
  4. 4. Break Even Model with a Pivot Table
  5. 5. Several slicer custom styles for you to use
  6. 6. Interactive Balance Sheet Pivot Table
  7. 7. Monthly Sales Manager Performance
  8. 8. Reconciling customer payments
New Excel 2013 Pivot Table Features 9 lessons
  1. 1. Cosmetic Changes
  2. 2. Recommended Pivot Tables
  3. 3. Distinct Count
  4. 4. Timeline Slicer
  5. 5. Data Model
  6. 6. Group Periods
  7. 7. Multiselect Slicers
  8. 8. Pivot Chart Expand and Collapse
  9. 9. 3D Maps
New Excel 2019 & Office 365 Pivot Table Features 5 lessons
  1. 1. Which Excel Version
  2. 2. Personalize the default Pivot Table layout
  3. 3. Automatic relationship detection
  4. 4. Automatic time grouping
  5. 5. Search in the Pivot Table
Member access: Lesson videos and downloadable resources require academy login. Existing members get the same access they had on academy.excel.tv.