Who is this course for?

24 downloads 3236 Views 11MB Size Report
If you are not satisfied with the tutorial, then I will return 100% of your money within 30 days ... Some concepts are not available in Excel 2007 (like Slicers and some Pivot Table ..... ACCOUNTING: Select a Monthly P&L report with a Slicer.
Who is this course for? This course is tailored for all levels of Excel users. From someone who has picked up Microsoft Excel for their first time, to someone who uses it around 2-3 hours a day, as well as long time Excel users who want to go deep in to learning all the commands, tricks and visual analytical reports that Pivot Tables have to offer.

What do you get with the Xtreme Pivot Table Course? Go and make yourself a coffee or tea, because this may take some time :) 1

More than 200 videos (217 to be exact!) which equates to over 9 hours of video tutorials. Online viewing only or Online viewing + Downloadable videos, depending on the plan that you choose;

2

Access to all the Excel workbooks that are shown in the training videos so you can practice the new tricks learned;

3

HD quality short videos viewable from your PC, Laptop, Tablet device or Mobile/Cell phone;

4

Bonus Pivot Table Cheat Sheet to stick on your desk that highlights the most important Pivot Table commands visually;

5

Bonus Excel workbook that shows all the Course content. You can tick off and rate each tutorial once viewed and track your progress;

6

My Top 20 Excel keyboard shortcuts that will save you heaps of time! They are in an animated gif format which lets you sit back and view each shortcut live on your web browser;

7

20% discount on the time saving Pivot Power Add-In from Contextures;

8

Personal Email and Skype support during 12 months from purchase to help you whenever you are stuck with a Pivot Table problem or query. You will always receive a 25% discount whenever you purchase new courses from myexcelonline.com

9 10

If you are not satisfied with the tutorial, then I will return 100% of your money within 30 days without any questions asked. That's my personal guarantee to you!

02

www.myexcelonline.com

Can I view an example of the videos & workbooks?

ABSOLUTELY! There is a free video for each chapter, plus two case studies and a bonus dashboard video for you to view at www.myexcelonline.com/courses. So that's a total of 16 videos that you can view for free! You can also download the respective workbooks so you can keep and practice.

What Excel version is the training compatible with?

The training was recorded using Excel 2010 and it is compatible with Excel 2007 and Excel 2013. Some concepts are not available in Excel 2007 (like Slicers and some Pivot Table Calculations) but this is outlined in the Course Outline in the next page.

What payment methods are accepted?

No matter what part of the world you are in, you can pay using your Debit or Credit Card from the following list: Visa, MasterCard, AMEX, Discover, Diners etc. If you want to make an electronic bank transfer then get in contact with us at [email protected]

03

www.myexcelonline.com

More questions?

ORDER NOW

Please get in contact with us at [email protected] or +34 645 899 767 and I will be glad to answer any questions regarding this course that you may have.

Course Outline SEQUENCE

CONTENTS

1 1.1 1.11 1.12 1.13 1.14

CUSTOMISE YOUR PIVOT TABLE ARRANGING YOUR DATA SET Tabular Format No gaps Formatting Tables TIPS Clean Your Data Set CREATING A PIVOT TABLE Inserting a Pivot Table Field List - Activate, move, resize & layout Field List & Areas Drill down to audit Sort Field List from A to Z TIPS Double click on any labels to show more Fields Defer Layout Update OPTIONS > DATA Pivot Cache explained Refresh Refresh All

1.15 1.2 1.21 1.22 1.23 1.24 1.25 1.26 1.27 1.3 1.31 1.32 1.33

04

MINUTES

LEVEL

2:17 2:14 1:17 2:36

Beginner - Intermediate Beginner - Intermediate Beginner - Intermediate Beginner - Intermediate

2:08

Beginner - Intermediate

3:12 1:30 5:33 1:33 0:35

Beginner - Intermediate Beginner - Intermediate Beginner - Intermediate Beginner - Intermediate Beginner - Intermediate

0:43 0:45

Beginner - Intermediate Beginner - Intermediate

0:54 1:11 2:11

