Excel Xtreme Pivot Tables

Pivot Table, Microsoft Excel

What you'll learn

Master advanced PivotTable techniques to analyze, summarize, and present large datasets, making data-driven decisions confidently.

Learn to unleash PivotTables’ full potential with advanced techniques, transforming complex datasets into actionable insights.

Discover deep PivotTable skills to turn large datasets into clear, impactful reports that drive strategic business decisions.

Conquer Excel with Xtreme Pivot Tables, learning to analyze and summarize large data for confident, data-backed decision-making.

Requirements

Basic Excel Skills: Familiarity with Excel’s interface, basic formulas, and general PivotTable use.

Understanding of Data Analysis: A foundational understanding of data concepts, such as summarizing data and generating reports, will be helpful.

Access to Excel: Excel 2016 or a later version is recommended for the best experience, as you’ll need it to follow along with advanced techniques.

Description

Maybe you’ve been working with Excel for years, or perhaps you’re new to it. Either way, if you deal with data, you’ve probably used PivotTables. They’re powerful tools, and you may even have a few tricks up your sleeve.I was the same. I could create PivotTables to get the basics done, but I knew I was barely scratching the surface. I realized that, while I could pull numbers together, I wasn’t truly getting the most out of my data. I was simply presenting numbers without diving into the deeper insights hiding in my datasets.Sound familiar? If so, you’re not alone.That’s why I created this course. It’s for people like us—professionals who work with data day in and day out and want to go beyond the basics. You can already create a simple PivotTable, sure. But what about using advanced grouping, calculated fields, or dynamic PivotCharts? If these aren’t in your current toolkit, then this course is for you.In Xtreme Pivot Tables, I’ll take you through:Techniques to create calculated fields and items that make your reports go from good to great. When someone challenges your data, you’ll have the skills to answer confidently.Advanced grouping and filtering options so that you’re only showing what matters most. If you’ve ever spent time just scrolling through your data, this will be a game changer.How to bring your data to life visually with PivotCharts. Excel’s native charts are just the beginning; I’ll show you how to customize them in ways you probably didn’t know were possible.Tips for structuring large datasets effectively, so you’re spending less time in the weeds and more time interpreting.No VBA or complicated macros—just the practical, advanced skills you need to master PivotTables and make a real impact with your data.If you’re ready to transform your data analysis in Excel and make informed, data-driven decisions with confidence, this is the course for you.Looking forward to seeing you there!

Overview

Section 1: Design & Customize Your Pivot Tables

Lecture 1 Tabular Format

Lecture 2 No Gaps

Lecture 3 Formatting

Lecture 4 Tables

Lecture 5 Clean Your Data Set

Lecture 6 Inserting a Pivot Table

Lecture 7 Field List

Lecture 8 Field List Areas

Lecture 9 Drill Down to Audit

Lecture 10 Sort Field List from A to Z

Lecture 11 Double Click on any Label to Show More Fields

Lecture 12 Defer Layout Update

Lecture 13 Pivot Cache

Lecture 14 Refresh

Lecture 15 Refresh All

Lecture 16 Refresh External Data

Lecture 17 Import from Access Database

Lecture 18 Change Data Source

Lecture 19 Clear Filter and Clear Pivot

Lecture 20 Select and Format

Lecture 21 Move a Pivot Table

Lecture 22 Pivot Table Styles

Lecture 23 Customize Styles

Lecture 24 Use a Customized Style in Another Workbook

Lecture 25 Subtotals

Lecture 26 Grand Totals

Lecture 27 Report Layout

Lecture 28 Blank Rows

Lecture 29 TIPS - Show the Classic Pivot Table Layout

Lecture 30 Expand and Collapse Buttons

Lecture 31 Move and Remove Fields and Items

Lecture 32 Show or Hide Field List

Lecture 33 Show or Hide Field Field Headers

Lecture 34 Change Count Of to Sum Of

Lecture 35 Number Formatting

Lecture 36 Field Name Formatting

Lecture 37 Predetermined Number Formatting

Lecture 38 TIPS - Change SUM Views in Label Areas

Lecture 39 Indent Rows in Compact Layouts

Lecture 40 Change Layout of a Report Filter

Lecture 41 Format Error Values

Lecture 42 Format Empty Cells

Lecture 43 Keep Column Widths Upon Refresh

Lecture 44 Automatically Refresh a Pivot Table

Lecture 45 TIPS - Printing a Pivot Table on Two Pages

Lecture 46 TIPS - Show Report Filters on Multiple Pages

Section 2: Value Field Settings > Summarize Values By

Lecture 47 Create Multiple Subtotals

Lecture 48 Count

Lecture 49 Average

Lecture 50 Maximum

Lecture 51 MINimum

Lecture 52 Product

Lecture 53 Count Numbers

Lecture 54 StdDevp

Lecture 55 VarP

Lecture 56 Show Various Grand Totals

Lecture 57 Shortcuts to Value Field Settings

Lecture 58 See All Pivot Items

