;


Video: Highline Excel 2013 Class Video #35: Excel 2013 PivotTables: Basic To Advanced 58 Examples

Download

Do You Like This Video ?

You Can share it for your Friends.

No, Thanks.

Highline Excel 2013 Class Video #35: Excel 2013 PivotTables: Basic To Advanced 58 Examples

Description Genre : education |  Views : 63 828

Download workbook: http://people.highline.edu/mgirvin/Al...
This is the Highline Community College Class, Spreadsheet Construction taught by Michael Girvin: Basic To Advanced Excel.
Topics in this video:
1. 3 Reports with Formulas (00:01:20 min)
2. What is a PivotTable do? (00:01:43 min)
a. Summarize data
b. "Calculation with criteria"
c. Create Reports that contain calculations with conditions/criteria
3. Proper Data Set (00:02:57 min)
4. Visualize (00:03:16 min)
5. Create from Insert Tab (00:04:34 min)
6. Excel 2013 Create PivotTable dialog box keyboard: Alt, N, V (00:04:51 min)
7. Drag and drop from Field List (00:06:16 min)
8. Format to Show Field Names: Design, Report Layout, Show in Tabular Form (Format Tip #1) (00:08:37 min)
9. Number Formatting must be done on Field, not with Format Cells dialog Box (Format Tip #2) (00:09:30 min)
10. Create PivotTable Style (Format Tip #3) (00:10:32 min)
a. Limited Formatting
11. Set PivotTable Style as Default (00:12:34 min)
12. Why Called Pivot? Pivot the report (00:13:16 min)
13. Pivot Data is stored in a cache (00:14:56 min)
14. Change Source Data (00:14:56 min)
a. Formulas Update Automatically
b. PivotTables update after "Refreshing" (00:15:54 min)
i. Analyze PivotTableTools Tab
ii. Alt + F5 (00:16:17 min)
iii. Ctrl + Alt + F5
15. Copy A PivotTable Report (00:17:02 min)
16. Change Function (00:17:30 min)
17. Filter a Field (00:17:58 min)
18. Only 11 functions (00:19:33 min and 00:23:11 min)
a. See list
b. Formulas in cells can make many more calculations
19. One Stop Shopping for Calculations: Value Field Settings (00:18:57 min)
20. Text Data defaults to Count (00:21:18 min)
21. Number Data defaults to SUM (00:21:18 min)
22. Counting counts criteria: Counting records, Numbers or Text (00:22:06 min)
23. Mixed Data Defaults to Count (00:23:50 min)
24. Review: 5 steps to creating PivotTable (00:25:50 min)
25. Report we saw back in Subtotal video (00:25:40 min)
26. Report with three calculations (00:26:50 min)
27. Formulas vs PivotTables (00:28:42 min)
PivotTables: 1) Fast and easy to create calculations with criteria. 2) Requires "Refresh" when Source Data Changes. 3) You can Pivot a report and add and remove whole categories of criteria and calculations update. 4) Change the function easily. 5) Formatting is limited.
Formulas: 1) Usually more complicated to create calculations with criteria. 2) Automatically updates when Source Data Changes. 3) It is extremely difficult if not impossible to Pivot a report and add and remove whole categories of criteria. 4) Changing a function is more difficult with formulas. 5) Formatting is less limited.
28. Collapse and Expand criteria in Row Labels (00:29:48 min)
29. Extracting data to new sheet with Double Click (00:30:15 min)
30. PivotChart (00:31:03 min)
31. Group Dates: Right-click Date in Pivot and point to Group. (00:32:17 min)
32. Group Times: Right-click Time in Pivot and point to Group. (00:34:44 min)
33. Old Excel 2003 dialog box to group or ungroup a field in a second way: (00:36:07 min)
a. Keyboard: Alt, D, P.
b. This creates a separate cache of data.
34. Grouping can't be done with Data Mismatch (00:38:47 min)
35. Group Numbers: Decimal create ambiguous categories (00:40:34 min) (Integers: Lower and Upper Limits are included in Category. Decimals: Lower Limit is included and Upper Limits is not included in Category). Integer creates unambiguous categories
36. Recommended Chart: Amazing because it summarizes (00:43:11 min)
37. Recommended PivotTable (00:45:06 min)
38. Report Filter: Adds new criteria to report (00:46:01 min)
39. Show Report Filter (00:47:45 min): PivotTableTools Analyze Ribbon Tab, PivotTable group, Options, "Show Report Filter Pages".
40. Slicers (00:49:14 min): Fancy Filters: PivotTableTools Analyze Ribbon Tab, Filter group
41. Use Filter and Slicer together to extract data (00:50:14 min)
42. Timelines (00:53:13 min): PivotTableTools Analyze Ribbon Tab, Filter group Show Values As: % of Column
43. Show Values As: % of Grand Total (00:56:11 min)
44. Show Values As: % of Column (00:59:19 min)
45. Show Values As: % of Row (01:00:24 min)
46. Connect One Slicer to many PivotTables (01:01:34 min)
47. Show Values As: Running Total (01:03:21 min)
48. Show Values As: Difference From (01:05:02 min)
49. Show Values As: % Difference From (01:05:50 min)
50. Show Values As: % of Parent (01:07:15 min)
51. Modify PivotTable Style (01:08:47 min)
52. Show Values As: % of Parent Row Total (01:09:36 min)
53. Show Values As: % of Parent Column Total (01:09:36 min)
54. Show Values As: Rank (01:10:16 min)
55. Sort based on a Value Field (01:11:39 min)
56. Create Formula in PivotTable: Sales - Expense (01:12:01 min)
57. Data Model: to get new PivotTable function: Distinct Count. (01:13:53 min)
58. Use Data Model and Relationships to replace VLOOKUP (01:16:46 min)
Pivot Tables

Show Description
Hide Description

Related Videos:

Similar Videos