Beginner - Intermediate Beginner - Intermediate Beginner - Intermediate

www.myexcelonline.com

1.34 1.35 1.36 1.4 1.41 1.42 1.43 1.5 1.51 1.52 1.53 1.6 1.61 1.62 1.63 1.64 1.65 1.7 1.71 1.72 1.73 1.74 1.8 1.81 1.82 1.83

Refresh External Data Import from Access database Change Data Source OPTIONS > ACTIONS Clear Filters & Clear Pivot Select & format Move pivot table DESIGN > STYLES Pivot Table Styles Customising a Pivot Table Style Use customised style in another workbook DESIGN > LAYOUT Subtotals Grand Totals Report Layout Blank Row TIPS Show the Classic Pivot Table Layout OPTIONS > SHOW Expand & Collapse buttons Move & Remove Fields Show/Hide Field List Show/Hide Field Headers CUSTOMISATION Change Count of to Sum of Number formatting Field name formatting

05

6:00 2:18 3:31

Intermediate - Advanced Intermediate - Advanced Beginner - Intermediate

1:08 3:26 0:50

Beginner - Intermediate Beginner - Intermediate Beginner - Intermediate

3:44 4:14 1:17

Beginner - Intermediate Intermediate - Advanced Beginner - Intermediate

1:00 0:53 2:43 0:33

Beginner - Intermediate Beginner - Intermediate Beginner - Intermediate Beginner - Intermediate

0:58

Beginner - Intermediate

4:48 2:37 0:17 0:17

Beginner - Intermediate Beginner - Intermediate Beginner - Intermediate Beginner - Intermediate

1:08 1:04 2:15

Beginner - Intermediate Beginner - Intermediate Beginner - Intermediate

www.myexcelonline.com

1.84 1.85 1.9 1.91 1.92 1.93 1.94 1.95 1.96 1.97 1.98 2 2.1 2.2 2.3 2.4 2.5 2.6 2.7 2.8 2.9 2.10 2.11 2.12 2.13

TIPS Predetermined number formatting Change Sum views in label areas PIVOT TABLE OPTIONS > LAYOUT & FORMAT Indent rows in compact layout Change the layout of a report filter Format error values Format empty cells Keep column widths upon refresh TIPS Automatic Refresh a pivot table Printing a pivot table on two pages Show report filter on multiple pages VALUE FIELD SETTINGS > SUMMARIZE VALUES BY Create multiple subtotals Count Average Maximum Minimum Product Count Numbers Std Dev Varp TIPS Show various Grand Totals Shortcuts to Field & Value Field Settings See all pivot Items Show a unique count

06

1:21 0:42

Beginner - Intermediate Beginner - Intermediate

0:05 1:18 0:54 1:18 0:47

Beginner - Intermediate Beginner - Intermediate Beginner - Intermediate Beginner - Intermediate Beginner - Intermediate

0:46 1:11 2:13

Beginner - Intermediate Beginner - Intermediate Beginner - Intermediate

1:35 2:27 2:04 2:00 1:22 3:33 2:04 7:05 3:57

Beginner - Intermediate Beginner - Intermediate Beginner - Intermediate Beginner - Intermediate Beginner - Intermediate Beginner - Intermediate Beginner - Intermediate Intermediate - Advanced Intermediate - Advanced

2:26 2:01 6:00 2:26

Beginner - Intermediate Beginner - Intermediate Intermediate - Advanced Beginner - Intermediate

www.myexcelonline.com

3 3.1 3.2 3.3 3.4 3.5 3.6 3.7 3.8 3.9 3.10 3.11 3.12 3.13 3.14

3.16 3.17

VALUE FIELD SETTINGS > SHOW VALUES AS % of Grand Total % of Column Total % of Row Total % Of % of Parent Row Total (NEW FROM EXCEL 2010) % of Parent Column Total (NEW FROM EXCEL 2010) % of Parent Total (NEW FROM EXCEL 2010) Difference From % Difference From Running Total in % Running Total in (NEW FROM EXCEL 2010) Rank Smallest to Largest (NEW FROM EXCEL 2010) Rank Largest to Smallest (NEW FROM EXCEL 2010) Index TIPS Shortcuts to Show Values As BUSINESS CASE ACCOUNTING: % of Revenue Margins FINANCIAL: Actual v Plan Variance Report

