Autoplay
Autocomplete
Previous Lesson
Complete and Continue
Become a Pivot Table Ninja and Create awesome dashboards
Instructions Manual
Manual
INTRODUCTION
INTRODUCTION (1:21)
XTREME PIVOT TABLE COURSE TRACKER
BONUS: PIVOT TABLE CHEAT SHEET
BONUS: TOP 20 EXCEL SHORTCUTS
BONUS: EXCEL RESOURCE BLOG
Course Review
1 - CUSTOMIZING YOUR PIVOT TABLE
WORKBOOKS
ARRANGING YOUR DATA SET ~ Tabular Format (2:25)
ARRANGING YOUR DATA SET ~ No Gaps (2:30)
ARRANGING YOUR DATA SET ~ Formatting (1:27)
ARRANGING YOUR DATA SET ~ Tables (2:46)
ARRANGING YOUR DATA SET ~ TIP: Clean Your Data Set (2:18)
CREATING A PIVOT TABLE ~ Inserting a Pivot Table (3:22)
CREATING A PIVOT TABLE ~ Field List - Activate, move, resize & layout (1:40)
CREATING A PIVOT TABLE ~ Field List & Areas (5:26)
CREATING A PIVOT TABLE ~ Drill down to audit (1:42)
CREATING A PIVOT TABLE ~ Sort Field List from A to Z (0:45)
CREATING A PIVOT TABLE ~ TIP: Double click on any labels to show more Fields (0:53)
CREATING A PIVOT TABLE ~ TIP: Defer Layout Update (0:55)
OPTIONS > DATA ~ Pivot Cache explained (1:01)
OPTIONS > DATA ~ Refresh (1:20)
OPTIONS > DATA ~ Refresh All (2:17)
OPTIONS > DATA ~ Refresh External Data (4:59)
OPTIONS > DATA ~ Import from Access database (2:27)
OPTIONS > DATA ~ Change Data Source (3:10)
OPTIONS > ACTIONS ~ Clear Filters & Clear Pivot (1:17)
OPTIONS > ACTIONS ~ Select & format (3:15)
OPTIONS > ACTIONS ~ Move a Pivot Table (1:01)
DESIGN > STYLES ~ Pivot Table Styles (3:43)
DESIGN > STYLES ~ Customising a Pivot Table Style (4:13)
DESIGN > STYLES ~ Use a customised style in another workbook (1:27)
DESIGN > LAYOUT ~ Subtotals (0:55)
DESIGN > LAYOUT ~ Grand Totals (1:01)
DESIGN > LAYOUT ~ Report Layouts (2:48)
DESIGN > LAYOUT ~ Blank Rows (0:40)
DESIGN > LAYOUT ~ TIP: Show the Classic Pivot Table Layout (1:08)
OPTIONS > SHOW ~ Expand & Collapse buttons (4:26)
OPTIONS > SHOW ~ Move & Remove Fields and Items (2:40)
OPTIONS > SHOW ~ Show/Hide Field List (0:27)
OPTIONS > SHOW ~ Show/Hide Field Headers (0:27)
CUSTOMISATION ~ Change "Count of" to "Sum of" (1:10)
CUSTOMISATION ~ Number formatting (1:42)
CUSTOMISATION ~ Field name formatting (2:16)
CUSTOMISATION ~ TIP: Predetermined number formatting (1:29)
CUSTOMISATION ~ TIP: Change Sum views in Label areas (0:52)
LAYOUT & FORMAT ~ Indent rows in compact layout (1:00)
LAYOUT & FORMAT ~ Change the layout of a report filter (1:25)
LAYOUT & FORMAT ~ Format error values (0:49)
LAYOUT & FORMAT ~ Format empty cells (1:27)
LAYOUT & FORMAT ~ Keep column widths upon refresh (0:53)
LAYOUT & FORMAT ~ TIP: Automatic Refresh a Pivot Table (0:56)
LAYOUT & FORMAT ~ TIP: Printing a pivot table on two pages (1:20)
LAYOUT & FORMAT ~ TIP:Show report filter on multiple pages (2:23)
2 - VALUE FIELD SETTINGS > SUMMARIZE VALUES BY
WORKBOOKS
Create multiple subtotals (1:44)
Count (2:27)
Average (2:43)
Maximum (2:03)
Minimum (1:26)
Product (3:39)
Count Numbers (2:26)
Std Dev (7:07)
Varp (3:36)
TIP: Show various Grand Totals (2:36)
TIP: Shortcuts to Field & Value Field Settings (2:19)
TIP: See all pivot Items (6:00)
TIP: Show a unique count (2:37)
Course Review
3 - VALUE FIELD SETTINGS > SHOW VALUES AS
WORKBOOKS
% of Grand Total (2:09)
% of Column Total (2:15)
% of Row Total (1:55)
% Of (4:03)
% of Parent Row Total (NEW IN EXCEL 2010) (3:20)
% of Parent Column Total (NEW IN EXCEL 2010) (2:24)
% of Parent Total (NEW IN EXCEL 2010) (2:52)
Difference From (4:55)
% Difference From (4:18)
Running Total in (2:20)
% Running Total in (NEW IN EXCEL 2010) (2:59)
Rank Smallest to Largest (NEW IN EXCEL 2010) (2:14)
Rank Largest to Smallest (NEW IN EXCEL 2010) (2:21)
Index (3:45)
TIP: Shortcuts to Show Values As (1:16)
CASE STUDY ~ ACCOUNTING: % of Revenue Margins (2:38)
CASE STUDY ~ FINANCIAL: Actual v Plan Variance Report (4:44)
4 - GROUPING
WORKBOOKS
Group by Date (2:45)
Group by Months (1:51)
Group by Quarters & Years (1:48)
Group by Sales ranges (3:19)
Group by Text fields (2:16)
Group by Time (1:45)
TIP: Shortcuts to Grouping (1:40)
TIP: Grouping by Half Years (1:55)
TIP: Group by a Date that starts on a Monday (2:00)
TIP: Grouping by a custom date (1:49)
TIP: Group by fiscal years & quarters (6:36)
TIP: Errors when grouping by dates (2:54)
TIP: Group two pivot tables independently (3:53)
TIP: Fixing the problem of counting grouped sales (0:46)
TIP: Display dates that have no data (0:59)
CASE STUDY ~ ACCOUNTING: Quarterly Comparative Report (6:45)
CASE STUDY ~ FINANCIAL: Min & Max Bank Balance (4:42)
5 - SORTING
WORKBOOKS
Sorting by Largest or Smallest (1:59)
Sort an Item Row (Left to Right) (1:31)
Sort manually (drag, write, right click) (2:01)
Sort using a Custom List (3:20)
Override a Custom List sort (1:27)
Sort row from A-Z and sales from Z-A (1:23)
TIP: Sort new items added to your data source (1:19)
TIP: Clear a sort (0:28)
TIP: Sort Largest to Smallest Grand Totals (0:26)
6 - FILTERING
WORKBOOKS
Filter by Dates (6:48)
Filter by Labels - Text (2:43)
Filter by Labels - Numerical Text (2:52)
Filter by Values (3:58)
Filter by Values - Top or Bottom 10 Items (2:13)
Filter by Values - Top or Bottom % (1:46)
Filter by Values - Top or Bottom Sum (1:49)
Filter by Report Filter (3:38)
TIP: Shortcuts to filters (1:43)
TIP: Keep or hide selected items (0:57)
TIP: Filter by Text wildcards * and ? (3:27)
TIP: Filter by multiple fields (1:07)
TIP: Apply multiple filters (1:52)
TIP: Filter by multiple values (1:14)
TIP: Include new items in manual filter (1:58)
TIP: Clear filters with one click (1:37)
TIP: Add a filter for the column items (0:40)
CASE STUDY ~ ACCOUNTING: Top 5 Expenses report (2:30)
CASE STUDY ~ FINANCIAL: Top 25% of Channel Partners (2:19)
7 - SLICERS (NEW IN EXCEL 2010)
WORKBOOKS
Insert a Slicer (4:01)
Slicer Styles (2:26)
Creating a custom style (5:38)
Copy a custom style into a new workbook (1:40)
Slicer Settings (4:50)
Slicer Size & Properties (3:03)
Slicer Connections for multiple pivot tables (3:24)
TIP: Different ways to filter a Slicer (1:23)
TIP: Use one slicer for two pivot tables (1:26)
TIP: Lock the workbook but not the slicer (1:33)
TIP: Interactive employee photos with Slicers! FUN! (9:28)
CASE STUDY ~ ACCOUNTING: Select a Monthly P&L report with a Slicer (4:21)
CASE STUDY ~ FINANCIAL: Base, Best & Worst case Forecast (5:10)
8 - CALCULATED FIELDS & ITEMS
WORKBOOKS
Creating a Calculated Field (4:06)
Use an existing Calculated Field in a new calculation (2:24)
Editing a Calculated Field (2:03)
Excel formulas & Calculated Fields (3:07)
Creating a Calculated Item (4:12)
Use an existing Calculated Item in a new calculation (1:43)
Editing a Calculated Item (2:26)
Excel formulas & Calculated Items (1:58)
Calculated Item on Column Labels (2:14)
Shortcomings of Calculated Items (2:04)
TIP: Solve Order for Calculated Items (4:11)
TIP: List Calculated Field & Item formulas (1:12)
TIP: Remove a Calculated Field temporarily (1:00)
TIP: Order of operations (1:56)
CASE STUDY ~ ACCOUNTING: Creating a P&L Pivot Table Report (8:25)
CASE STUDY ~ FINANCIAL: Actuals v Plan with Calculated Fields (6:36)
9 - PIVOT CHARTS
WORKBOOKS
Insert a Pivot Chart (3:24)
Insert a Slicer with a Pivot Chart (2:00)
Pivot Chart Designs (3:56)
Pivot Chart Layouts (5:04)
Pivot Chart Formats (5:57)
Limitations of Pivot Charts & workarounds (2:02)
TIP: Shortcuts to formatting a Pivot Chart (1:23)
TIP: Link chart title to a pivot cell (1:05)
TIP: Copying a second chart
TIP: Put a chart on a separate page with F11 (0:47)
TIP: Insert Pivot Chart straight from the data source (0:59)
TIP: Paste Pivot Chart to your email as a picture
TIP: Paste Pivot Chart to PowerPoint & make live updates
TIP: Printing a Pivot Chart
TIP: Charts Do´s & Don'ts
TIP: Change Chart Type with Slicers! FUN!!!
TIP: Workaround to creating an interactive Scatter graph
CASE STUDY ~ ACCOUNTING: P&L Pivot Table report with Graphs
CASE STUDY ~ FINANCIAL: Pivot Table Slicer & Chart Dashboard
10 - CONDITIONAL FORMATTING PIVOT TABLES
WORKBOOKS
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 & Bottom Rules
Data Bars, Color Scales & Icon Sets (NEW IN EXCEL 2010)
TIP: Format only cells that contain - For Bonuses
TIP: Format only Top or Bottom ranked values - Top 3 sales per year
TIP: Format values that are above or below the average - For Promotions
TIP: Use a formula to determine which cells to format
Use selected cells to format multiple fields
All cells showing values to format multiple fields
Control Conditional Formatting with Slicers
Show text in the Pivot Table Values area
Cond Format blank cells or cells
CASE STUDY ~ ACCOUNTING: Accounts Receivable Ageing Report Matrix
CASE STUDY ~ FINANCIAL: Conditionally Format your sales results
11 - GETPIVOTDATA Formula
WORKBOOKS
Intro to GETPIVOTDATA
Create a custom report with GETPIVOTDATA
Reference Dates with GETPIVOTDATA
Data validation with GETPIVOTDATA
Shortfalls of GETPIVOTDATA
TIP: Grand Totals to the left of the pivot table
CASE STUDY ~ ACCOUNTING: Live forecasting with GETPIVOTDATA
CASE STUDY ~ FINANCIAL: Channel Analysis with GETPIVOTDATA
12 - MACROS & PIVOT TABLES
WORKBOOKS
Adding the Developer tab & disabling macros
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
13 - DATA MANAGEMENT
WORKBOOKS
Reducing file memory by copying existing pivot table
Reducing file memory by deleting the data source
Reducing file memory by saving file as Excel Binary Workbook
Reducing file memory by keeping data source in MS Access
Compatibility Issues with Excel 2007 and Excel 2010
Sharing a Pivot Table via OneDrive
14 - BONUS VIDEOS
WORKBOOKS
Sales Forecasting with Calculated Fields
Consolidate with a Pivot Table
Frequency distribution with a Pivot Table
Break Even Model
Several Slicer custom styles for you to use
Interactive Balance Sheet Pivot Table
Monthly Sales Manager Performance Report
Reconciling customer payments
CASE STUDY ~ FINANCIAL: Top 25% of Channel Partners
Lesson content locked
If you're already enrolled,
you'll need to login
.
Enroll in Course to Unlock