Published 11/2024
MP4 | Video: h264, 1920×1080 | Audio: AAC, 44.1 KHz
Language: English | Size: 9.32 GB | Duration: 10h 12m
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.
Password/解压密码www.tbtos.com
转载请注明:0daytown » Excel Xtreme Pivot Tables