4 4.1 4.2 4.3 4.4 4.5 4.6

GROUPING Group by Date Group by Month Group by Quarters & Years Group by Sales ranges Group by Text fields Group by Time

3.15

07

2:11 2:11 1:05 4:01 3:09 2:22 3:20 4:44 4:30 2:12 3:10 2:14 2:15 3:38

Beginner - Intermediate Beginner - Intermediate Beginner - Intermediate Beginner - Intermediate Beginner - Intermediate Beginner - Intermediate Beginner - Intermediate Beginner - Intermediate Beginner - Intermediate Beginner - Intermediate Beginner - Intermediate Beginner - Intermediate Beginner - Intermediate Beginner - Intermediate

1:07

Beginner - Intermediate

2:28 4:33

Beginner - Intermediate Beginner - Intermediate

2:45 1:44 1:45 3:12 2:14 1:45

Beginner - Intermediate Beginner - Intermediate Beginner - Intermediate Beginner - Intermediate Beginner - Intermediate Beginner - Intermediate

www.myexcelonline.com

4.7 4.8 4.9 4.10 4.11 4.12 4.13 4.14 4.15 4.16 4.17 5 5.1 5.2 5.3 5.4 5.5 5.6

TIPS Shortcuts to Grouping Grouping by Half Years Group by a Date that starts on a Monday Grouping by a custom date Group by fiscal years & quarters Errors when grouping by date Group two pivot tables independently Fixing the problem of counting grouped sales Display dates that have no data BUSINESS CASE ACCOUNTING: Quarterly Comparative Report FINANCIAL: Min & Max Bank Balance

5.7 5.8 5.9

SORT Sorting by Largest or Smallest Sort an Item Row (Left to Right) Sort manually (drag, write, right click) Sort using a Custom List Override a Custom List sort Sort row from A-Z and sales from Z-A TIPS Sort new items added to your data source Clear a sort Sort Largest to Smallest Grand Totals

6 6.1

FILTER Filter by Dates

08

1:35 1:48 1:58 1:41 6:44 2:50 3:46 0:35 0:49

Beginner - Intermediate Beginner - Intermediate Beginner - Intermediate Beginner - Intermediate Intermediate - Advanced Beginner - Intermediate Beginner - Intermediate Beginner - Intermediate Beginner - Intermediate

6:35 4:32

Intermediate - Advanced Beginner - Intermediate

1:57 1:21 1:55 3:22 1:19 1:14

Beginner - Intermediate Beginner - Intermediate Beginner - Intermediate Beginner - Intermediate Beginner - Intermediate Beginner - Intermediate

1:12 0:18 0:16

Beginner - Intermediate Beginner - Intermediate Beginner - Intermediate

6:45

Beginner - Intermediate

www.myexcelonline.com

6.2 6.3 6.4 6.5 6.6 6.7 6.8

6.18 6.19

Filter by Labels - Text Filter by Labels - Numerical Text Filter by Values Filter by Values - Top or Bottom 10 Items Filter by Values - Top or Bottom % Filter by Values - Top or Bottom Sum Filter by Report Filter TIPS Shortcuts to filters Keep or hide selected items Filter by Text wildcards * and ? Filter by multiple fields Apply multiple filters Filter by multiple values Include new items in manual filter Clear filters with one click Add a filter for the column items BUSINESS CASE ACCOUNTING: Top 5 Expenses report FINANCIAL: Top 25% of Channel Partners

7 7.1 7.2 7.3 7.4 7.5 7.6 7.7

SLICERS (NEW FROM EXCEL 2010) Insert a Slicer Slicer Styles Creating a custom style Copy a custom style into a new workbook Slicer Settings Slicer Size & Properties Slicer Connections for multiple pivot tables

