2015
MyExcelOnline / Udemy
John Michaloudis
10:08
English
This is the Ultimate Excel Pivot Table course which has 217 short and precise tutorials. It also expands in to Charts, Conditional Formatting, Macros and Excel Shortcut Tips!
No matter if you are a Beginner or an Advanced user of Excel, you are sure to benefit from this course which goes through every single tool that is available in an Excel Pivot Table. The course is designed for Excel 2007, 2010 or 2013.
There are 14 different chapters so you can work on your weaknesses and enhance your strengths. Each chapter was designed to improve your Excel skills with extra time saving Tips and real life business Case Studies.
In no time you will be able to analyze lots of data and tell a story in a quick and interactive way, learn how to build stunning Dashboards and get noticed by top management & prospective employers. The course is just over 9 hours long so you can become an awesome analyst and advanced Excel user within 1 week!
1 INTRODUCTION
2 COURSE TRACKER WORKBOOK - Text
3 BONUS: PIVOT TABLE CHEAT SHEET - 1 page
4 BONUS: TOP 20 EXCEL SHORTCUTS - Text
5 BONUS: EXCEL RESOURCE BLOG - page 1
Section 1: CUSTOMIZING YOUR PIVOT TABLE
6 WORKBOOKS - Text
7 ARRANGING YOUR DATA SET ~ Tabular Format
8 ARRANGING YOUR DATA SET ~ No Gaps
9 ARRANGING YOUR DATA SET ~ Formatting
10 ARRANGING YOUR DATA SET ~ Tables
11 ARRANGING YOUR DATA SET ~ TIP: Clean Your Data Set
12 CREATING A PIVOT TABLE ~ Inserting a Pivot Table
13 CREATING A PIVOT TABLE ~ Field List - Activate, move, resize & layout
14 CREATING A PIVOT TABLE ~ Field List & Areas
15 CREATING A PIVOT TABLE ~ Drill down to audit
16 CREATING A PIVOT TABLE ~ Sort Field List from A to Z
17 CREATING A PIVOT TABLE ~ TIP: Double click on any labels to show more Fields
18 CREATING A PIVOT TABLE ~ TIP: Defer Layout Update
19 OPTIONS > DATA ~ Pivot Cache explained
20 OPTIONS > DATA ~ Refresh
21 OPTIONS > DATA ~ Refresh All
22 OPTIONS > DATA ~ Refresh External Data
23 OPTIONS > DATA ~ Import from Access database
24 OPTIONS > DATA ~ Change Data Source
25 OPTIONS > ACTIONS ~ Clear Filters & Clear Pivot
26 OPTIONS > ACTIONS ~ Select & format
27 OPTIONS > ACTIONS ~ Move a Pivot Table
28 DESIGN > STYLES ~ Pivot Table Styles
29 DESIGN > STYLES ~ Customising a Pivot Table Style
30 DESIGN > STYLES ~ Use a customised style in another workbook
31 DESIGN > LAYOUT ~ Subtotals
32 DESIGN > LAYOUT ~ Grand Totals
33 DESIGN > LAYOUT ~ Report Layouts
34 DESIGN > LAYOUT ~ Blank Rows
35 DESIGN > LAYOUT ~ TIP: Show the Classic Pivot Table Layout
36 OPTIONS > SHOW ~ Expand & Collapse buttons
37 OPTIONS > SHOW ~ Move & Remove Fields and Items
38 OPTIONS > SHOW ~ Show/Hide Field List
39 OPTIONS > SHOW ~ Show/Hide Field Headers
40 CUSTOMISATION ~ Change "Count of" to "Sum of"
41 CUSTOMISATION ~ Number formatting
42 CUSTOMISATION ~ Field name formatting
43 CUSTOMISATION ~ TIP: Predetermined number formatting
44 CUSTOMISATION ~ TIP: Change views in Sum Label areas
45 LAYOUT & FORMAT ~ Indent rows in compact layout
46 LAYOUT & FORMAT ~ Change the layout of a report filter
47 LAYOUT & FORMAT ~ values Format error
48 LAYOUT & FORMAT ~ Format empty cells
49 LAYOUT & FORMAT ~ Keep column widths upon refresh
50 LAYOUT & FORMAT ~ TIP: Automatic Refresh a Pivot Table
51 LAYOUT & FORMAT ~ TIP: Printing a pivot table on two pages
52 LAYOUT & FORMAT ~ TIP:Show report filter on multiple pages
53 Recognize which button is used to update a Pivot Table - 1 question
Section 2: VALUE FIELD SETTINGS > SUMMARIZE VALUES BY
54 WORKBOOKS - Text
55 Create multiple subtotals
56 Count
57 Average
58 Maximum
59 Minimum
60 Product
61 Count Numbers
62 Std Dev
63 Varp
64 TIP: Show various Grand Totals
65 TIP: Shortcuts to Field & Value Field Settings
66 TIP: See all pivot Items
67 TIP: Show a unique count
68 Recall the way to change a "Count Of" to a "Sum Of" - 1 question
Section 3: VALUE FIELD SETTINGS > SHOW VALUES AS
69 WORKBOOKS - Text
70 % of Grand Total
71 % of Column Total
72 % of Row Total
73 % Of
74% of Parent Row Total (NEW IN EXCEL 2010)
75 % of Parent Column Total (NEW IN EXCEL 2010)
76 % of Parent Total (NEW IN EXCEL 2010)
77 Difference From
78 % Difference From
79 Running Total in
80 % Running Total in (NEW IN EXCEL 2010)
81 Rank Smallest to Largest (NEW IN EXCEL 2010)
82 Rank Largest to Smallest (NEW IN EXCEL 2010)
83 Index
84 TIP: Shortcuts to Show Values As
85 CASE STUDY ~ ACCOUNTING: % of Revenue Margins
86 CASE STUDY ~ FINANCIAL: Actual v Plan Variance Report
87 Identify the steps to showing the values as a Year to Date accumulation - 1 question
Section 4: GROUPING
88 WORKBOOKS - Text
89 Group by Date
90 Group by Months
91 Group by Quarters & Years
92 Group by Sales ranges
93 Group by Text fields
94 Group by Time
95 TIP: Shortcuts to Grouping
96 TIP: Grouping by Half Years
97 TIP: Group by a Date that starts on a Monday
98 TIP: Grouping by a custom date
99 TIP: Group by fiscal years & quarters
100 TIP: Errors when grouping by dates
101 TIP: Group two pivot tables independently
102 TIP: Fixing the problem of counting grouped sales
103 TIP: Display dates that have no data
104 CASE STUDY ~ ACCOUNTING: Quarterly Comparative Report
105 CASE STUDY ~ FINANCIAL: Min & Max Bank Balance
106 State which command is used to group a PivotTable report by weeks - 1 question
Section 5: SORTING
107 WORKBOOKS - Text
108 Sorting by Largest or Smallest
109 Sort an Item Row (Left to Right)
110 Sort manually (drag, write, right click)
111 Sort using a Custom List
112 Override a Custom sort List
113 Sort row from A-Z and sales from Z-A
114 TIP: Sort new items added to your data source
115 TIP: Clear a sort
116 TIP: Sort Largest to Smallest Grand Totals
117 Identify the steps to creating a Custom List - 1 question
Section 6: FILTERING
118 WORKBOOKS - Text
119 Filter by Dates
120 Filter by Labels - Text
121 Filter by Labels - Numerical Text
122 Filter by Values
123 Filter by Values - Top or Bottom 10 Items
124 Filter by Values - Top or Bottom %
125 Filter by Values - Top or Bottom Sum
126 Filter by Report Filter
127 TIP: Shortcuts to filters
128 TIP: Keep or hide selected items
129 TIP: Filter by Text wildcards * and ?
130 TIP: Filter by multiple fields
131 TIP: Apply multiple filters
132 TIP: Filter by multiple values
133 TIP: Include new items in manual filter
134 TIP: Clear filters with one click
135 TIP: Add a filter for the column items
136 CASE STUDY ~ ACCOUNTING: Top 5 Expenses report
137 CASE STUDY ~ FINANCIAL: Top 25% of Channel Partners
138 Recall the command to search for a wildcard entry - 1 question
Section 7: SLICERS (NEW IN EXCEL 2010)
139 WORKBOOKS - Text
140 Insert a Slicer
141 Slicer Styles
142 Creating a custom style
143 Copy a custom style into a new workbook
144 Slicer Settings
145 Slicer Size & Properties
146 Slicer Connections for multiple pivot tables
147 TIP: Different ways to filter a Slicer
148 TIP: Use one slicer for two pivot tables
149 TIP: Lock the workbook but not the slicer
150 TIP: Interactive employee photos with Slicers! FUN!
151 CASE STUDY ~ ACCOUNTING: Select a Monthly P&L report with a Slicer
152 CASE STUDY ~ FINANCIAL: Base, Best & Worst case Forecast
153 Identify the steps for customizing a Slicer - 1 question
Section 8: CALCULATED FIELDS & ITEMS
154 WORKBOOKS - Text
155 Creating a Calculated Field
156 " Use an existing Calculated Field in a new calculation
157 Editing a Calculated Field
158 Excel formulas & Calculated Fields
159 Creating a Calculated Item
160 " Use an existing Calculated Item in a new calculation
161 Editing a Calculated Item
162 Excel formulas & Calculated Items
163 Calculated Item on Column Labels
164 Shortcomings of Calculated Items
165 TIP: Solve Order for Calculated Items
166 TIP: List Calculated Field & Item formulas
167 TIP: Remove a Calculated Field temporarily
168 TIP: Order of operations
169 CASE STUDY ~ ACCOUNTING: Creating a P&L Pivot Table Report
170 CASE STUDY ~ FINANCIAL: Actuals v Plan with Calculated Fields
171 State the steps to creating a Calculated Item - 1 question
Step 9: PIVOT CHARTS
172 WORKBOOKS - Text
173 Insert a Pivot Chart
174 Insert a Slicer with a Pivot Chart
175 Pivot Chart Designs
176 Pivot Chart Layouts
177 Pivot Chart Formats
178 Limitations of Pivot Charts & workarounds
179 Saving a Pivot Chart template
180 TIP: Shortcuts to formatting a Pivot Chart
181 TIP: Link chart title to a pivot cell
182 TIP: Copying a second chart
183 TIP: Put a chart on a separate page with F11
184 TIP: Insert a Pivot Chart straight from the data source
185 TIP: Paste a Pivot Chart to your email as a picture
186 TIP: Paste a Pivot Chart to PowerPoint & make live updates
187 TIP: Printing a Pivot Chart
188 TIP: Include a Sparkline with your pivot table
189 TIP: Charts Dos & Don'ts
190 TIP: Change Chart Type with Slicers! FUN!!!
191 TIP: Workaround to creating an interactive Scatter graph
192 CASE STUDY ~ ACCOUNTING: P&L Pivot Table report with Graphs
193 CASE STUDY ~ FINANCIAL: Pivot Table Slicer & Dashboard Chart
194 State of the button to format a Pivot Chart - 1 question
Section 10: CONDITIONAL FORMATTING PIVOT TABLES
195 WORKBOOKS - Text
196 Intro to Conditional Formatting
197 Highlight Cell Rules based on values
198 Highlight Cell Rules based on text labels
199 Highlight Cell Rules based on date labels
200 Top & Bottom Rules
201 Data Bars, Color Scales & Icon Sets (NEW IN EXCEL 2010)
202 TIP: Format only cells that contain - Bonuses For
203 TIP: Format only Top or Bottom ranked values - Top 3 sales per year
204 TIP: Format values that are above or below the average - For Promotions
205 TIP: Use a formula to determine which cells to format
206 Use selected cells to format multiple fields
207 All cells showing values to format multiple fields
208 Control with Conditional Formatting Slicers
209 Show text in the Pivot Table Values area
210 Cond Format blank cells or cells
211 CASE STUDY ~ ACCOUNTING: Accounts Receivable Ageing Report Matrix
212 CASE STUDY ~ FINANCIAL: Conditionally Format your sales results
213 Recognize the option that conditionally formats a all values except the Subtotals - 1 question
Section 11: GETPIVOTDATA Formula
214 WORKBOOKS - Text
215 Intro to GETPIVOTDATA
216 Create a custom report with GETPIVOTDATA
217 Reference Dates with GETPIVOTDATA
218 Data validation with GETPIVOTDATA
219 Shortfalls of GETPIVOTDATA
220 TIP: Grand Totals to the left of the pivot table
221 CASE STUDY ~ ACCOUNTING: Live forecasting with GETPIVOTDATA
222 CASE STUDY ~ FINANCIAL: Channel Analysis with GETPIVOTDATA
223 Define when to use the GETPIVOTDATA function - 1 question
Section 12: MACROS & PIVOT TABLES
224 WORKBOOKS - Text
225 Adding the Developer tab & disabling macros
226 Record a simple macro to Refresh a pivot table
227 the Date filter macro
228 Different pivot table macro views
229 Top 10 macro
230 Add macro to quick access toolbar
231 Recall how to assign a macro - question 1
Section 13: DATA MANAGEMENT
232 WORKBOOKS - Text
233 Reducing file memory by copying an existing pivot table
234 Reducing file memory by deleting the data source
235 Reducing file memory file by saving as an Excel Binary Workbook
236 Reducing file memory by keeping data source in MS Access
237 Compatibility Issues with Excel 2007 and Excel 2010
238 Sharing a Pivot Table via OneDrive
239 List the ways to reduce the memory size of an Excel file - 1 question
Section 14: BONUS VIDEOS
240 WORKBOOKS - Text
241 Sales Forecasting with Calculated Fields
242 Consolidate with a Pivot Table
243 Frequency distribution with a Pivot Table
244 Break Even Model
245 Slicer Several custom styles for you to use
246 Interactive Balance Sheet Pivot Table
247 Monthly Sales Performance Manager Report
248 Reconciling customer payments
249 THANK YOU!
Download File Size:2.01 GB
|