Lecture 59 Show a Unique Count

Section 3: Value Field Settings > Show Values As

Lecture 60 Percentage of Grand Total

Lecture 61 Percentage of Column Total

Lecture 62 Percentage of Row Total

Lecture 63 Percentage Of

Lecture 64 Percentage of Parent Row Total

Lecture 65 Percentage of Parent Column Total

Lecture 66 Percentage of Parent Total

Lecture 67 Difference From

Lecture 68 Percentage Difference From

Lecture 69 Running Total In

Lecture 70 Percentage Running Total In

Lecture 71 Rank Smallest to Largest

Lecture 72 Rank Largest to Smallest

Lecture 73 Index

Lecture 74 Shortcuts to Show Values As

Lecture 75 Percentage of Revenue

Lecture 76 Actual v Plan Variation

Section 4: Grouping

Lecture 77 Group by Date

Lecture 78 Grouping by Months

Lecture 79 Grouping by Quarters and Years

Lecture 80 Group by Sales Ranges

Lecture 81 Group by Text Fields

Lecture 82 Group by Time

Lecture 83 Shortcuts to Grouping

Lecture 84 Grouping by Half Years

Lecture 85 Grouping by a Date that starts on a Monday

Lecture 86 Grouping by a Custom Date

Lecture 87 Grouping by a Fiscal Year and Quarter

Lecture 88 Errors when Grouping by Dates

Lecture 89 Group Two Pivot Tables in the Same Workbook

Lecture 90 Fixing the Problem of Counting Grouped Sales

Lecture 91 Display Dates that Have No Data

Lecture 92 Quarterly Comparative Report

Lecture 93 FINANCIAL Min and Max Bank Balance

Section 5: Sort

Lecture 94 Sorting by Largest or Smallest

Lecture 95 Sort an Item Row

Lecture 96 Sort Manually

Lecture 97 Sort using a Custom List

Lecture 98 Override a Custom List Sort

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

Lecture 100 Sort New Items Added to Your Data Source

Lecture 101 TIPS Clear a Sort

Lecture 102 TIPS Sort Largest to Smallest Grand Totals

Section 6: Filter

Lecture 103 Filter by Dates

Lecture 104 Filter by Labels - Text

Lecture 105 Filter by Labels - Numerical Text

Lecture 106 Filter by Values

Lecture 107 Filter by Values - Top or Bottom 10 Items

Lecture 108 Filter by Values - Top or Bottom Percent

Lecture 109 Filter by Values - Top or Bottom Sum

Lecture 110 Filter by Report Filter

Lecture 111 Shortcuts to Filter

Lecture 112 Keep or Hide Selected Items

Lecture 113 Filter by Text Wildcards

Lecture 114 TIPS Filter by Multiple Fields

Lecture 115 Apply Multiple Filters

Lecture 116 TIPS Filter by Multiple Values

Lecture 117 Include New Items in Manual Filter

Lecture 118 Clear Filters with One Click

Lecture 119 TIPS Add a Filter for the Column Items

Lecture 120 Top 5 Expense Report

Lecture 121 Top 25 percent of Channel Partners

Section 7: Slicers (New in Excel 2010)

Lecture 122 Insert a Slicer

Lecture 123 Slicer Styles

Lecture 124 Creating a Custom Style

Lecture 125 Copying a Custom Style in to a New Workbook

Lecture 126 Slicer Settings

Lecture 127 Slicer Size and Properties

Lecture 128 Slicer Connections for Multiple Pivot Tables

Lecture 129 Different ways to Filter a Slicer

Lecture 130 Use One Slicer for Two Pivot Tables

Lecture 131 Lock the Workbook but not the Slicer

Lecture 132 Interactive Employee Photos with Slicers

Lecture 133 Select a Monthly PnL Report with a Slicer

Lecture 134 Base, Best and Worst Case Forecast

Section 8: Calculated Fields & Items

Lecture 135 Creating a Calculated Field

Lecture 136 Use an Existing Calculated Field

Lecture 137 Editing a Calculated Field

Lecture 138 Excel formulas and Calculated Fields

Lecture 139 Creating a Calculated Item

Lecture 140 Use an Existing Calculated Item

Lecture 141 Editing a Calculated Item

Lecture 142 Excel Formulas and Calculated Items

Lecture 143 Calculated Item on Column Labels

Lecture 144 Shortcomings of Calculated Items

Lecture 145 Solve Order for Calculated Items

Lecture 146 List Formulas

Lecture 147 Remove a Calculated Field temporarily

Lecture 148 Order of Operations

Lecture 149 Creating a PnL Pivot Table Report

Lecture 150 Actual v Plan with Calculated Fields

Section 9: Pivot Charts

Lecture 151 Insert a Pivot Chart

Lecture 152 Insert a Slicer with a Pivot Chart

Lecture 153 Pivot Chart Designs

Lecture 154 Pivot Chart Layouts

Lecture 155 Pivot Chart Formats