6.9 6.10 6.11 6.12 6.13 6.14 6.15 6.16 6.17

09

2:35 2:45 4:37 2:04 1:41 1:39 4:14

Beginner - Intermediate Beginner - Intermediate Beginner - Intermediate Beginner - Intermediate Beginner - Intermediate Beginner - Intermediate Beginner - Intermediate

1:33 0:48 3:11 0:57 1:43 1:05 1:50 1:32 0:30

Beginner - Intermediate Beginner - Intermediate Beginner - Intermediate Beginner - Intermediate Beginner - Intermediate Beginner - Intermediate Beginner - Intermediate Beginner - Intermediate Beginner - Intermediate

2:20 2:08

Beginner - Intermediate Beginner - Intermediate

4:12 2:21 6:19 1:32 4:46 3:03 3:18

Beginner - Intermediate Beginner - Intermediate Intermediate - Advanced Beginner - Intermediate Beginner - Intermediate Beginner - Intermediate Beginner - Intermediate

www.myexcelonline.com

7.8 7.9 7.10 7.11 7.12 7.13 8 8.1 8.2 8.3 8.4 8.5 8.6 8.7 8.8 8.9 8.10 8.11 8.12 8.13 8.14

TIPS Different ways to filter a Slicer Use one slicer for two pivot tables Lock the workbook but not the slicer Interactive employee photos with Slicers! FUN! BUSINESS CASE ACCOUNTING: Select a Monthly P&L report with a Slicer FINANCIAL: Base, Best & Worst case Forecast CALCULATED FIELDS & ITEMS Creating a Calculated Field Use an existing Calculated Field in a new calculation Editing a Calculated Field Excel formulas & Calculated Fields Creating a Calculated Item Use an existing Calculated Item in a new calculation Editing a Calculated Item Excel formulas & Calculated Items Calculated Item on Column Labels Shortcomings of Calculated Items TIPS Solve Order for Calculated Items List Calculated Field & Item formulas Remove a Calculated Field temporarily Order of operations

10

1:13 1:16 1:23 10:16

Beginner - Intermediate Beginner - Intermediate Beginner - Intermediate Intermediate - Advanced

4:10 5:11

Beginner - Intermediate Intermediate - Advanced

4:39 2:21 1:59 3:03 4:18 1:37 2:20 1:48 2:21 1:48

Beginner - Intermediate Beginner - Intermediate Beginner - Intermediate Beginner - Intermediate Beginner - Intermediate Beginner - Intermediate Beginner - Intermediate Beginner - Intermediate Beginner - Intermediate Beginner - Intermediate

4:23 1:04 0:51 1:48

Intermediate - Advanced Beginner - Intermediate Beginner - Intermediate Beginner - Intermediate

www.myexcelonline.com

8.15 8.16 9 9.1 9.2 9.3 9.4 9.5 9.6 9.7 9.8 9.9 9.10 9.11 9.12 9.13 9.14 9.15 9.16 9.17 9.18 9.19 9.20 9.21

BUSINESS CASE ACCOUNTING: Creating a P&L Pivot Table Report FINANCIAL: Actuals v Plan with Calculated Fields PIVOT CHARTS Insert a Pivot Chart Insert a Slicer with a Pivot Chart Pivot Chart Designs Pivot Chart Layouts Pivot Chart Formats Limitations of Pivot Charts & workarounds Saving a pivot chart template TIPS Shortcuts to formatting a Pivot Chart Link chart title to a pivot cell Copying a second chart Put a chart on a separate page with F11 Insert Pivot Chart straight from the data source Paste Pivot Chart to your email as a picture Paste Pivot Chart to PowerPoint & make live updates Printing a Pivot Chart Include a Sparkline with your pivot table (NEW FROM EXCEL 2010) Charts Do´s & Don'ts Change Chart Type with Slicers! FUN!!! Workaround to creating an interactive Scatter graph BUSINESS CASE ACCOUNTING: P&L Pivot Table report with Graphs FINANCIAL: Pivot Table Slicer & Chart Dashboard

11

8:15 6:26

Intermediate - Advanced Beginner - Intermediate

