Microsoft Excel - Excel Pivot Tables & Data Analysis Bootcamp!

John Michaloudis MyExcelOnline.com, Stand Out From The Crowd

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
230 Lessons (10h 42m)
    • 1. Course Introduction

      2:40
    • 2. 1.11 - Tabular Format

      2:25
    • 3. 1.12 - No Gaps

      2:30
    • 4. 1.13 - Formatting

      1:27
    • 5. 1.14 - Tables

      2:45
    • 6. 1.15 - Clean Your Data Set

      2:18
    • 7. 1.21 - Inserting a Pivot Table

      3:22
    • 8. 1.22 - Field List - Activate, move, resize & layout

      1:40
    • 9. 1.23 - Field List & Areas

      5:25
    • 10. 1.24 - Drill down to audit

      1:42
    • 11. 1.25 - Sort Field List from A to Z

      0:45
    • 12. 1.26 - Double click on any labels to show more Fields

      0:53
    • 13. 1.27 - Defer Layout Update

      0:55
    • 14. 1.31 - Pivot Cache explained

      1:00
    • 15. 1.32 - Refresh

      1:20
    • 16. 1.33 - Refresh All

      2:17
    • 17. 1.34 - Refresh External Data

      4:59
    • 18. 1.35 - Import from Access database

      2:27
    • 19. 1.36 - Change Data Source

      3:10
    • 20. 1.41 - Clear Filters & Clear Pivot

      1:17
    • 21. 1.42 - Select & Format

      3:15
    • 22. 1.43 - Move a Pivot Table

      1:00
    • 23. 1.51 - Pivot Table Styles

      3:43
    • 24. 1.52 - Customising a Pivot Table Style

      4:13
    • 25. 1.53 - Use a customised style in another workbook

      1:27
    • 26. 1.61 - Subtotals

      0:55
    • 27. 1.62 - Grand Totals

      1:01
    • 28. 1.63 - Report Layouts

      2:48
    • 29. 1.64 - Blank Rows

      0:40
    • 30. 1.65 - Show the Classic Pivot Table Layout

      1:08
    • 31. 1.71 - Expand & Collapse buttons

      4:26
    • 32. 1.72 - Move & Remove Fields and Items

      2:39
    • 33. 1.73 - Show/Hide Field List

      0:27
    • 34. 1.74 - Show/Hide Field Headers

      0:27
    • 35. 1.81 - Change Count of to Sum of

      1:10
    • 36. 1.82 - Number formatting

      1:42
    • 37. 1.83 - Field name formatting

      2:16
    • 38. 1.84 - Predetermined number formatting

      1:29
    • 39. 1.85 - Change Sum views in Label areas

      0:52
    • 40. 1.91 - Indent rows in compact layout

      1:00
    • 41. 1.92 - Change the layout of a report filter

      1:25
    • 42. 1.93 - Format error values

      0:49
    • 43. 1.94 - Format empty cells

      1:27
    • 44. 1.95 - Keep column widths upon refresh

      0:53
    • 45. 1.96 - Automatic Refresh a Pivot Table

      0:56
    • 46. 1.97 - Printing a pivot table on two pages

      1:20
    • 47. 1.98 - Show report filter on multiple pages

      2:23
    • 48. 2.1 - Create multiple subtotals

      1:44
    • 49. 2.2 - Count

      2:27
    • 50. 2.3 - Average

      2:43
    • 51. 2.4 - Maximum

      2:03
    • 52. 2.5 - Minimum

      1:26
    • 53. 2.6 - Product

      3:38
    • 54. 2.7 - Count Numbers

      2:26
    • 55. 2.8 - Std Dev

      7:07
    • 56. 2.9 - Varp

      3:36
    • 57. 2.10 - Show various Grand Totals

      2:36
    • 58. 2.11 - Shortcuts to Field & Value Field Settings

      2:19
    • 59. 2.12 - See all pivot Items

      5:59
    • 60. 2.13 - Show a unique count

      2:36
    • 61. 3.1 - % of Grand Total

      2:09
    • 62. 3.2 - % of Column Total

      2:15
    • 63. 3.3 - % of Row Total

      1:55
    • 64. 3.4 - % Of

      4:03
    • 65. 3.5 - % of Parent Row Total (NEW IN EXCEL 2010)

      3:19
    • 66. 3.6 - % of Parent Column Total (NEW IN EXCEL 2010)

      2:23
    • 67. 3.7 - % of Parent Total (NEW IN EXCEL 2010)

      2:52
    • 68. 3.8 - Difference From

      4:54
    • 69. 3.9 - % Difference From

      4:18
    • 70. 3.10 - Running Total in

      2:20
    • 71. 3.11 - % Running Total in (NEW IN EXCEL 2010)

      2:58
    • 72. 3.12 - Rank Smallest to Largest (NEW IN EXCEL 2010)

      2:14
    • 73. 3.13 - Rank Largest to Smallest (NEW IN EXCEL 2010)

      2:21
    • 74. 3.14 - Index

      3:45
    • 75. 3.15 - Shortcuts to Show Values As

      1:16
    • 76. 3.16 - ACCOUNTING: % of Revenue Margins

      2:38
    • 77. 3.17 - FINANCIAL: Actual v Plan Variance Report

      4:43
    • 78. 4.1 - Group by Date

      2:45
    • 79. 4.2 - Group by Months

      1:51
    • 80. 4.3 - Group by Quarters & Years

      1:48
    • 81. 4.4 - Group by Sales ranges

      3:19
    • 82. 4.5 - Group by Text fields

      2:16
    • 83. 4.6 - Group by Time

      1:45
    • 84. 4.7 - Shortcuts to Grouping

      1:39
    • 85. 4.8 - Grouping by Half Years

      1:55
    • 86. 4.9 - Group by a Date that starts on a Monday

      2:00
    • 87. 4.10 - Grouping by a custom date

      1:49
    • 88. 4.11 - Group by fiscal years & quarters

      6:36
    • 89. 4.12 - Errors when grouping by dates

      2:54
    • 90. 4.13 - Group two pivot tables independently

      3:52
    • 91. 4.14 - Fixing the problem of counting grouped sales

      0:45
    • 92. 4.15 - Display dates that have no data

      0:59
    • 93. 4.16 - ACCOUNTING: Quarterly Comparative Report

      6:45
    • 94. 4.17 - FINANCIAL: Min & Max Bank Balance

      4:42
    • 95. 5.1 - Sorting by Largest or Smallest

      1:59
    • 96. 5.2 - Sort an Item Row (Left to Right)

      1:31
    • 97. 5.3 - Sort manually (drag, write, right click)

      2:01
    • 98. 5.4 - Sort using a Custom List

      3:20
    • 99. 5.5 - Override a Custom List sort

      1:27
    • 100. 5.6 - Sort row from A-Z and sales from Z-A

      1:23
    • 101. 5.7 - Sort new items added to your data source

      1:18
    • 102. 5.8 - Clear a sort

      0:28
    • 103. 5.9 - Sort Largest to Smallest Grand Totals

      0:26
    • 104. 6.1 - Filter by Dates

      6:48
    • 105. 6.2 - Filter by Labels - Text

      2:43
    • 106. 6.3 - Filter by Labels - Numerical Text

      2:51
    • 107. 6.4 - Filter by Values

      3:58
    • 108. 6.5 - Filter by Values - Top or Bottom 10 Items

      2:13
    • 109. 6.6 - Filter by Values - Top or Bottom %

      1:46
    • 110. 6.7 - Filter by Values - Top or Bottom Sum

      1:49
    • 111. 6.8 - Filter by Report Filter

      3:38
    • 112. 6.9 - Shortcuts to filters

      1:43
    • 113. 6.10 - Keep or hide selected items

      0:57
    • 114. 6.11 - Filter by Text wildcards * and ?

      3:27
    • 115. 6.12 - Filter by multiple fields

      1:07
    • 116. 6.13 - Apply multiple filters

      1:52
    • 117. 6.14 - Filter by multiple values

      1:14
    • 118. 6.15 - Include new items in manual filter

      1:58
    • 119. 6.16 - Clear filters with one click

      1:37
    • 120. 6.17 - Add a filter for the column items

      0:40
    • 121. 6.18 - ACCOUNTING: Top 5 Expenses report

      2:30
    • 122. 6.19 - FINANCIAL: Top 25% of Channel Partners

      2:18
    • 123. 7.1 - Insert a Slicer

      4:01
    • 124. 7.2 - Slicer Styles

      2:26
    • 125. 7.3 - Creating a custom style

      5:38
    • 126. 7.4 - Copy a custom style into a new workbook

      1:40
    • 127. 7.5 - Slicer Settings

      4:50
    • 128. 7.6 - Slicer Size & Properties

      3:03
    • 129. 7.7 - Slicer Connections for multiple pivot tables

      3:24
    • 130. 7.8 - Different ways to filter a Slicer

      1:22
    • 131. 7.9 - Use one slicer for two pivot tables

      1:26
    • 132. 7.10 - Lock the workbook but not the slicer

      1:33
    • 133. 7.11 - Interactive employee photos with Slicers! FUN!

      9:28
    • 134. 7.12 - ACCOUNTING: Select a Monthly P&L report with a Slicer

      4:20
    • 135. 7.13 - FINANCIAL: Base, Best & Worst case Forecast

      5:10
    • 136. 8.1 - Creating a Calculated Field

      4:06
    • 137. 8.2 - Use an existing Calculated Field in a new calculation

      2:23
    • 138. 8.3 - Editing a Calculated Field

      2:03
    • 139. 8.4 - Excel formulas & Calculated Fields

      3:07
    • 140. 8.5 - Creating a Calculated Item

      4:12
    • 141. 8.6 - Use an existing Calculated Item in a new calculation

      1:43
    • 142. 8.7 - Editing a Calculated Item

      2:26
    • 143. 8.8 - Excel formulas & Calculated Items

      1:58
    • 144. 8.9 - Calculated Item on Column Labels

      2:14
    • 145. 8.10 - Shortcomings of Calculated Items

      2:03
    • 146. 8.11 - Solve Order for Calculated Items

      4:11
    • 147. 8.12 - List Calculated Field & Item formulas

      1:12
    • 148. 8.13 - Remove a Calculated Field temporarily

      1:00
    • 149. 8.14 - Order of operations

      1:56
    • 150. 8.15 - ACCOUNTING: Creating a P&L Pivot Table Report

      8:25
    • 151. 8.16 - FINANCIAL: Actuals v Plan with Calculated Fields

      6:36
    • 152. 9.1 - Insert a Pivot Chart

      3:24
    • 153. 9.2 - Insert a Slicer with a Pivot Chart

      2:00
    • 154. 9.3 - Pivot Chart Designs

      3:55
    • 155. 9.4 - Pivot Chart Layouts

      5:04
    • 156. 9.5 - Pivot Chart Formats

      5:57
    • 157. 9.6 - Limitations of Pivot Charts & workarounds

      2:01
    • 158. 9.7 - Saving a Pivot Chart template

      1:36
    • 159. 9.8 - Shortcuts to formatting a Pivot Chart

      1:23
    • 160. 9.9 - Link chart title to a pivot cell

      1:05
    • 161. 9.10 - Copying a second chart

      0:51
    • 162. 9.11 - Put a chart on a separate page with F11

      0:47
    • 163. 9.12 - Insert Pivot Chart straight from the data source

      0:59
    • 164. 9.13 - Paste Pivot Chart to your email as a picture

      1:12
    • 165. 9.14 - Paste Pivot Chart to PowerPoint & make live updates

      1:34
    • 166. 9.15 - Printing a Pivot Chart

      1:27
    • 167. 9.16 - Include a Sparkline with your pivot table

      1:24
    • 168. 9.17 - Charts Do´s & Don'ts

      2:45
    • 169. 9.18 - Change Chart Type with Slicers! FUN!!!

      8:08
    • 170. 9.19 - Workaround to creating an interactive Scatter graph

      2:27
    • 171. 9.20 - ACCOUNTING: P&L Pivot Table report with Graphs

      8:21
    • 172. 9.21 - FINANCIAL: Pivot Table Slicer & Chart Dashboard

      15:55
    • 173. 10.1 - Intro to Conditional Formatting

      2:43
    • 174. 10.2 - Highlight Cell Rules based on values

      1:27
    • 175. 10.3 - Highlight Cell Rules based on text labels

      0:54
    • 176. 10.4 - Highlight Cell Rules based on date labels

      1:22
    • 177. 10.5 - Top & Bottom Rules

      3:31
    • 178. 10.6 - Data Bars, Color Scales & Icon Sets (NEW IN EXCEL 2010)

      5:49
    • 179. 10.7 - Format only cells that contain - For Bonuses

      1:45
    • 180. 10.8 - Format only Top or Bottom ranked values - Top 3 sales per year

      1:39
    • 181. 10.9 - Format values that are above or below the average - For Promotions

      1:44
    • 182. 10.10 - Use a formula to determine which cells to format

      2:39
    • 183. 10.11 - Use selected cells to format multiple fields

      1:38
    • 184. 10.12 - All cells showing values to format multiple fields

      1:38
    • 185. 10.13 - Control Conditional Formatting with Slicers

      2:54
    • 186. 10.14 - Show text in the Pivot Table Values area

      4:51
    • 187. 10.15 - Cond Format blank cells or cells

      1:14
    • 188. 10.16 - ACCOUNTING: Accounts Receivable Ageing Report Matrix

      6:17
    • 189. 10.17 - FINANCIAL: Conditionally Format your sales results

      3:32
    • 190. 11.1 - Intro to GETPIVOTDATA

      4:59
    • 191. 11.2 - Create a custom report with GETPIVOTDATA

      5:24
    • 192. 11.3 - Reference Dates with GETPIVOTDATA

      3:17
    • 193. 11.4 - Data validation with GETPIVOTDATA

      2:52
    • 194. 11.5 - Shortfalls of GETPIVOTDATA

      1:48
    • 195. 11.7 - ACCOUNTING: Live forecasting with GETPIVOTDATA

      7:24
    • 196. 11.8 - FINANCIAL: Channel Analysis with GETPIVOTDATA

      5:30
    • 197. 12.1 - Adding the Developer tab & disabling macros

      2:29
    • 198. 12.2 - Record a simple macro to Refresh a pivot table

      4:26
    • 199. 12.3 - Date filter macro

      4:27
    • 200. 12.4 - Different pivot table views macro

      4:43
    • 201. 12.5 - Top 10 macro

      3:30
    • 202. 12.6 - Add macro to quick access toolbar

      1:20
    • 203. 13.1 - Reducing file memory by copying existing pivot table

      1:29
    • 204. 13.2 - Reducing file memory by deleting the data source

      1:46
    • 205. 13.3 - Reducing file memory by saving file as Excel Binary Workbook

      0:57
    • 206. 13.4 - Reducing file memory by keeping data source in MS Access

      2:12
    • 207. 13.5 - Compatibility Issues with Excel 2007 and Excel 2010

      1:19
    • 208. 13.6 - Sharing a Pivot Table via OneDrive

      2:49
    • 209. 14.1 - Sales Forecasting with Calculated Fields

      4:18
    • 210. 14.2 - Consolidate with a Pivot Table

      4:46
    • 211. 14.3 - Frequency distribution with a Pivot Table

      2:16
    • 212. 14.4 - Break Even Model

      3:04
    • 213. 14.5 - Several slicer custom styles for you to use

      2:03
    • 214. 14.6 - Interactive Balance Sheet Pivot Table

      12:21
    • 215. 14.7 - Monthly Sales Manager Performance Report

      4:35
    • 216. 14.8 - Reconciling customer payments

      1:42
    • 217. 15.1 - Cosmetic changes in the PivotTable Tools Ribbon Menu

      2:02
    • 218. 15.2 - Recommended Pivot Tables

      1:55
    • 219. 15.3 - Distinct Count

      1:47
    • 220. 15.4 - Timelime Slicers

      4:12
    • 221. 15.5 - Data Models

      6:55
    • 222. 16.1 - Group Periods

      1:43
    • 223. 16.2 - Multi-Select Slicer items

      0:30
    • 224. 16.3 - Pivot Chart - Expand and Collapse fields

      1:08
    • 225. 16.4 - 3D Maps

      1:42
    • 226. 17.1 - Which Excel Version Do You Have?

      1:52
    • 227. 17.2 - Default PivotTable Layout

      5:30
    • 228. 17.3 - Automatic Relationship Detection

      5:38
    • 229. 17.4 - Automatic Time Grouping

      2:50
    • 230. 17. 5 - Search in the PivotTable

      1:25