Excel 2013 PowerPivot Basics #01: Introduction To PowerPivot for Excel 2013

Basics of PowerPivot: Why PowerPivot?, Add to Data Model, Relationships, Calculated Column, Calculated Field, Build report with PivotTable based on Data Model:
1. (00:22 min mark) Smartest People with PowerPivot
2. (01:31 min mark) Which Versions of Excel 2013 contain PowerPivot
3. (01:46 min mark) Install PowerPivot for Excel 2013
4. (02:14 min mark) What aspects of PowerPivot is so amazing
5. (06:46 min mark) When not to use PowerPivot
6. (07:28 min mark) Amazing PowerPivot Columnar Database, also known as Data Model or PowerPivot xVelocity engine
7. (09:46 min mark) Example of Multiple tables when you don’t want to use PowerPivot, but instead would want to use Helper Columns and VLOOKUP functions. In essences, PowerPivot will not permanently replace all VLOOKUPs. When you have multiple tables, sometimes PowerPivot will be the perfect solution, other times VLOOKUP will be the perfect solution.
8. (14:19 min mark) Start of PowerPivot Example
9. (14:48 min mark) Convert Proper Data Sets to Excel Tables BEFORE importing into PowerPivot, Create Table Keyboard = Ctrl + T, Name Table Keyboard = Alt, J, T, A
10. (15:43 min mark) Why Excel Import must be Excel Table.
11. (15:55 min mark) “Fact Tables and Dimension Tables” = Database terms, and, “Transaction Tables and Lookup Tables” = Excel Terms”
12. (16:22 min mark) Import Excel Tables into “Manage Data Model” window using “Add to Data Model”, Keyboard = Alt, B, Y
13. (16:44 min mark) Manage Data Model window
14. (17:56 min mark) We start to build the “Data Model” by bringing the Tables into the “Manage Data Model” window.
15. (18:11 min mark) Building Relationships between Tables using the “Diagram View”
16. (18:25 min mark) Arranging Tables in “Star Schema” view or “Criteria/Filters Flow Downward” view.
17. (19:05 min mark) Continuing to build the Data Model by adding Relationships between Tables. Relationships replace VLOOKUPs.
18. (20:26 min mark) One To Many Relationship
19. (22:16 min mark) PivotTable mistake if you don’t create correct Relationships
20. (22:25 min mark) Multiple Tables appear in PivotTable Field list.
21. (23:26 min mark) Correct PivotTable with correct Relationships: Units by Region PivotTable. This means we can drag and drop fields into the PivotTable from Multiple Tables.
22. (24:21 min mark) Format Tables in “Manage Data Model” window.
23. (25:20 min mark) Sort columns in Tables in “Manage Data Model” window.
24. (25:35 min mark) Adjust view of Measure Grid below Tables.
25. (25:44 min mark) Create Calculated Column in in “Manage Data Model” window.
26. (26:22 min mark) RELATED function replaces VLOOKUP
27. (26:39 min mark) We must use Formula Bar to create formulas.
28. (27:11 min mark) Table Names and Field Names are similar to Excel Table Formula Nomenclature (Structured References): Tables Names are followed by Field Names in square brackets.
29. (27:39 min mark) Row Context for Calculated Columns: No more Cell References like in normal Excel formulas.
30. (28:58 min mark) Formula for Net Revenue given price, discount and units.
31. (29:04 min mark) Formula Convention for Table Names and Field Names: Tables Names are followed by Field Names in square brackets. From Russo and Ferrari.
32. (30:36 min mark) Functions in PowerPivot are called DAX functions. DAX = Data Analysis Expressions.
33. (30:58 min mark) Number Formatting From Calculated Column Formula appears in PivotTable!!!!
34. (32:23 min mark) Implicit vs. Explicit Formulas. Explicit is better because you can use them in any other PivotTable or Calculated Column or Calculated Field (Measure)
35. ( min mark) Create Calculated Field (Measure) in the Measure Grid below the Table. These are Explicit Formulas.
36. (33:56 min mark) Conventions for created Calculated Fields.
37. (34:52 min mark) Add Number Formatting to Calculated Field.
38. (36:00 min mark) Filter Context: Calculated Fields (Measures) resect the Criteria dropped into the Row/Column/Filter and Slicer area of PivotTables. The Criteria in the PivotTable actually filters the underlying Data Model.

