Thanks to Nick Williams from Acuity Training for creating this helpful post.

It can handle large volumes of data (millions of rows) from various sources and all of this within a single Excel file.

The most effective way to interact with the Internal Data Model is to use the Power Pivot Ribbon interface.

The Power Pivot Ribbon gives you additional functions over and above the standard Excel Data tab.

However, only the following versions of Excel 2016 support the Power Query functionality.

Unless it is already in your Excel sheet you will need a tool or connector to connect to different types of data sources and fetch your data.

This can be a complex subject depending on your data source and is beyond the scope of this article.

Both these functions in Excel are carried out by another add-in called Power Query (in Excel 2010 and 2013) Get Transform (in Excel 2013).

This is where we create the relationships between different data tables.

The combination of multiple Pivot Tables Pivot Charts with slicers can be used to create a Dashboard.

If you have other versions of the Power Pivot add-in installed, those versions are also listed in the COM Add-ins list.

Calculated Fields and KPIs can be used to create any summarizing calculations.

Add to Data Model allows you to add the data table present in the Excel into Power Pivot.

Create A Powerpivot In Excel 2016 Update All The

Update All allows you to update all the data connections established with the data model.

Settings helps to control other parameters associated with Power Pivot.

The data tab contains detailed data showing sales by country, by product type, by quarter, by sales channel etc.

The mapping tab contains that data sub-totaled by quarter, also seen below.

This can also be derived using formula in Power Pivot as well.

Make sure you have selected the range of the Data table then select the Add to data model button from the Power Pivot Tab.

It is the Power Pivot window, it has several options to edit, modify and update the data model.

Lets look at how we can create a relationship between the two.

Hence the following selections needs to be made in the pop-up.

How To Create A Powerpivot In Excel 2016 For Mac