Excel 2013 PivotTables: Basic To Advanced 58 Examples 
Excel 2013 PivotTables: Basic To Advanced 58 Examples
by Highline
Video Lecture 35 of 50
Not yet rated
Views: 1,107
Date Added: March 14, 2015

Lecture Description

Download workbook: people.highline.edu/mgirvin/AllClasses/214_2013/214/Busn214_2013.htm

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

Course Index

  1. Back To Basics: Number Format, Keyboards, PivotTables, Formulas
  2. Comprehensive Excel Formula Types & Elements Video 12 Examples
  3. How Formulas Calculate: Order of Precedence in Excel
  4. Percentage Increase or Decrease Formula
  5. Cell References: Relative, Absolute & Mixed (8 Examples)
  6. Scenarios To Save Sets Of Formula Inputs, Paste Special Trick
  7. Worksheet & Workbook References, 3-D Cell References
  8. Excel Table Formula Nomenclature / Structured References 22 Ex.
  9. Defined Names & Aggregate Functions
  10. AND & BETWEEN Criteria: SUMIFS, DSUM, COUNTIFS, AVERAGEIFS
  11. Calculations With OR Criteria: COUNTIFS, DCOUNTA, SUMIFS, DSUM
  12. AND & OR Criteria Calculations: D Functions like DSUM, DCOUNT
  13. AND & OR Criteria Calculations: Table Feature
  14. AND, OR, IF Functions For AR Dept (Multiple Logical Tests)
  15. IF & IS Functions To Deliver Numbers, Text, Formulas, Nested IFs
  16. Round Functions: ROUND, MROUND, ROUNDUP, CEILING, more...
  17. Introduction To SUMPRODUCT Function
  18. VLOOKUP Function 20 Examples, VLOOKUP Formula, Excel VLOOKUP
  19. Text Functions RIGHT, LEFT, MID, SEARCH for Partial Text VLOOKUP
  20. VLOOKUP, DGET & MATCH For Looking Up Records 7 Examples
  21. INDEX and MATCH Functions For Unusual Lookups 4 Examples
  22. CHOOSE function to lookup table for VLOOKUP With Multiple Tables
  23. VLOOKUP and IFNA for Commissions
  24. Getting Top 5 Values With LARGE And Bottom 5 With SMALL
  25. Goal Seek: Get Formula To Evaluate To Desired Result
  26. Excel Compare Two Lists Formula: Item In List? Item Not In List?
  27. How To Track Down Excel Formula Errors (16 Examples)
  28. Excel Data Validation: List, Date, Time, Custom (22 Examples)
  29. Flash Fill: Extract, Combine, Insert, Reverse Data (25 Example)
  30. Custom Number Formatting & Text Functions, (33 Examples)
  31. Date Functions: EDATE, EOMONTH, DATE, YEAR, MONTH, DAY, More
  32. Introduction To Array Formulas and Array Functions
  33. Sorting In Excel. Excel Sort (13 Examples)
  34. Excel Subtotals, Add Subtotals based on One or Two Columns
  35. Excel 2013 PivotTables: Basic To Advanced 58 Examples
  36. Excel Filter For Extracting Records with AND or OR Criteria
  37. Excel Advanced Filter Extract Data 26 Examples Basic - Advanced
  38. Excel Text To Columns To Split or Convert Data 7 Examples
  39. Excel Import Data: Text File, CSV File, Access, Excel File
  40. Conditional Formatting Basic To Advanced 50 Examples
  41. Review Of Chart Basics For Excel 2013
  42. Save Chart As Template in Excel 2013
  43. Set Default Chart in Excel 2013, Keyboard Shortcuts Charts
  44. Pie within Pie Chart in Excel 2013
  45. Combining Chart Types and Secondary Axis in Excel 2013
  46. Statistics: Histogram Formulas & Chart in Excel 2013
  47. Statistics: Dot Plot (Cell Chart)
  48. Conditional Formatting: Bar Chart with Data Labels
  49. Break Even Analysis Formulas & Chart, Plotting Break Even Point
  50. Recorded Macro Basics, including Absolute & Relative References

Course Description

This is the Highline Community College Class, Spreadsheet Construction taught by Michael Girvin: Basic To Advanced Excel. It contains 50 video lectures teaching the basics of Excel, as well as advanced concepts.

Be sure to download the workbook to follow along with the examples. They are found on the official course website.

Comments

There are no comments. Be the first to post one.
  Post comment as a guest user.
Click to login or register:
Your name:
Your email:
(will not appear)
Your comment:
(max. 1000 characters)
Are you human? (Sorry)