3:18 1:50 3:49 4:59 5:50 1:54 1:26

Beginner - Intermediate Beginner - Intermediate Beginner - Intermediate Beginner - Intermediate Beginner - Intermediate Beginner - Intermediate Beginner - Intermediate

1:15 0:55 0:41 0:27 0:49 1:02 1:26 1:18 1:14 2:51 8:28 2:17

Beginner - Intermediate Beginner - Intermediate Beginner - Intermediate Beginner - Intermediate Beginner - Intermediate Beginner - Intermediate Beginner - Intermediate Beginner - Intermediate Beginner - Intermediate Beginner - Intermediate Intermediate - Advanced Beginner - Intermediate

8:18 15:45

Intermediate - Advanced Intermediate - Advanced

www.myexcelonline.com

10 10.1 10.2 10.3 10.4 10.5 10.6

10.16 10.17

CONDITIONAL FORMATTING PIVOT TABLES 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, Colour Scales & Icon Sets (NEW FROM EXCEL 2010) TIPS Format only cells that contain - For Bonuses Format only Top or Bottom ranked values - Top 3 sales per year Format values that are above or below the average - For Promotions 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 BUSINESS CASE ACCOUNTING: Accounts Receivable Ageing Report Matrix FINANCIAL: Conditionally Format your sales results

11 11.1 11.2 11.3 11.4 11.5

GETPIVOTDATA formula Intro to GETPIVOTDATA Create a custom report with GETPIVOTDATA Reference Dates with GETPIVOTDATA Data validation with GETPIVOTDATA Shortfalls of GETPIVOTDATA

10.7 10.8 10.9 10.10 10.11 10.12 10.13 10.14 10.15

12

2:33 1:18 0:44 1:13 3:23 5:49

Beginner - Intermediate Beginner - Intermediate Beginner - Intermediate Beginner - Intermediate Beginner - Intermediate Beginner - Intermediate

1:39 1:30 1:36 2:33 1:30 1:30 2:52 4:42 1:04

Beginner - Intermediate Beginner - Intermediate Beginner - Intermediate Beginner - Intermediate Beginner - Intermediate Beginner - Intermediate Intermediate - Advanced Intermediate - Advanced Beginner - Intermediate

6:08 3:24

Intermediate - Advanced Beginner - Intermediate

4:59 5:15 3:12 2:45 1:38

Beginner - Intermediate Intermediate - Advanced Beginner - Intermediate Beginner - Intermediate Beginner - Intermediate

www.myexcelonline.com

11.7 11.8

TIPS Grand Totals to the left of the pivot table BUSINESS CASE ACCOUNTING: Live forecasting with GETPIVOTDATA FINANCIAL: Channel Analysis with GETPIVOTDATA

12 12.1 12.2 12.3 12.4 12.5 12.6

MACROS 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

2:31 4:18 4:26 4:33 3:26 1:10

Beginner - Intermediate Intermediate - Advanced Intermediate - Advanced Intermediate - Advanced Beginner - Intermediate Beginner - Intermediate

13 13.1 13.2 13.3 13.4 13.5 13.6

DATA MANAGEMENT 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

0:50 1:36 0:47 2:02 1:05 2:39

Beginner - Intermediate Beginner - Intermediate Beginner - Intermediate Beginner - Intermediate Beginner - Intermediate Beginner - Intermediate

11.6

13

3:11

Intermediate - Advanced

7:14 5:20

Intermediate - Advanced Intermediate - Advanced

www.myexcelonline.com

14 14.1 14.2 14.3 14.4 14.5 14.6 14.7 14.8 14.9

BONUS VIDEOS 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 vendor payments Pivot Power Add-In to make you more efficient

4:11 3:14 2:06 2:55 1:53 12:11 4:25 2:00 4:00

14

Beginner - Intermediate Beginner - Intermediate Beginner - Intermediate Beginner - Intermediate Beginner - Intermediate Intermediate - Advanced Intermediate - Advanced Beginner - Intermediate Beginner - Intermediate

www.myexcelonline.com