Highline Excel 2013 Class Video 35

Excel 2013 PivotTables: Basic To Advanced 58 Examples

DESCRIPTION

Topics in this video:
1. 3 Reports with Formulas.
2. What is a PivotTable do?
a. Summarise data
b. “Calculation with criteria”
c. Create Reports that contain calculations with conditions/criteria
3. Proper Data Set.
4. Visualise.
5. Create from Insert Tab.
6. Excel 2013 Create PivotTable dialog box keyboard: Alt, N, V.
7. Drag and drop from Field List.
8. Format to Show Field Names: Design, Report Layout, Show in Tabular Form (Format Tip #1).
9. Number Formatting must be done on Field, not with Format Cells dialog Box (Format Tip #2).
10. Create PivotTable Style (Format Tip #3).
a. Limited Formatting
11. Set PivotTable Style as Default.
12. Why Called Pivot? Pivot the report.
13. Pivot Data is stored in a cache.
14. Change Source Data.
a. Formulas Update Automatically
b. PivotTables update after “Refreshing”
i. Analyse PivotTableTools Tab
ii. Alt + F5
iii. Ctrl + Alt + F5
15. Copy A PivotTable Report.
16. Change Function.
17. Filter a Field.
18. Only 11 functions.
a. See list
b. Formulas in cells can make many more calculations
19. One Stop Shopping for Calculations: Value Field Settings.
20. Text Data defaults to Count.
21. Number Data defaults to SUM.
22. Counting counts criteria: Counting records, Numbers or Text.
23. Mixed Data Defaults to Count.
24. Review: 5 steps to creating PivotTable.
25. Report we saw back in Subtotal video.
26. Report with three calculations.
27. Formulas vs PivotTables.
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.
29. Extracting data to new sheet with Double Click.
30. PivotChart.
31. Group Dates: Right-click Date in Pivot and point to Group.
32. Group Times: Right-click Time in Pivot and point to Group.
33. Old Excel 2003 dialog box to group or ungroup a field in a second way:
a. Keyboard: Alt, D, P.
b. This creates a separate cache of data.
34. Grouping can’t be done with Data Mismatch.
35. Group Numbers: Decimal create ambiguous categories.
(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 summaries.
37. Recommended PivotTable.
38. Report Filter: Adds new criteria to report.
39. Show Report Filter: PivotTableTools Analyse Ribbon Tab, PivotTable group, Options, “Show Report Filter Pages”.
40. Slicers: Fancy Filters: PivotTableTools Analyse Ribbon Tab, Filter group
41. Use Filter and Slicer together to extract data
42. Timelines (00:53:13 min): PivotTableTools Analyse Ribbon Tab, Filter group Show Values As: % of Column
43. Show Values As: % of Grand Total.
44. Show Values As: % of Column.
45. Show Values As: % of Row.
46. Connect One Slicer to many PivotTables.
47. Show Values As: Running Total.
48. Show Values As: Difference From.
49. Show Values As: % Difference From.
50. Show Values As: % of Parent.
51. Modify PivotTable Style.
52. Show Values As: % of Parent Row Total.
53. Show Values As: % of Parent Column Total.
54. Show Values As: Rank.
55. Sort based on a Value Field.
56. Create Formula in PivotTable: Sales – Expense.
57. Data Model: to get new PivotTable function: Distinct Count.
58. Use Data Model and Relationships to replace VLOOKUP.

WORKBOOKS

Download “Highline Excel 2013 Workbooks” Highline-Excel-2013-Video-Workbooks.zip – Downloaded 169 times – 21 MB




Be the first to comment

Leave a Reply