Autoplay
Autocomplete
Previous Lesson
Complete and Continue
Microsoft Excel - Excel with Excel Pivot Tables Bootcamp
DESIGN & CUSTOMIZE YOUR PIVOT TABLE
Tabular Format (2:17)
No Gaps (2:23)
Formatting (1:20)
Excel Tables (2:38)
Clean Your Data Set (2:11)
Inserting a Pivot Table (3:14)
Field List (1:33)
Field List Areas (5:18)
Drill down to audit (1:35)
Sort Field List from A to Z (0:37)
Double click on any label to show more fields (0:46)
Defer Layout Update (0:48)
Pivot cache explained (0:53)
Refresh a Pivot Table (1:12)
Refresh All (2:20)
Refresh External Data (4:51)
Import from Access database (2:19)
Change Data Source (3:03)
Clear Filter and Clear Pivot (1:10)
Select and Format (3:08)
Move a Pivot Table (0:53)
Pivot Table Styles (3:35)
Customise Styles (4:05)
Use a customised style in another workbook (1:20)
Subtotals (0:48)
Grand Totals (0:54)
Report Layout (2:41)
Blank Rows (0:32)
Show the Classic Pivot Table layout (1:00)
Expand and Collapse Buttons (4:19)
Move and Remove Fields and Items (2:32)
Show or Hide Field List (0:20)
Show or Hide Field Headers (0:19)
Change Count of to Sum of (1:01)
Number formatting (1:34)
Field name formatting (2:09)
Predetermined number formatting (1:21)
Change Sum views in label areas (0:44)
Indent rows in Compact Layout (0:52)
Change layout of a report filter (1:18)
Format error values (0:42)
Format empty cells (1:20)
Keep column widths upon refresh (0:46)
Automatically refresh a pivot table (0:48)
Printing a pivot table on two pages (1:13)
Show report filter on multiple pages (2:15)
VALUE FIELD SETTINGS > SUMMARIZE VALUES BY
Create multiple subtotals (1:36)
Count (2:19)
Average (2:35)
Maximum (1:55)
Minimum (1:18)
Product (3:31)
CountNumbers (2:26)
StdDevp (6:59)
VarP (3:28)
Show Various Grand Totals (2:28)
Shortcuts to Value Field Settings (2:12)
See all pivot Items (5:52)
Show a unique count (2:29)
VALUE FIELD SETTINGS > SHOW VALUES AS
Percentage of Grand Total (2:01)
Percentage of Column Total (2:08)
Percentage of Row Total (1:47)
Percentage Of (3:56)
Percentage of Parent Row Total (3:12)
Percentage of Parent Column Total (2:16)
Percentage of Parent Total (2:44)
Difference From (4:47)
Percentage Difference From (4:11)
Running Total In (2:12)
Percentage Running Total In (2:51)
Rank Smallest to Largest (2:14)
Rank Largest to Smallest (2:14)
Index (3:38)
Shortcuts to Show Values As (1:08)
BUSINESS CASE - SHOW VALUES AS
Percentage of Revenue (2:31)
Actual v Plan Variation (4:36)
GROUPING
Group by Date (2:38)
Grouping by Months (1:43)
Grouping by Quarters and Years (1:41)
Group by Sales ranges (3:11)
Group by Text fields (2:09)
Group by Time (1:38)
Shortcuts to Grouping (1:32)
Grouping by Half Years (1:48)
Grouping by a Date that starts on a Monday (1:53)
Grouping by a custom date (1:41)
Grouping by a fiscal year and quarter (6:29)
Errors when grouping by dates (2:46)
Group two pivot tables in the same workbook (3:45)
Fixing the problem of counting grouped sales (0:38)
Display dates that have no data (0:52)
BUSINESS CASE - GROUPING
Min and Max Bank Balance (4:35)
Quarterly Comparative Report (6:37)
SORT
Sorting by Largest or Smallest (1:51)
Sort an Item Row (1:24)
Sort Manually (1:53)
Sort using a Custom List (3:13)
Override a Custom List sort (1:20)
Sort rows from A to Z and values by Z to A (1:16)
Sort new items added to your data source (1:11)
Clear a Sort (0:21)
Sort Largest to Smallest Grand Totals (0:19)
FILTER
Filter by Dates (6:41)
Filter by Labels - Text (2:35)
Filter by Labels - Numerical Text (2:44)
Filter by Values (3:50)
Filter by Vales - Top or Bottom 10 Items (2:06)
Filter by Values - Top or Bottom Percent (1:39)
Filter by Values - Top or Bottom Sum (1:42)
Filter by Report Filter (3:31)
Shortcuts to Filter (1:36)
Keep or hide selected items (0:57)
Filter by Text wildcards (3:19)
Filter by multiple fields (1:00)
Apply multiple filters (1:45)
Filter by multiple values (1:06)
Include new items in manual filter (1:50)
Clear filters with one click (1:30)
Add a filter for the column items (0:32)
BUSINESS CASE - FILTER
Top 5 Expense Report (2:22)
Top 25 percent of Channel Partners (2:10)
SLICERS (NEW IN EXCEL 2010)
Insert a Slicer (3:54)
Slicer Styles (2:18)
Creating a custom style (5:30)
Copying a custom style in to a new workbook (1:33)
Slicer Settings (4:42)
Slicer Size and Properties (2:56)
Slicer Connections for multiple pivot tables (3:16)
Different ways to filter a Slicer (1:14)
Use one slicer for two pivot tables (1:19)
Lock the workbook but not the slicer (1:25)
Interactive employee photos with Slicers! (9:20)
BUSINESS CASE - SLICERS
Select a monthly PnL report with a Slicer (4:13)
Base, Best and Worst case Forecast (5:02)
CALCULATED FIELDS & ITEMS
Creating a Calculated Field (3:58)
Use an existing calculated field (2:16)
Editing a calculated field (1:56)
Excel formulas and Calculated Fields (2:59)
Creating a Calculated Item (4:05)
Use an existing calculated item (1:36)
Editing a Calculated Item (2:18)
Excel formulas and Calculated Items (1:50)
Calculated Item on Column Labels (2:06)
Shortcomings of Calculated Items (2:03)
Solve Order for Calculated Items (4:04)
List Formulas (1:05)
Remove a Calculated Field temporarily (0:52)
Order of operations (1:49)
BUSINESS CASE - CALCULATED FIELDS
Creating a PnL Pivot Table report (8:18)
Actual v Plan with Calculated Fields (6:29)
PIVOT CHARTS
Insert a Pivot Chart (3:16)
Insert a Slicer with a Pivot Chart (1:52)
Pivot Chart Designs (3:48)
Pivot Chart Layouts (4:57)
Pivot Chart Formats (5:50)
Limitations of Pivot Charts and workarounds (1:54)
Saving a Pivot Chart template (1:28)
Shortcuts to formatting a Pivot Chart (1:16)
Link chart title to a pivot cell (0:58)
Copying a second chart (0:51)
Put a chart on a separate page with F11 (0:40)
Insert Pivot Chart straight from the data source (0:51)
Paste Pivot Chart to your email as a picture (1:05)
Paste Pivot Chart To Powerpoint (1:34)
Printing a Pivot Chart (1:20)
Include a Sparkline with your pivot table (1:16)
Chart Do's and Dont's (2:37)
Use Slicers to change chart types (8:00)
Workaround to creating a Scatter graph (2:20)
BUSINESS CASE - PIVOT CHARTS
PnL Pivot table report with Graphs (8:14)
Pivot Table Slicer and Chart EXCEL Dashboard (15:47)
CONDITIONAL FORMATTING PIVOT TABLES
Intro to conditional formatting (2:35)
Highlight Cell Rules based on values (1:20)
Highlight Cell Rules based on text labels (0:47)
Highlight Cell Rules based on date labels (1:15)
Top and Bottom Rules (3:24)
Data Bars, Color Scales and Icon Sets (5:42)
Format cells that contain - For Bonuses (1:38)
Format Top ranked values (1:32)
Format values above the average (1:37)
Use a formula to determine which cells to format (2:39)
Use selected cells to format multiple fields (1:31)
All cells showing values (1:31)
Control Conditional Formatting with Slicers (2:46)
Show text in the Pivot Table Values area (4:43)
Conditionally Formatting Blank Cells (1:06)
BUSINESS CASE - CONDITIONAL FORMATTING
Accounts Receivable Ageing Report Matrix (6:09)
Conditionally Format your sales results (3:25)
GETPIVOTDATA formula
Intro to GETPIVOTDATA (4:51)
Create a custom report with GETPIVOTDATA (5:17)
Reference Dates with GETPIVOTDATA (3:09)
Data validation with GETPIVOTDATA (2:45)
Shortfalls of GETPIVOTDATA (1:40)
Grand Totals to the left of the pivot table (3:11)
BUSINESS CASE - GETPIVOTDATA
Live forecasting with GETPIVOTDATA (7:16)
Channel analysis with GETPIVOTDATA (5:22)
MACROS
Adding the Developer tab (2:32)
Record a simple macro to Refresh a pivot table (4:18)
Date filter macro (4:20)
Different pivot table views macro (4:34)
Top 10 macro (3:23)
Add macro to quick access toolbar (1:13)
DATA MANAGEMENT
Reducing file memory by copying existing pivot table (1:21)
Reducing file memory by deleting the data source (1:38)
Reducing file memory by saving file as XLSB (0:50)
Reducing file memory by keeping data in Access (2:05)
Compatibility Issues with Excel 2007 and Excel 2010 (1:12)
Sharing a Pivot Table via OneDrive (2:41)
BONUS VIDEOS
Sales Forecasting with Calculated Fields (4:11)
Consolidate with a Pivot Table (4:38)
Frequency Distribution with a Pivot Table (2:09)
Break Even Model with a Pivot Table (2:57)
Several slicer custom styles for you to use (1:56)
Interactive Balance Sheet Pivot Table (12:13)
Monthly Sales Manager Performance (4:27)
Reconciling customer payments (1:34)
NEW EXCEL 2013 PIVOT TABLE FEATURES
Cosmetic Changes (2:05)
Recommended Pivot Tables (1:58)
Distinct Count (1:50)
Timeline Slicer (4:15)
Data Model (6:58)
NEW EXCEL 2016 PIVOT TABLE FEATURES
Multiselect Slicers (0:33)
Group Periods (1:46)
Pivot Chart Expand and Collapse (1:11)
3D Maps (1:45)
NEW EXCEL 2019 & OFFICE 365 PIVOT TABLE FEATURES
Which Excel Version (1:52)
Personalize the default Pivot Table layout (5:30)
Automatic relationship detection (5:38)
Automatic time grouping (2:50)
Search in the Pivot Table (1:25)
Reducing file memory by copying existing pivot table
Lesson content locked
If you're already enrolled,
you'll need to login
.
Enroll in Course to Unlock