Power Pivot For Excel: What It Is and How to Use It

Add lookup tables to your data sets with this resourceful add-on

Business people working with charts and graphs on glass wall

 Martin Barraud / Getty Images

PRODUCT DISCLOSURE $

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.

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

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.

  1. Open Excel.

  2. Select File > Options.

  3. Select Add-Ins.

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

    COM Add-ins pup-up menu item in Excel
  5. Select Go.

  6. Select Microsoft Power Pivot for Excel.

    Microsoft Power Pivot for Excel option
  7. 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.

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.

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.

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.

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.

To import Excel data into a Power Pivot Data Model:

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

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

    From Workbook submenu in Get Data button menu

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

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

  4. Select the check box for Select multiple items.

    Select multiple items checkbox in Excel
  5. Select the tables you want to import.

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

  6. Select Load to import the data tables into a Data Model.

  7. 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.

    Power Pivot window button
  8. 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.

    A screenshot showing the data model in the PowerPivot window
  9. The tabs at the bottom of the Power Pivot Window correspond to each of the tables that were imported.

  10. 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.

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.

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

    Manage button in Power Pivot tab
  2. Select Home, then select Diagram View.

    Diagram View button in View section
  3. 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.

    A screenshot showing Diagram View in Excel PowerPivot
  4. Drag the column heading from one table to the other table or tables that contain the same column heading.

    A screenshot showing how to create relationships between tables in Excel PowerPivot
  5. Continue to match column headings.

    A screenshot showing an example of relationships in PowerPivot for Excel add-in
  6. 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.

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

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

    Ok button in Create PivotTable dialog
  3. 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.

    A screenshot showing how to select data to use in a PivotTable in PowerPivot Excel
  4. 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.

    A screenshot showing how to add a value to a PivotTable in Excel PowerPivot

    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.

  5. 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. 

A screenshot showing a PivotTable that has been turned into a PivotChart in PowerPivot for Excel
  1. Select the PivotTable, then go to PivotTable Tools Analyze.
  2. Select Pivot Chart to open the Insert Chart dialog box.
  3. Choose a chart, then select OK.

Create PivotCharts

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

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

  2. Select PivotChart.

    PivotChart from PivotTable button
  3. Choose New Worksheet and select OK. A PivotChart placeholder appears on a new worksheet.

    OK button in Create PivotChart dialog
  4. Go to PivotChart Tools Analyze and select Field List to display the PivotChart Fields pane.

    Field List button in Show/Hide section of Pivot Chart
  5. Drag fields to add to the PivotChart. In this example, a PivotChart is created showing the average grade for classes filtered by semester.

    A screenshot showing how to add fields to a PivotChart in Excel PowerPivot
  6. Analyze your data. Experiment with the Filters and sort data with the column header dropdown arrows.