Lecture 156 Limitations of Pivot Charts and Workarounds

Lecture 157 Saving a Pivot Chart Template

Lecture 158 Shortcuts to formatting a Pivot Chart

Lecture 159 TIPS Link Chart Title To a Pivot Cell

Lecture 160 Copying a Second Chart

Lecture 161 Put a Chart on a Separate Page with F11

Lecture 162 Insert Pivot Chart Straight from the Data Source

Lecture 163 Paste Pivot Chart to your Email as a Picture

Lecture 164 TIPS Paste Pivot Chart to Powerpoint

Lecture 165 TIPS Printing a Pivot Chart

Lecture 166 Include a Sparkline with Your Pivot Table

Lecture 167 Chart Do's and Dont's

Lecture 168 FUN Use Slicers to Change Chart Types

Lecture 169 Workaround to creating a Scatter Graph

Lecture 170 PnL Pivot table report with Graphs

Lecture 171 Pivot Table Slicer and Chart Dashboard

Section 10: Conditional Formatting of Pivot Tables

Lecture 172 Intro to Conditional Formatting

Lecture 173 Highlight Cell Rules based on Values

Lecture 174 Highlight Cell Rules based on Text Labels

Lecture 175 Highlight Cell Rules based on Date Labels

Lecture 176 Top and Bottom Rules

Lecture 177 Data Bars, Color Scales and Icon Sets

Lecture 178 Format cells that contain

Lecture 179 Format Top Ranked Values

Lecture 180 Format Values Above the Average

Lecture 181 Use a Formula to Determine Which Cells to Format

Lecture 182 TIPS Use Selected Cells to Format Multiple Fields

Lecture 183 All Cells Showing Values

Lecture 184 Control Conditional Formatting with Slicers

Lecture 185 TIP Show text in the Pivot Table Values Area

Lecture 186 Conditionally Formatting Blank Cells

Lecture 187 Accounts Receivable Ageing Report Matrix

Lecture 188 Conditionally Format your sales results

Section 11: GETPIVOTDATA Formula

Lecture 189 Intro to GET PIVOT DATA

Lecture 190 Create a custom report with GET PIVOT DATA

Lecture 191 Reference Dates with GET PIVOT DATA

Lecture 192 Data validation with GET PIVOT DATA

Lecture 193 Shortfalls of GET PIVOT DATA

Lecture 194 Grand Totals to the left of the pivot table

Lecture 195 Live forecasting with GET PIVOT DATA

Lecture 196 Channel analysis with GET PIVOT DATA

Section 12: Macros

Lecture 197 Adding the Developer tab

Lecture 198 Record a simple macro to Refresh a Pivot Table

Lecture 199 Date filter macro

Lecture 200 Different Pivot Table Views Macro

Lecture 201 Top 10 macro

Lecture 202 Add macro to quick access toolbar

Section 13: Data Management

Lecture 203 Reducing File Memory by Copying Existing Pivot Table

Lecture 204 Reducing File Memory by Deleting the Data Source

Lecture 205 Reducing file memory by saving file as XLSB

Lecture 206 Reducing file memory by keeping data in Access

Lecture 207 Compatibility Issues with Excel 2007 and Excel 2010

Lecture 208 Sharing a Pivot Table via OneDrive

Section 14: More learnings

Lecture 209 Sales Forecasting with Calculated Fields

Lecture 210 Consolidate with a Pivot Table

Lecture 211 Frequency Distribution with a Pivot Table

Lecture 212 Break Even Model with a Pivot Table

Lecture 213 Several slicer custom styles for you to use

Lecture 214 Interactive Balance Sheet Pivot Table

Lecture 215 Monthly Sales Manager Performance

Lecture 216 Reconciling customer payments

Section 15: New Excel 2013 Pivot Table Features

Lecture 217 Cosmetic Changes

Lecture 218 Recommended Pivot Tables

Lecture 219 Distinct Count

Lecture 220 Timeline Slicer

Lecture 221 Data Model

Lecture 222 Group Periods

Lecture 223 Multiselect Slicers

Lecture 224 Pivot Chart Expand and Collapse

Lecture 225 3D Maps

Section 16: New Excel 2019 & Office 365 Pivot Table Features

Lecture 226 Which Excel Version

Lecture 227 Personalize the default Pivot Table layout

Lecture 228 Automatic relationship detection

Lecture 229 Automatic time grouping

Lecture 230 Search in the Pivot Table

Data Analysts: Professionals ready to elevate their skills by diving into advanced PivotTable techniques for in-depth data analysis.,Business Analysts: Those looking to deepen their analytical capabilities, creating more impactful reports using Excel’s PivotTables.,Managers and Decision Makers: Leaders who rely on large datasets for strategic insights and want to learn advanced methods for data-driven decisions.,Excel Power Users: Users who are comfortable with Excel and want to take their PivotTable skills to the next level with advanced techniques.,Students and Professionals: Anyone aiming to become proficient in data analysis for career growth or new opportunities in data-intensive roles.