· 1111 i i i i i i i i - free microsoft excel tutorials · what excel version is the training...
TRANSCRIPT
2012 Em2cu,
John Michaloudis
Sum of SALES Column Labels a
Row Labels Iii 2012 2013 2014 Grand Total
i'
Top $ Ch1n'"' P1.n:n9n M(t,,ofs.i.i.,p1rGroup
IUta(Plll:Nuj i1i.tlJUi I'/ /' "
·�1111 i I i I i I i I � I
- -·- - - -1-1-1-1- - -·
728,139 647,088 624,591 520,194 670,682 .. 760,947
590,843 .. , 743,145 529,990 I 443,535 668,677 •• 1 695,978 639,140 •• 1 687,234
www.myexcelonline.com
www.myexcelonline.com
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
2
3
4
5
6
7
8
9
10
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;
Access to all the Excel workbooks that are shown in the training videos so you can practice the new tricks learned;
HD quality short videos viewable from your PC, Laptop, Tablet device or Mobile/Cell phone, anytime & anywhere, 24x7!;
Bonus Pivot Table Cheat Sheet to stick on your desk that highlights the most important Pivot Table commands visually;
Bonus Excel workbook that shows all the Course content. You can tick off and rate each tutorial once viewed and track your progress;
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;
20% discount on the time saving Pivot Power Add-In from Contextures;
Dedicated Personal Email 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
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
Can I view an example of the videos & workbooks? ABSOLUTELY!
Under the Course Curriculum & Sample Videos section of the course page, you can click on the SHOW CONTENT for each chapter. Within each chapter there is a sample video and workbook that you can view (click on image to view):
What Excel version is the training compatible with? This course was recorded using Excel 2010 but all the concepts taught are compatible with:> Excel for Windows: 2003*, 2007*, 2010, 2013, 2016> Excel for Mac: 2011*, 2016
* Slicers and some Pivot Table calculations are not available in these versions
What payment methods are accepted?We accept all major credit cards like Visa, MasterCard, American Express etc. as well as PayPal!
If you want to make an electronic bank transfer then get in contact with us at [email protected]
www.myexcelonline.com03
www.myexcelonline.com
More questions?Please get in contact with us at [email protected] and I will be glad to answer any questions regarding this course that you may have.
Course Outline
04
11.11.11
1.12
1.13
1.14
1.15
1.21.21
1.22
1.23
1.24
1.25
1.26
1.27
1.31.31
1.32
1.33
CUSTOMISE YOUR PIVOT TABLEARRANGING YOUR DATA SETTabular Format
No gaps
Formatting
Tables
TIPSClean Your Data Set
CREATING A PIVOT TABLEInserting a Pivot Table
Field List - Activate, move, resize & layout
Field List & Areas
Drill down to audit
Sort Field List from A to Z
TIPSDouble click on any labels to show more Fields
Defer Layout Update
OPTIONS > DATAPivot Cache explained
Refresh
Refresh All
2:17
2:14
1:17
2:36
2:08
3:12
1:30
5:33
1:33
0:35
0:43
0:45
0:54
1:11
2:11
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
Beginner - Intermediate
SEQUENCE CONTENTS MINUTES LEVEL
CLICK TO
ORDER
www.myexcelonline.com
1.34
1.35
1.36
1.41.41
1.42
1.43
1.51.51
1.52
1.53
1.61.61
1.62
1.63
1.64
1.65
1.71.71
1.72
1.73
1.74
1.81.81
1.82
1.83
Refresh External Data
Import from Access database
Change Data Source
OPTIONS > ACTIONSClear Filters & Clear Pivot
Select & format
Move pivot table
DESIGN > STYLESPivot Table Styles
Customising a Pivot Table Style
Use customised style in another workbook
DESIGN > LAYOUTSubtotals
Grand Totals
Report Layout
Blank Row
TIPSShow the Classic Pivot Table Layout
OPTIONS > SHOWExpand & Collapse buttons
Move & Remove Fields
Show/Hide Field List
Show/Hide Field Headers
CUSTOMISATIONChange Count of to Sum of
Number formatting
Field name formatting
6:00
2:18
3:31
1:08
3:26
0:50
3:44
4:14
1:17
1:00
0:53
2:43
0:33
0:58
4:48
2:37
0:17
0:17
1:08
1:04
2:15
Intermediate - Advanced
Intermediate - Advanced
Beginner - Intermediate
Beginner - Intermediate
Beginner - Intermediate
Beginner - Intermediate
Beginner - Intermediate
Intermediate - Advanced
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
05
www.myexcelonline.com
1.84
1.85
1.91.91
1.92
1.93
1.94
1.95
1.96
1.97
1.98
22.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
TIPSPredetermined number formatting
Change Sum views in label areas
PIVOT TABLE OPTIONS > LAYOUT & FORMATIndent rows in compact layout
Change the layout of a report filter
Format error values
Format empty cells
Keep column widths upon refresh
TIPSAutomatic Refresh a pivot table
Printing a pivot table on two pages
Show report filter on multiple pages
VALUE FIELD SETTINGS > SUMMARIZE VALUES BYCreate multiple subtotals
Count
Average
Maximum
Minimum
Product
Count Numbers
Std Dev
Varp
TIPSShow various Grand Totals
Shortcuts to Field & Value Field Settings
See all pivot Items
Show a unique count
1:21
0:42
0:05
1:18
0:54
1:18
0:47
0:46
1:11
2:13
1:35
2:27
2:04
2:00
1:22
3:33
2:04
7:05
3:57
2:26
2:01
6:00
2:26
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
Beginner - Intermediate
Beginner - Intermediate
Beginner - Intermediate
Intermediate - Advanced
Intermediate - Advanced
Beginner - Intermediate
Beginner - Intermediate
Intermediate - Advanced
Beginner - Intermediate
06
www.myexcelonline.com
33.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.15
3.16
3.17
44.1
4.2
4.3
4.4
4.5
4.6
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
TIPSShortcuts to Show Values As
BUSINESS CASEACCOUNTING: % of Revenue Margins
FINANCIAL: Actual v Plan Variance Report
GROUPINGGroup by Date
Group by Month
Group by Quarters & Years
Group by Sales ranges
Group by Text fields
Group by Time
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
1:07
2:28
4:33
2:45
1:44
1:45
3:12
2:14
1:45
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
Beginner - Intermediate
Beginner - Intermediate
Beginner - Intermediate
Beginner - Intermediate
Beginner - Intermediate
Beginner - Intermediate
Beginner - Intermediate
Beginner - Intermediate
Beginner - Intermediate
07
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
55.1
5.2
5.3
5.4
5.5
5.6
5.7
5.8
5.9
66.1
TIPSShortcuts 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 CASEACCOUNTING: Quarterly Comparative Report
FINANCIAL: Min & Max Bank Balance
SORTSorting 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
TIPSSort new items added to your data source
Clear a sort
Sort Largest to Smallest Grand Totals
FILTERFilter by Dates
1:35
1:48
1:58
1:41
6:44
2:50
3:46
0:35
0:49
6:35
4:32
1:57
1:21
1:55
3:22
1:19
1:14
1:12
0:18
0:16
6:45
Beginner - Intermediate
Beginner - Intermediate
Beginner - Intermediate
Beginner - Intermediate
Intermediate - Advanced
Beginner - Intermediate
Beginner - Intermediate
Beginner - Intermediate
Beginner - Intermediate
Intermediate - Advanced
Beginner - Intermediate
Beginner - Intermediate
Beginner - Intermediate
Beginner - Intermediate
Beginner - Intermediate
Beginner - Intermediate
Beginner - Intermediate
Beginner - Intermediate
Beginner - Intermediate
Beginner - Intermediate
Beginner - Intermediate
08
www.myexcelonline.com
6.2
6.3
6.4
6.5
6.6
6.7
6.8
6.9
6.10
6.11
6.12
6.13
6.14
6.15
6.16
6.17
6.18
6.19
77.1
7.2
7.3
7.4
7.5
7.6
7.7
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
TIPSShortcuts 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 CASEACCOUNTING: Top 5 Expenses report
FINANCIAL: Top 25% of Channel Partners
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
2:35
2:45
4:37
2:04
1:41
1:39
4:14
1:33
0:48
3:11
0:57
1:43
1:05
1:50
1:32
0:30
2:20
2:08
4:12
2:21
6:19
1:32
4:46
3:03
3:18
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
Beginner - Intermediate
Beginner - Intermediate
Beginner - Intermediate
Beginner - Intermediate
Beginner - Intermediate
Beginner - Intermediate
Intermediate - Advanced
Beginner - Intermediate
Beginner - Intermediate
Beginner - Intermediate
Beginner - Intermediate
09
www.myexcelonline.com
7.8
7.9
7.10
7.11
7.12
7.13
88.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
TIPSDifferent 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 CASEACCOUNTING: Select a Monthly P&L report with a Slicer
FINANCIAL: Base, Best & Worst case Forecast
CALCULATED FIELDS & ITEMSCreating 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
TIPSSolve Order for Calculated Items
List Calculated Field & Item formulas
Remove a Calculated Field temporarily
Order of operations
1:13
1:16
1:23
10:16
4:10
5:11
4:39
2:21
1:59
3:03
4:18
1:37
2:20
1:48
2:21
1:48
4:23
1:04
0:51
1:48
Beginner - Intermediate
Beginner - Intermediate
Beginner - Intermediate
Intermediate - Advanced
Beginner - Intermediate
Intermediate - Advanced
Beginner - Intermediate
Beginner - Intermediate
Beginner - Intermediate
Beginner - Intermediate
Beginner - Intermediate
Beginner - Intermediate
Beginner - Intermediate
Beginner - Intermediate
Beginner - Intermediate
Beginner - Intermediate
Intermediate - Advanced
Beginner - Intermediate
Beginner - Intermediate
Beginner - Intermediate
10
www.myexcelonline.com
8.15
8.16
99.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 CASEACCOUNTING: Creating a P&L Pivot Table Report
FINANCIAL: Actuals v Plan with Calculated Fields
PIVOT CHARTSInsert 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
TIPSShortcuts 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 CASEACCOUNTING: P&L Pivot Table report with Graphs
FINANCIAL: Pivot Table Slicer & Chart Dashboard
8:15
6:26
3:18
1:50
3:49
4:59
5:50
1:54
1:26
1:15
0:55
0:41
0:27
0:49
1:02
1:26
1:18
1:14
2:51
8:28
2:17
8:18
15:45
Intermediate - Advanced
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
Beginner - Intermediate
Beginner - Intermediate
Beginner - Intermediate
Beginner - Intermediate
Intermediate - Advanced
Beginner - Intermediate
Intermediate - Advanced
Intermediate - Advanced
11
www.myexcelonline.com
1010.1
10.2
10.3
10.4
10.5
10.6
10.7
10.8
10.9
10.10
10.11
10.12
10.13
10.14
10.15
10.16
10.17
1111.1
11.2
11.3
11.4
11.5
CONDITIONAL FORMATTING PIVOT TABLESIntro 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)
TIPSFormat 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 CASEACCOUNTING: Accounts Receivable Ageing Report Matrix
FINANCIAL: Conditionally Format your sales results
GETPIVOTDATA formulaIntro to GETPIVOTDATA
Create a custom report with GETPIVOTDATA
Reference Dates with GETPIVOTDATA
Data validation with GETPIVOTDATA
Shortfalls of GETPIVOTDATA
2:33
1:18
0:44
1:13
3:23
5:49
1:39
1:30
1:36
2:33
1:30
1:30
2:52
4:42
1:04
6:08
3:24
4:59
5:15
3:12
2:45
1: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
Intermediate - Advanced
Intermediate - Advanced
Beginner - Intermediate
Intermediate - Advanced
Beginner - Intermediate
Beginner - Intermediate
Intermediate - Advanced
Beginner - Intermediate
Beginner - Intermediate
Beginner - Intermediate
12
www.myexcelonline.com
11.6
11.7
11.8
1212.1
12.2
12.3
12.4
12.5
12.6
1313.1
13.2
13.3
13.4
13.5
13.6
TIPSGrand Totals to the left of the pivot table
BUSINESS CASE
ACCOUNTING: Live forecasting with GETPIVOTDATA
FINANCIAL: Channel Analysis with GETPIVOTDATA
MACROSAdding 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
DATA MANAGEMENTReducing 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
3:11
7:14
5:20
2:31
4:18
4:26
4:33
3:26
1:10
0:50
1:36
0:47
2:02
1:05
2:39
Intermediate - Advanced
Intermediate - Advanced
Intermediate - Advanced
Beginner - Intermediate
Intermediate - Advanced
Intermediate - Advanced
Intermediate - Advanced
Beginner - Intermediate
Beginner - Intermediate
Beginner - Intermediate
Beginner - Intermediate
Beginner - Intermediate
Beginner - Intermediate
Beginner - Intermediate
Beginner - Intermediate
13
www.myexcelonline.com
1414.1
14.2
14.3
14.4
14.5
14.6
14.7
14.8
14.9
BONUS VIDEOSSales 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
Beginner - Intermediate
Beginner - Intermediate
Beginner - Intermediate
Beginner - Intermediate
Beginner - Intermediate
Intermediate - Advanced
Intermediate - Advanced
Beginner - Intermediate
Beginner - Intermediate
14