Download file: http://people.highline.edu/mgirvin/excelisfun.htm Scroll to down to PowerPivot Section.
See how to replace VLOOKUP with Excel 2013 Data Model and Relationships. See how to get two or more tables into a PivotTable Field List:
1. (00:11 min mark) Get Two Tables into an Excel 2013 PivotTable Field List without using PowerPivot.
2. (00:54 min mark) How to indirectly access Data Model and Relationships in Excel 2013 PivotTable Field List without using PowerPivot.
3. (01:10 min mark) Convert Excel Proper Data set to Excel Table (Ctrl + T) and then name Table (Alt, J, T, A)
4. (01:30 min mark) When you invoke the Insert PivotTable dialog box (Alt, N, V) the “Add this data to Data Model” check box appears. By adding the Table from this dialog, you can add Table to Data Model. Even though we do not have PowerPivot, the Columnar Database / XVelocity engine is still in Excel 2013 by default.
5. (02:27 min mark) ALL tab in PivotTable Field List: if you have Two Excel Tables in an Excel 2013 workbook, when you invoke the Insert PivotTable, the two tables will appear.
6. (02:43 min mark) Pulling Fields from both tables into the PivotTable when there is no established Relationship between the two tables invokes the “Yellow Relationship” button.
7. (03:54) When you create a Relationship between the two tables with the “Yellow Relationship” button, the second Table is added to the Data Model and a Relationship is established.
8. (04:24 min mark) Installing COM PowerPivot Add-in.
9. (05:10 min mark) Add Excel Tables To Data Model (Alt, B, Y)
10. (05:30 min mark) Creating a Relationship between two tables using Diagram View in PowerPivot.
11. (05:30 min mark) Creating a Relationship between two tables using Diagram View in PowerPivot.
12. (06:13 min mark0 Create PivotTable based on PowerPivot Data Model.
Add two or more tables to PivotTable Field List.