[HiroshimaMS]

Power Pivot is like working in Access, wow! and I lived 10 years years without knowing that, what a shame! Fortunately and finally I found you! Thank you for your excellent work!

[excelisfun]

Excel 2013 PowerPivot Basics #01: Introduction To PowerPivot for Excel 2013 

Basics of PowerPivot: Why PowerPivot?, Add to Data Model, Relationships, Calculated Column, Calculated Field, Build report with PivotTable based on Data Model:
1. (00:22 min mark) Smartest People with PowerPivot
2. (01:31 min mark) Which Versions of Excel 2013 contain PowerPivot
3. (01:46 min mark) Install PowerPivot for Excel 2013
4. (02:14 min mark) What aspects of PowerPivot is so amazing
5. (06:46 min mark) When not to use PowerPivot
6. (07:28 min mark) Amazing PowerPivot Columnar Database, also known as Data Model or PowerPivot xVelocity engine
7. (09:46 min mark) Example of Multiple tables when you don’t want to use PowerPivot, but instead would want to use Helper Columns and VLOOKUP functions. In essences, PowerPivot will not permanently replace all VLOOKUPs. When you have multiple tables, sometimes PowerPivot will be the perfect solution, other times VLOOKUP will be the perfect solution.
8. (14:19 min mark) Start of PowerPivot Example
9. (14:48 min mark) Convert Proper Data Sets to Excel Tables BEFORE importing into PowerPivot, Create Table Keyboard = Ctrl + T, Name Table Keyboard = Alt, J, T, A
10. (15:43 min mark) Why Excel Import must be Excel Table.
11. (15:55 min mark) “Fact Tables and Dimension Tables” = Database terms, and, “Transaction Tables and Lookup Tables” = Excel Terms”
12. (16:22 min mark) Import  Excel Tables into “Manage Data Model” window using “Add to Data Model”, Keyboard = Alt, B, Y
13. (16:44 min mark) Manage Data Model window
14. (17:56 min mark) We start to build the “Data Model” by bringing the Tables into the “Manage Data Model” window.
15. (18:11 min mark) Building Relationships between Tables using the “Diagram View”
16. (18:25 min mark) Arranging Tables in “Star Schema” view or “Criteria/Filters Flow Downward” view.
17. (19:05 min mark) Continuing to build the Data Model by adding Relationships between Tables. Relationships replace VLOOKUPs.
18. (20:26 min mark) One To Many Relationship
19. (22:16 min mark) PivotTable mistake if you don’t create correct Relationships
20. (22:25 min mark) Multiple Tables appear in PivotTable Field list.
21. (23:26 min mark) Correct PivotTable with correct Relationships: Units by Region PivotTable. This means we can drag and drop fields into the PivotTable from Multiple Tables.
22. (24:21 min mark) Format Tables in “Manage Data Model” window.
23. (25:20 min mark) Sort columns in Tables in “Manage Data Model” window.
24. (25:35 min mark) Adjust view of Measure Grid below Tables.
25. (25:44 min mark) Create Calculated Column in in “Manage Data Model” window.
26. (26:22 min mark) RELATED function replaces VLOOKUP
27. (26:39 min mark) We must use Formula Bar to create formulas.
28. (27:11 min mark) Table Names and Field Names are similar to Excel Table Formula Nomenclature (Structured References): Tables Names are followed by Field Names in square brackets.
29. (27:39 min mark) Row Context for Calculated Columns: No more Cell References like in normal Excel formulas.
30. (28:58 min mark) Formula for Net Revenue given price, discount and units.
31. (29:04 min mark) Formula Convention for Table Names and Field Names: Tables Names are followed by Field Names in square brackets. From Russo and Ferrari.
32. (30:36 min mark) Functions in PowerPivot are called DAX functions. DAX = Data Analysis Expressions.
33. (30:58 min mark) Number Formatting From Calculated Column Formula appears in PivotTable!!!!
34. (32:23 min mark) Implicit vs. Explicit Formulas. Explicit is better because you can use them in any other PivotTable or Calculated Column or Calculated Field (Measure)
35. ( min mark) Create Calculated Field (Measure) in the Measure Grid below the Table. These are Explicit Formulas.
36. (33:56 min mark) Conventions for created Calculated Fields.
37. (34:52 min mark) Add Number Formatting to Calculated Field.
38. (36:00 min mark) Filter Context: Calculated Fields (Measures) resect the Criteria dropped into the Row/Column/Filter and Slicer area of PivotTables. The Criteria in the PivotTable actually filters the underlying Data Model.

