Excel 2013 PowerPivot Basics #03: Replace VLOOKUP with Excel 2013 Data Model & Relationships

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.

[excelisfun]

Excel 2013 PowerPivot Basics #03: Replace VLOOKUP with Excel 2013 Data Model & Relationships

See how to replace VLOOKUP with Excel 2013 Data Model and Relationships:
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.

[DanielLamarche]

I dedicated this Sunday to watch as much PP videos as possible. Thanks.
But after I have to vacuum the house and clean the kitchen!!! No time to loose!
Daniel

[shahzadhabib]

Thank you for tutorial. Two thumbs up. I am an older person fighting with Excel, your Excel tutorials helped me lot.

Habib

[excelisfun]

@hamy72, you are welcome!!!

Your Google setting do not allow me to directly reply to your comments.

[krismaly]

I enjoyed this video and recommend to others

Thanks for educating the community and appreciate all your efforts

Thanks a bunch

[gilbertosegoviano]

You are amazing as a teacher, thank you very much for sharing your videos

[JudeBarak]

Hi, Thanks for all your great tutorials. 
I found out that once you add the tables to the data model, the dates grouping option is unavailable and you'll have to use month & years columns.... So its nice to work that way only if you don't need grouping by dates....

[michaelfrishman]

very very comprehensive tutorial, thank you very much !

[kush.blackbelt]

Excellent videos bro.. All your videos are just excellent

[pratikkhambhadiya]

Thank you very much for all this tutorials

[geliegurl]

I find your tutorials very useful and not to mention you even teach excel keyboard shortcuts which other tutorials will not bother to put. I just found your channel and have watched just a few so If I may ask, do you have a list of excel keyboard shortcuts, like the shortcuts for new pivot table, renaming table, etc.? Hope you can provide it.. I know it'll help a lot of others too. If you do and have posted a video or link already, may you please send me a link? Will greatly appreciate it... thanks!

[lisapourier]

Hi there, thanks for this video! I am wondering about your duplicates in your left table? How is it that your linking of the tables still works: I receive an error even when I use a table without duplicates as base for the linking. Any tips would be appreciated.

[steverieve]

Ok use this but got a Exception Type was thrown.  any idea why?Also not able to create  Calc Fields when combining tables.  Ay help on this?

[tomash]

Interesting didn't work for me when I tried to insert pivot which would create a data model without powerpivot

[kittydoes]

Help please. When i try to create relationships from the tables, it gives me an error message that i need at least a column with unique values. What does it mean?

[michalroesler]

f**k yeah - learn the s**t out of these power pivots :-)
I already hit the like button in the very begging of @ExcelIsFun videos. :-)