You’ve got data and lots of it. When you want to analyze all that data, learn how to use the Power Pivot add-in with Excel to import data sets, identify relationships, build PivotTables, and create PivotCharts.

How to Get the Excel Power Pivot Add-in

Power Pivot gives you the power of business insights and analytics app. You don’t need specialized training to develop data models and perform calculations. You just need to enable it before you can use it.

Instructions in this article apply to Excel 2019, 2016, 2013, and Excel for Microsoft 365.

  • Open Excel.
  • Select File > Options.
  • Select Add-Ins.
  • Select the Manage dropdown menu, then select COM Add-ins.
  • Select Go.
  • Select Microsoft Power Pivot for Excel.
  • Select OK. The Power Pivot tab is added to Excel.

Follow Along with the Tutorial

When you want to get up and running with Power Pivot quickly, learn by example. Microsoft has several example datasets available as a free download, which contain the raw data, the Data Model, and examples of data analysis. These are great learning tools that provide insight into how professionals analyze big data.

Open Excel.

Select File > Options.

Select Add-Ins.

Select the Manage dropdown menu, then select COM Add-ins.

Select Go.

Select Microsoft Power Pivot for Excel.

Select OK. The Power Pivot tab is added to Excel.

This tutorial uses the Microsoft Student Data Model sample workbook. You’ll find a download link to the sample workbook and to a completed data model in the first note on the page.

The data in this sample Excel workbook has the following:

  • The workbook contains four worksheets.
  • Each worksheet contains related data, meaning there’s at least one column heading on a worksheet matching a column heading in another worksheet.
  • The data in each worksheet is formatted as a table.
  • Every cell in the table contains data. There are no blank cells, rows, or columns in the tables.

There are other example datasets on the Microsoft website. Explore these learning resources:

  • Download data from a Microsoft Access database that describes Olympic Medals.
  • Download three Business Intelligence samples that show how to use Power Pivot to import data, create relationships, build PivotTables, and design PivotCharts.

How to Add Data to Your Excel File and Build a Data Model

You’ve collected the data you’ll need. Now it’s time to import your data sets into Excel and automatically create a Data Model. A Data Model is similar to a relational database and provides the tabular data used in PivotTables and PivotCharts.

Before using any dataset, clean it up. Use Excel’s CLEAN function to get rid of non-printable characters, run a spell check, remove duplicate rows of data, convert numbers and dates to the proper format, and rearrange the data.

To import Excel data into a Power Pivot Data Model:

If you need data for a school assignment, work project, or to follow along with this tutorial, you’ll find awesome public datasets at GitHub.

  • Open a blank worksheet and save the file with a unique name.
  • Select Data, then select Get Data > From File > From Workbook to open the Import Data dialog box.
  • In Excel 2013, select Power Query > Get External Data and choose your data source.
  • Navigate to the folder containing the Excel file, select the file, then select Import to open the Navigator.
  • Select the check box for Select multiple items.
  • Select the tables you want to import.
  • When you import two or more tables, Excel automatically creates the Data Model.
  • Select Load to import the data tables into a data model.
  • To make sure the import was successful and the Data Model was created, go to Data and, in the Data Tools group, select Go to the Power Pivot Window.
  • The Power Pivot Window displays your data in a worksheet format and consists of three main areas: Data Table, Calculation Area, and Data Table Tabs.
  • The tabs at the bottom of the Power Pivot Window correspond to each of the tables that were imported.
  • Close the Power Pivot Window.

Create Relationships Between Tables with Power Pivot Excel

Now that you have a Data Model, it’s time to create relationships between each of the data tables.

Open a blank worksheet and save the file with a unique name.

Select Data, then select Get Data > From File > From Workbook to open the Import Data dialog box.

In Excel 2013, select Power Query > Get External Data and choose your data source.

Navigate to the folder containing the Excel file, select the file, then select Import to open the Navigator.

Select the check box for Select multiple items.

Select the tables you want to import.

When you import two or more tables, Excel automatically creates the Data Model.

Select Load to import the data tables into a data model.

To make sure the import was successful and the Data Model was created, go to Data and, in the Data Tools group, select Go to the Power Pivot Window.