[jessontvarghese]

Best teacher I have ever seen. Covers all the exceptional scenarios while teaching with templates.

[FsoOmar]

I'm pretty excited this Friday! I constructed my first ever POWER PIVOT. Billion thanks to ExcelIsFun. Mike, you are my best Excel instructor. May God bless you for your free tutorials.

[ForceMight]

Caps A + M + A + Z + I + N + G enter enter.... its really great watching you do your thing. Made learning this such a breeze

[juliagarb]

If I could, I would put 100 thumbs up! I got into Power BI and couldn't make a precise sense of the calculated fields and columns. Glad I found this video! Hands down TY.

[FPrimeHD]

I'm about to give training on PowerPivot to my analytics group. We use Tableau, R, and SQL Server, so I'm here refreshing up on this. After using Tableau for 6 months, it really makes me appreciate how powerful backend modeling is with Power BI / Power Pivot / Power Query. They are light years ahead of Tableau in that respect. As always, thank you for producing amazing video's!

[nadermounir]

Absolutely amazing video for someone who is new to Power pivot like myself. You hard work Mike is helping many people at work. Thank you so much

[evanrudibaugh]

I love your style. You make database management hotkeys sound like pure excitement. ALT J + T + A!!!! Bam!! ENTER!! Multikill! Finish him! Right, right, CONTROL Flawless victory!!
Is this what normal people feel when watching sports? OMG... please make competitive speed-Excel-ing a sport.
I also have the video set to 2x speed, which adds so many levels of enjoyment.

[friedelmollemeyer]

this is a great Lesson volume - very good because all exercisable files are possible to download

[ChPetru]

In all honesty, BEST trainer i have ever listened. Amazing work!

[lakmohammed]

Started working on excel recently and realized its fun, came on youtube for more and you made me realize it's more than fun.
Good Job.

[sazzadurrahman]

I think you are one of the best trainers in the world

[z]

A masterclass narrative by a power user expert.No waffle you learn the operational need to do PPivots and great use of short cut keys.
Must see for freshman.

[__HumanBeing]

You are the law.. I thought I was an excel expert (and I can perform advanced techniques in VBA code) but looking at your tutorials on arrays and some crazy methods makes me rethink what I really know about formulas! haha thank you very much, keep the good work! The more you share the more you grow up!

[ricardoalexandre]

Damn....The best Tutorial for Power Pivot Basics !!! I was not understanding all the meaning and advantages for using power pivot...but now I get the "hole" idea! Thanks a lot!

[chantyclause]

I'm excited to take your course! I'm an Access user who finds the functionality in Excel to be lacking. This looks like a great way to use the two and be able to interact with all the non-Access users here at work!

[MrJamesHWard]

You are a generous human being. Thank you for your service.

[saum]

I went through the MS Tutorial on PowerPivot... learnt a bit...but this video is much better - Mr. Excel - you rock

[sforti]

i subscribed to Lynda.com.  I still go back to "Excel is Fun."  You are one of the best kept secrets in transforming users into Power Users.  Keep up your good work.