Session 1.1 ARRANGING YOUR DATA SET
1.11 | Tabular Format | 02:17 | Beginner – Intermediate | |
1.12 | No gaps | 02:14 | Beginner – Intermediate | |
1.13 | Formatting | 01:17 | Beginner – Intermediate | |
1.14 | Tables | 02:36 | Beginner – Intermediate | |
1.15 | Clean Your Data Set | 02:08 | Beginner – Intermediate |
Session 1.2 CREATING A PIVOT TABLE
1.21 | Inserting a Pivot Table | 03:12 | Beginner – Intermediate | |
1.22 | Field List – Activate, move, resize & layout | 01:30 | Beginner – Intermediate | |
1.23 | Field List & Areas | 05:33 | Beginner – Intermediate | |
1.24 | Drill down to audit | 01:33 | Beginner – Intermediate | |
1.25 | Sort Field List from A to Z | 00:35 | Beginner – Intermediate | |
1.26 | Double click on any labels to show more Fields | 00:43 | Beginner – Intermediate | |
1.27 | Defer Layout Update | 00:45 | Beginner – Intermediate |
Session 1.3 OPTIONS > DATA
1.31 | Pivot Cache explained | 00:54 | Beginner – Intermediate | |
1.32 | Refresh | 01:11 | Beginner – Intermediate | |
1.33 | Refresh All | 02:11 | Beginner – Intermediate | |
1.34 | Refresh External Data | 06:00 | Intermediate – Advanced | |
1.35 | Import from Access database | 02:18 | Intermediate – Advanced | |
1.36 | Change Data Source | 03:31 | Beginner – Intermediate |
Session 1.4 OPTIONS > ACTIONS
1.41 | Clear Filters & Clear Pivot | 01:08 | Beginner – Intermediate | |
1.42 | Select & format | 03:26 | Beginner – Intermediate | |
1.43 | Move pivot table | 00:50 | Beginner – Intermediate |
Session 1.5 DESIGN > STYLES
1.51 | Pivot Table Styles | 03:44 | Beginner – Intermediate | |
1.52 | Customising a Pivot Table Style | 04:14 | Intermediate – Advanced | |
1.53 | Use customised style in another workbook | 01:17 | Beginner – Intermediate |
Session 1.6 DESIGN > LAYOUT
1.61 | Subtotals | 01:00 | Beginner – Intermediate | |
1.62 | Grand Totals | 00:53 | Beginner – Intermediate | |
1.63 | Report Layout | 02:43 | Beginner – Intermediate | |
1.64 | Blank Row | 00:33 | Beginner – Intermediate | |
1.65 | Show the Classic Pivot Table Layout | 00:58 | Beginner – Intermediate |
Session 1.7 OPTIONS > SHOW
1.71 | Expand & Collapse Buttons | 04:48 | Beginner – Intermediate | |
1.72 | Move & Remove Fields | 02:37 | Beginner – Intermediate | |
1.73 | Show/Hide Field List | 00:17 | Beginner – Intermediate | |
1.74 | Show/Hide Field Headers | 00:17 | Beginner – Intermediate |
Session 1.8 CUSTOMISATION
1.81 | Change Count of to Sum of | 01:08 | Beginner – Intermediate | |
1.82 | Number formatting | 01:04 | Beginner – Intermediate | |
1.83 | Field name formatting | 02:15 | Beginner – Intermediate | |
1.84 | Predetermined number formatting | 01:21 | Beginner – Intermediate | |
1.85 | Change Sum views in label areas | 00:42 | Beginner – Intermediate |
Session 1.9 PIVOTTABLE OPTIONS > LAYOUT & FORMAT
1.91 | Indent rows in compact layout | 00:05 | Beginner – Intermediate | |
1.92 | Change the layout of a report filter | 01:18 | Beginner – Intermediate | |
1.93 | Format error values | 00:54 | Beginner – Intermediate | |
1.94 | Format empty cells | 01:18 | Beginner – Intermediate | |
1.95 | Keep column widths upon refresh | 00:47 | Beginner – Intermediate | |
1.96 | Automatic Refresh a pivot table | 00:46 | Beginner – Intermediate | |
1.97 | Printing a pivot table on two pages | 01:11 | Beginner – Intermediate | |
1.98 | Show report filter on multiple pages | 02:13 | Beginner – Intermediate |
Session 2 VALUE FIELD SETTINGS > SUMMARIZE VALUES BY
2.1 | Create multiple subtotals | 01:35 | Beginner – Intermediate | |
2.2 | Count | 02:27 | Beginner – Intermediate | |
2.3 | Average | 02:04 | Beginner – Intermediate | |
2.4 | Maximum | 02:00 | Beginner – Intermediate | |
2.5 | Minimum | 01:22 | Beginner – Intermediate | |
2.6 | Product | 03:33 | Beginner – Intermediate | |
2.7 | Count Numbers | 02:04 | Beginner – Intermediate | |
2.8 | Std Dev | 07:05 | Intermediate – Advanced | |
2.9 | Varp | 03:57 | Intermediate – Advanced | |
2.10 | Show various Grand Totals | 02:26 | Beginner – Intermediate | |
2.11 | Shortcuts to Field & Value Field Settings | 02:01 | Beginner – Intermediate | |
2.12 | See all pivot Items | 06:00 | Intermediate – Advanced | |
2.13 | Show a unique count | 02:26 | Beginner – Intermediate |
Session 3 VALUE FIELD SETTINGS > SHOW VALUES AS
3.1 | % of Grand Total | 02:11 | Beginner – Intermediate | |
3.2 | % of Column Total | 02:11 | Beginner – Intermediate | |
3.3 | % of Row Total | 01:05 | Beginner – Intermediate | |
3.4 | % Of | 04:01 | Beginner – Intermediate | |
3.5 | % of Parent Row Total (NEW IN EXCEL 2010) | 03:09 | Beginner – Intermediate | |
3.6 | % of Parent Column Total (NEW IN EXCEL 2010) | 02:22 | Beginner – Intermediate | |
3.7 | % of Parent Total (NEW IN EXCEL 2010) | 03:20 | Beginner – Intermediate | |
3.8 | Difference From | 04:44 | Beginner – Intermediate | |
3.9 | % Difference From | 04:30 | Beginner – Intermediate | |
3.10 | Running Total in | 02:12 | Beginner – Intermediate | |
3.11 | % Running Total in (NEW IN EXCEL 2010) | 03:10 | Beginner – Intermediate | |
3.12 | Rank Smallest to Largest (NEW IN EXCEL 2010) | 02:14 | Beginner – Intermediate | |
3.13 | Rank Largest to Smallest (NEW IN EXCEL 2010) | 02:15 | Beginner – Intermediate | |
3.14 | Index | 03:38 | Beginner – Intermediate | |
3.15 | Shortcuts to Show Values As | 01:07 | Beginner – Intermediate | |
3.16 | ACCOUNTING: % of Revenue Margins | 02:28 | Beginner – Intermediate | |
3.17 | FINANCIAL: Actual v Plan Variance Report | 04:33 | Beginner – Intermediate |
Session 4 GROUPING
4.1 | Group by Date | 02:45 | Beginner – Intermediate | |
4.2 | Group by Month | 01:44 | Beginner – Intermediate | |
4.3 | Group by Quarters & Years | 01:45 | Beginner – Intermediate | |
4.4 | Group by Sales ranges | 03:12 | Beginner – Intermediate | |
4.5 | Group by Text fields | 02:14 | Beginner – Intermediate | |
4.6 | Group by Time | 01:45 | Beginner – Intermediate | |
4.7 | Shortcuts to Grouping | 01:35 | Beginner – Intermediate | |
4.8 | Grouping by Half Years | 01:48 | Beginner – Intermediate | |
4.9 | Group by a Date that starts on a Monday | 01:58 | Beginner – Intermediate | |
4.10 | Grouping by a custom date | 01:41 | Beginner – Intermediate | |
4.11 | Group by fiscal years & quarters | 06:44 | Intermediate – Advanced | |
4.12 | Errors when grouping by date | 02:50 | Beginner – Intermediate | |
4.13 | Group two pivot tables independently | 03:46 | Beginner – Intermediate | |
4.14 | Fixing the problem of counting grouped sales | 00:35 | Beginner – Intermediate | |
4.15 | Display dates that have no data | 00:49 | Beginner – Intermediate | |
4.16 | ACCOUNTING: Quarterly Comparative Report | 06:35 | Intermediate – Advanced | |
4.17 | FINANCIAL: Min & Max Bank Balance | 04:32 | Beginner – Intermediate |
Session 5 SORT
5.1 | Sorting by Largest or Smallest | 01:57 | Beginner – Intermediate | |
5.2 | Sort an Item Row (Left to Right) | 01:21 | Beginner – Intermediate | |
5.3 | Sort manually (drag, write, right click) | 01:55 | Beginner – Intermediate | |
5.4 | Sort using a Custom List | 03:22 | Beginner – Intermediate | |
5.5 | Override a Custom List sort | 01:19 | Beginner – Intermediate | |
5.6 | Sort row from A-Z and sales from Z-A | 01:14 | Beginner – Intermediate | |
5.7 | Sort new items added to your data source | 01:12 | Beginner – Intermediate | |
5.8 | Clear a sort | 00:18 | Beginner – Intermediate | |
5.9 | Sort Largest to Smallest Grand Totals | 00:16 | Beginner – Intermediate |
Session 6 FILTER
6.1 | Filter by Dates | 06:45 | Beginner – Intermediate | |
6.2 | Filter by Labels – Text | 02:35 | Beginner – Intermediate | |
6.3 | Filter by Labels – Numerical Text | 02:45 | Beginner – Intermediate | |
6.4 | Filter by Values | 04:37 | Beginner – Intermediate | |
6.5 | Filter by Values – Top or Bottom 10 Items | 02:04 | Beginner – Intermediate | |
6.6 | Filter by Values – Top or Bottom % | 01:41 | Beginner – Intermediate | |
6.7 | Filter by Values – Top or Bottom Sum | 01:39 | Beginner – Intermediate | |
6.8 | Filter by Report Filter | 04:14 | Beginner – Intermediate | |
6.9 | Shortcuts to filters | 01:33 | Beginner – Intermediate | |
6.10 | Keep or hide selected items | 00:48 | Beginner – Intermediate | |
6.11 | Filter by Text wildcards * and ? | 03:11 | Beginner – Intermediate | |
6.12 | Filter by multiple fields | 00:57 | Beginner – Intermediate | |
6.13 | Apply multiple filters | 01:43 | Beginner – Intermediate | |
6.14 | Filter by multiple values | 01:05 | Beginner – Intermediate | |
6.15 | Include new items in manual filter | 01:50 | Beginner – Intermediate | |
6.16 | Clear filters with one click | 01:32 | Beginner – Intermediate | |
6.17 | Add a filter for the column items | 00:30 | Beginner – Intermediate | |
6.18 | ACCOUNTING: Top 5 Expenses report | 02:20 | Beginner – Intermediate | |
6.19 | FINANCIAL: Top 25% of Channel Partners | 02:08 | Beginner – Intermediate |
Session 7 SLICERS
7.1 | Insert a Slicer | 04:12 | Beginner – Intermediate | |
7.2 | Slicer Styles | 02:21 | Beginner – Intermediate | |
7.3 | Creating a custom style | 06:19 | Intermediate – Advanced | |
7.4 | Copy a custom style into a new workbook | 01:32 | Beginner – Intermediate | |
7.5 | Slicer Settings | 04:46 | Beginner – Intermediate | |
7.6 | Slicer Size & Properties | 03:03 | Beginner – Intermediate | |
7.7 | Slicer Connections for multiple pivot tables | 03:18 | Beginner – Intermediate | |
7.8 | Different ways to filter a Slicer | 01:13 | Beginner – Intermediate | |
7.9 | Use one slicer for two pivot tables | 01:16 | Beginner – Intermediate | |
7.10 | Lock the workbook but not the slicer | 01:23 | Beginner – Intermediate | |
7.11 | Interactive employee photos with Slicers" FUN" | 10:16 | Intermediate – Advanced | |
7.12 | ACCOUNTING: Select a Monthly P&L report with a Slicer | 04:10 | Beginner – Intermediate | |
7.13 | FINANCIAL: Base, Best & Worst case Forecast | 05:11 | Intermediate – Advanced |
Session 8 CALCULATED FIELDS & ITEMS
8.1 | Creating a Calculated Field | 04:39 | Beginner – Intermediate | |
8.2 | Use an existing Calculated Field in a new calculation | 02:21 | Beginner – Intermediate | |
8.3 | Editing a Calculated Field | 01:59 | Beginner – Intermediate | |
8.4 | Excel formulas & Calculated Fields | 03:03 | Beginner – Intermediate | |
8.5 | Creating a Calculated Item | 04:18 | Beginner – Intermediate | |
8.6 | Use an existing Calculated Item in a new calculation | 01:37 | Beginner – Intermediate | |
8.7 | Editing a Calculated Item | 02:20 | Beginner – Intermediate | |
8.8 | Excel formulas & Calculated Items | 01:48 | Beginner – Intermediate | |
8.9 | Calculated Item on Column Labels | 02:21 | Beginner – Intermediate | |
8.10 | Shortcomings of Calculated Items | 01:48 | Beginner – Intermediate | |
8.11 | Solve Order for Calculated Items | 04:23 | Intermediate – Advanced | |
8.12 | List Calculated Field & Item formulas | 01:04 | Beginner – Intermediate | |
8.13 | Remove a Calculated Field temporarily | 00:51 | Beginner – Intermediate | |
8.14 | Order of operations | 01:48 | Beginner – Intermediate | |
8.15 | ACCOUNTING: Creating a P&L Pivot Table Report | 08:15 | Intermediate – Advanced | |
8.16 | FINANCIAL: Actuals v Plan with Calculated Fields | 06:26 | Beginner – Intermediate |
Session 9 PIVOT CHARTS
9.1 | Insert a Pivot Chart | 03:18 | Beginner – Intermediate | |
9.2 | Insert a Slicer with a Pivot Chart | 01:50 | Beginner – Intermediate | |
9.3 | Pivot Chart Designs | 03:49 | Beginner – Intermediate | |
9.4 | Pivot Chart Layouts | 04:59 | Beginner – Intermediate | |
9.5 | Pivot Chart Formats | 05:50 | Beginner – Intermediate | |
9.6 | Limitations of Pivot Charts & workarounds | 01:54 | Beginner – Intermediate | |
9.7 | Saving a pivot chart template | 01:26 | Beginner – Intermediate | |
9.8 | Shortcuts to formatting a Pivot Chart | 01:15 | Beginner – Intermediate | |
9.9 | Link chart title to a pivot cell | 00:55 | Beginner – Intermediate | |
9.10 | Copying a second chart | 00:41 | Beginner – Intermediate | |
9.11 | Put a chart on a separate page with F11 | 00:27 | Beginner – Intermediate | |
9.12 | Insert Pivot Chart straight from the data source | 00:49 | Beginner – Intermediate | |
9.13 | Paste Pivot Chart to your email as a picture | 01:02 | Beginner – Intermediate | |
9.14 | Paste Pivot Chart to PowerPoint & make live updates | 01:26 | Beginner – Intermediate | |
9.15 | Printing a Pivot Chart | 01:18 | Beginner – Intermediate | |
9.16 | Include a Sparkline with your pivot table | 01:14 | Beginner – Intermediate | |
9.17 | Charts Do´s & Don’ts | 02:51 | Beginner – Intermediate | |
9.18 | Change Chart Type with Slicers" FUN""" | 08:28 | Intermediate – Advanced | |
9.19 | Workaround to creating an interactive Scatter graph | 02:17 | Beginner – Intermediate | |
9.20 | ACCOUNTING: P&L Pivot Table report with Graphs | 08:18 | Intermediate – Advanced | |
9.21 | FINANCIAL: Pivot Table Slicer & Chart Dashboard | 15:45 | Intermediate – Advanced |
Session 10 CONDITIONAL FORMATTING PIVOTTABLES
10.1 | Intro to conditional formatting | 02:33 | Beginner – Intermediate | |
10.2 | Highlight Cell Rules based on values | 01:18 | Beginner – Intermediate | |
10.3 | Highlight Cell Rules based on text labels | 00:44 | Beginner – Intermediate | |
10.4 | Highlight Cell Rules based on date labels | 01:13 | Beginner – Intermediate | |
10.5 | Top & Bottom Rules | 03:23 | Beginner – Intermediate | |
10.6 | Data Bars, Colour Scales & Icon Sets | 05:49 | Beginner – Intermediate | |
10.7 | Format only cells that contain – For Bonuses | 01:39 | Beginner – Intermediate | |
10.8 | Format only Top or Bottom ranked values – Top 3 sales per year | 01:30 | Beginner – Intermediate | |
10.9 | Format values that are above or below the average – For Promotions | 01:36 | Beginner – Intermediate | |
10.10 | Use a formula to determine which cells to format | 02:33 | Beginner – Intermediate | |
10.11 | Use selected cells to format multiple fields | 01:30 | Beginner – Intermediate | |
10.12 | All cells showing values to format multiple fields | 01:30 | Beginner – Intermediate | |
10.13 | Control Conditional Formatting with Slicers | 02:52 | Intermediate – Advanced | |
10.14 | Show text in the Pivot Table Values area | 04:42 | Intermediate – Advanced | |
10.15 | Cond Format blank cells or cells | 01:04 | Beginner – Intermediate | |
10.16 | ACCOUNTING: Accounts Receivable Ageing Report Matrix | 06:08 | Intermediate – Advanced | |
10.17 | FINANCIAL: Conditionally Format your sales results | 03:24 | Beginner – Intermediate |
Session 11 GETPIVOTDATA Function
11.1 | Intro to GETPIVOTDATA | 04:59 | Beginner – Intermediate | |
11.2 | Create a custom report with GETPIVOTDATA | 05:15 | Intermediate – Advanced | |
11.3 | Reference Dates with GETPIVOTDATA | 03:12 | Beginner – Intermediate | |
11.4 | Data validation with GETPIVOTDATA | 02:45 | Beginner – Intermediate | |
11.5 | Shortfalls of GETPIVOTDATA | 01:38 | Beginner – Intermediate | |
11.6 | Grand Totals to the left of the pivot table | 03:11 | Intermediate – Advanced | |
11.7 | ACCOUNTING: Live forecasting with GETPIVOTDATA | 07:14 | Intermediate – Advanced | |
11.8 | FINANCIAL: Channel Analysis with GETPIVOTDATA | 05:20 | Intermediate – Advanced |
Session 12 MACROS
12.1 | Adding the Developer tab & disabling macros | 02:31 | Beginner – Intermediate | |
12.2 | Record a simple macro to Refresh a pivot table | 04:18 | Intermediate – Advanced | |
12.3 | Date filter macro | 04:26 | Intermediate – Advanced | |
12.4 | Different pivot table views macro | 04:33 | Intermediate – Advanced | |
12.5 | Top 10 macro | 03:26 | Beginner – Intermediate | |
12.6 | Add macro to quick access toolbar | 01:10 | Beginner – Intermediate |
Session 13 DATA MANAGEMENT
13.1 | Reducing file memory by copying existing pivot table | 00:50 | Beginner – Intermediate | |
13.2 | Reducing file memory by deleting the data source | 01:36 | Beginner – Intermediate | |
13.3 | Reducing file memory by saving file as Excel Binary Workbook | 00:47 | Beginner – Intermediate | |
13.4 | Reducing file memory by keeping data source in MS Access | 02:02 | Beginner – Intermediate | |
13.5 | Compatibility Issues with Excel 2007 and Excel 2010 | 01:05 | Beginner – Intermediate | |
13.6 | Sharing a Pivot Table via OneDrive | 02:39 | Beginner – Intermediate |
Session 14 BONUS VIDEOS
14.1 | Sales Forecasting with Calculated Fields | 04:11 | Beginner – Intermediate | |
14.2 | Consolidate with a Pivot Table | 03:14 | Beginner – Intermediate | |
14.3 | Frequency distribution with a Pivot Table | 02:06 | Beginner – Intermediate | |
14.4 | Break Even Model | 02:55 | Beginner – Intermediate | |
14.5 | Several slicer custom styles for you to use | 01:53 | Beginner – Intermediate | |
14.6 | Interactive Balance Sheet Pivot Table | 12:11 | Intermediate – Advanced | |
14.7 | Monthly Sales Manager Performance Report | 04:25 | Intermediate – Advanced | |
14.8 | Reconciling Customer Payments | 04:00 | Beginner – Intermediate |
Session 15 - NEW EXCEL 2013 PIVOT TABLE FEATURES
15.1 | Cosmetic Changes in the Pivot Table Tools Ribbon Menu | 02:02 | Beginner – Intermediate | |
15.2 | Recommended Pivot Tables | 01:55 | Beginner – Intermediate | |
15.3 | Distinct Count | 01:47 | Beginner – Intermediate | |
15.4 | Timeline Slicers | 04:12 | Beginner – Intermediate | |
15.5 | Data Models | 06:55 | Intermediate - Advanced |
Session 16 - NEW EXCEL 2016 PIVOT TABLE FEATURES
16.1 | Group Periods | 01:43 | Beginner – Intermediate | |
16.2 | Multi-Select Slicer Items | 00:30 | Beginner – Intermediate | |
16.3 | Pivot Chart - Expand and Collapse Fields | 01:08 | Beginner – Intermediate | |
16.4 | 3D Maps | 01:42 | Beginner – Intermediate |
Session 17 - NEW EXCEL 2019 PIVOT TABLE FEATURES
17.1 | Which Excel Version | 01:49 | Beginner – Intermediate | |
17.2 | Personalize the Default PivotTable Layout | 05:27 | Beginner – Intermediate | |
17.3 | Automatic Relationship Detection | 05:35 | Intermediate - Advanced | |
17.4 | Automatic Time Grouping | 02:50 | Beginner – Intermediate | |
17.5 | Search in the PivotTable | 01:22 | Beginner – Intermediate |