The Power Pivot Window displays your data in a worksheet format and consists of three main areas: Data Table, Calculation Area, and Data Table Tabs.

The tabs at the bottom of the Power Pivot Window correspond to each of the tables that were imported.

Close the Power Pivot Window.

When you want to add new data to the data model, in the Excel window, go to Power Pivot and select Add to Data Model. The data appears as a new tab in the Power Pivot Window.

  • Select Power Pivot, then select Manage Data Model to open the Power Pivot Window.
  • Select Home, then select Diagram View.
  • The imported tables appear as separate boxes in Diagram View. Drag to move the tables to a different location. Drag a corner of a box to resize it.
  • Drag the column heading from one table to the other table or tables that contain the same column heading.
  • Continue to match column headings.
  • Select Home, then select Data View.

How to Create PivotTables 

When you use Power Pivot to create a Data Model, most of the hard work involving PivotTables and PivotCharts has been done for you. The relationships you created between the tables in your dataset are used to add the fields you’ll use to create PivotTables and PivotCharts.

Select Power Pivot, then select Manage Data Model to open the Power Pivot Window.

Select Home, then select Diagram View.

The imported tables appear as separate boxes in Diagram View. Drag to move the tables to a different location. Drag a corner of a box to resize it.

Drag the column heading from one table to the other table or tables that contain the same column heading.

Continue to match column headings.

Select Home, then select Data View.

  • In the Power Pivot Window, select Home, then select PivotTable.
  • In the Create PivotTable dialog box, select New Worksheet, then select OK.
  • In the PivotTable Fields pane, select the fields to add to the PivotTable. In this example, a PivotTable is created that contains a student name and their average grade.
  • To sort the PivotTable data, drag a field to the Filters area. In this example, the Class Name field is added to the Filters area so the list can be filtered to show student’s average grade for a class.
  • To change the calculation method used by a field in the Values area, select the dropdown box next to the field name and select Value Field Settings. In this example, Sum of Grade was changed to Average of Grade.
  • Analyze your data. Experiment with the filters and sort data using the column header dropdown arrows.

Convert a PivotTable into a PivotChart

If you want to visualize your PivotTable data, turn a PivotTable into a PivotChart. 

In the Power Pivot Window, select Home, then select PivotTable.

In the Create PivotTable dialog box, select New Worksheet, then select OK.

In the PivotTable Fields pane, select the fields to add to the PivotTable. In this example, a PivotTable is created that contains a student name and their average grade.

To sort the PivotTable data, drag a field to the Filters area. In this example, the Class Name field is added to the Filters area so the list can be filtered to show student’s average grade for a class.

To change the calculation method used by a field in the Values area, select the dropdown box next to the field name and select Value Field Settings. In this example, Sum of Grade was changed to Average of Grade.

Analyze your data. Experiment with the filters and sort data using the column header dropdown arrows.

  • Select the PivotTable, then go to PivotTable Tools > Analyze.Select Pivot Chart to open the Insert Chart dialog box.Choose a chart, then select OK.

Create PivotCharts

If you’d rather analyze your data in a visual format, create a PivotChart.

  • In the Power Pivot window, select Home, then select the PivotTable dropdown arrow. A list of options appears.
  • Select PivotChart.
  • Choose New Worksheet and select OK. A PivotChart placeholder appears on a new worksheet.
  • Go to PivotChart Tools > Analyze and select Field List to display the PivotChart Fields pane.
  • Drag fields to add to the PivotChart. In this example, a PivotChart is created showing the average grade for classes filtered by semester.
  • Analyze your data. Experiment with the Filters and sort data with the column header dropdown arrows.

In the Power Pivot window, select Home, then select the PivotTable dropdown arrow. A list of options appears.

Select PivotChart.

Choose New Worksheet and select OK. A PivotChart placeholder appears on a new worksheet.

Go to PivotChart Tools > Analyze and select Field List to display the PivotChart Fields pane.

Drag fields to add to the PivotChart. In this example, a PivotChart is created showing the average grade for classes filtered by semester.

Analyze your data. Experiment with the Filters and sort data with the column header dropdown arrows.

Get the Latest Tech News Delivered Every Day