Excel 365 Part 23 – PowerPivot

Power Pivot is a technique used to create relationships between sheets in Excel similar to a relational database. Once you convert records located in different sheets to tables, you will then be able to analyze the data in a way that cannot be done in a traditional PivotTable in Excel. Power Pivot is an Add-In tool which means it must be added using the Add-Ins feature in the Excel Options menu. The following topics will be covered: Create Table, Add To Model, Define Relationships, Measures, DAX program language, Calculated Fields, and Key Performance Indicators (KPI).

Video Series Part 23 - This is a comprehensive guide to learning Excel from the basics to advanced programming techniques. These videos are labeled in order beginning with Part 1, 2, 3, 4, etc. Each video can be downloaded and freely distributed to anyone desiring to learn Excel in great depth.
Higher Quality Video: www.elearnlogic.com/media/excel365-3-ch8.mp4

- Click +Rumble if the video was helpful.
- Click Subscribe so you won’t miss out on any new videos.
- Click the Share button so you can help others expand their skills.

Each video provides a great overview of the concepts, but the courseware provides additional details for a better understanding. You will be able to follow along with the courseware while watching this video.

Courseware: Excel 365 - Formulas, Charts, And PivotTables
The courseware is available on Amazon at: https://www.amazon.com/dp/1660224934

Chapter 8 - Power Pivot
Power Pivot is a technique used to create relationships between sheets in Excel similar to a relational database. Once you convert records located in different sheets to tables, you will then be able to analyze the data in a way that cannot be done in a traditional PivotTable in Excel. Power Pivot is an Add-In tool which means it must be added using the Add-Ins feature in the Excel Options menu. The following topics will be covered: Create Table, Add To Model, Define Relationships, Measures, DAX program language, Calculated Fields, and Key Performance Indicators (KPI).

Section 1: Power Pivot Installation
This section will cover methods to install or activate Power Pivot capabilities. There are different procedures for different Excel releases. Before Excel 2016, you had to install a Microsoft download and use the Excel Add-In feature located in Excel Options.

Section 2: Data Table
This will establish a Data Table to be used as input to Power Pivot and PivotTables. The concepts to be covered include: Data Table, Refresh New Fields, and Smart Rename.

Section 3: Autodetect And Manual Relationships
In this section, we will explain the difference between Autodetect Relationships (an Excel 2016 feature) and Manual Relationships (an Excel 2010/2013 feature). We will use the Data Model feature to add the Table to the PowerPivot interface in order to build the relationships between tables. We will also cover Manual Relationships and Autodetect Relationships.

Section 4: Power Pivot More Tables
This is a different technique that allows the Tables to be Related without predefining the relationships. It will autodetect the possible relationships and connect them up.

Section 5: Power Pivot Window
This will cover the “Add to Data Model” technique used to build Relationships between Tables. This is the most accurate and reliable technique to build Relationships. If the other techniques (mentioned above) fail, this can be used to fix any problem encountered. The concepts covered include: Power Pivot Window, Add to Data Model, Create Relationships, Relationship Screen, Manage Relationships, Diagram View, Create PivotTable, Rename Tables, Office Repair, and Object Reference Error.

Section 6: Power Pivot Ribbon Tab
This section will cover the Icons located in the Excel Power Pivot Ribbon Tab. The primary focus of DAX formulas is to build Measures and use them to build KPIs. DAX is a programming language used to build calculated fields or formulas. KPI uses the DAX result to display indicators or buttons when a record has alert condition. The following concepts will be covered: New Measure, Manage Measures, KPIs, Add to Data, Model, Update All, Detect, and Settings.

Section 7: Power Pivot Window (Home Ribbon Tab)
This section will cover the Home Ribbon Tab in the “Power Pivot for Excel” window. The concepts to be covered are Clipboard, Refresh, Formatting Group, Formatting Tools, Sort & Filter, Find, Calculations, Diagram View, Show Hidden, and Calculation Area.

This Courseware/Video is based on Excel 365 and documents command differences between Excel 2010, Excel 2013, Excel 2016, and Excel 2019.

If you have any questions, please contact: Jeff Hutchinson, Website: http://www.elearnlogic.com, Email: jeffhutch@elearnlogic.com