Excel 2013 PowerPivot Basics 03

Replace VLOOKUP with Excel 2013 Data Model & Relationships

DESCRIPTION

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:

(00:11) Get Two Tables into an Excel 2013 PivotTable Field List without using PowerPivot.
(00:54) How to indirectly access Data Model and Relationships in Excel 2013 PivotTable Field List without using PowerPivot.
(01:10) Convert Excel Proper Data set to Excel Table (Ctrl + T) and then name Table (Alt, J, T, A)
(01:30) 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.
(02:27) 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.
(02:43) Pulling Fields from both tables into the PivotTable when there is no established Relationship between the two tables invokes the “Yellow Relationship” button.
(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.
(04:24) Installing COM PowerPivot Add-in.
(05:10) Add Excel Tables To Data Model (Alt, B, Y)
(05:30) Creating a Relationship between two tables using Diagram View in PowerPivot.
(05:30) Creating a Relationship between two tables using Diagram View in PowerPivot.
(06:13) Create PivotTable based on PowerPivot Data Model.

WORKBOOKS