Create a PivotTable or PivotChart Report

Looking for help with Power Pivot in Excel 2013? Go to Power Pivot Help on Office.com.

When you work in an Excel workbook that has PowerPivot for Excel, you can create PivotTables and PivotCharts in two different places: in the PowerPivot window on the Home tab, and in the Excel window on the PowerPivot tab. If you want to use the data in your PowerPivot window to build a PivotTable or chart, you must use one of these options. The PivotTable button that is on the Insert tab in the Excel window can also create PivotTables and PivotCharts, but those PivotTables and PivotCharts cannot use PowerPivot data, only data that is stored in the worksheets in the Excel workbook.

This topic assumes that you are already familiar with using PivotTables and PivotCharts. If not, see the following topics on Microsoft Office Online for an introduction:

PivotTable reports 101

PivotTable and PivotChart reports

Overview of PivotTable and PivotChart reports

When you create PivotTables that include PowerPivot data, you also have access to the following features:

  • The use of a formula language, Data Analysis Expressions (DAX), that provides time intelligence functions and other features. For more information, see Data Analysis Expressions (DAX) Overview.

  • The ability to create relationships between tables, to look up related data, and to filter using relationships. For more information, see Relationships and Lookups in Formulas.

  • The ability to apply filters dynamically based on the current context, or to filter across related tables. For more information, see Filter Data in a Table.

  • The use of enhanced Slicers. You can quickly add multiple PivotTable reports and PivotChart reports at the same time. When you use the PowerPivot Field List to add Slicers, the Slicers will filter all of the objects in the report automatically. For more information, see Filter Data Using Slicers.

To create a PivotTable report or PivotChart report

  1. Click the down arrow under the PivotTable button in one of the following places.

    • In the PowerPivot window, on the Home tab, in the Reports group.

    - or -

    • In the Excel window, on the PowerPivot tab, in the View group.
  2. Select from the list of options. If you want, you can add linked PivotTable and PivotChart reports at the same time.

    Option

    Description

    Single PivotTable

    Creates a blank PivotTable on a new worksheet or a worksheet that you choose.

    Single PivotChart

    Creates a blank PivotChart on a new worksheet or a worksheet that you choose.

    Chart and Table (Horizontal)

    Creates a blank PivotChart and PivotTable on a new worksheet or a worksheet that you choose, and positions them side-by-side.

    The data in the chart and the table are independent of each other. However, Slicers apply to both.

    Chart and Table (Vertical)

    Creates a blank PivotChart and PivotTable on a new worksheet or a worksheet that you choose, and positions them with the chart above the table. You can later change the positions.

    The data in the chart and the table are independent of each other. However, Slicers apply to both.

    Two Charts (Horizontal)

    Creates two blank PivotCharts on a new worksheet or a worksheet that you choose, and positions them side-by-side.

    The charts are independent of each other. However, Slicers apply to both.

    Two Charts (Vertical)

    Creates two blank PivotCharts on a new worksheet or a worksheet that you choose, and positions them with one chart above the other. You can later change the positions.

    The charts are independent of each other. However, Slicers apply to both.

    Four Charts

    Creates four blank PivotCharts on a new worksheet or a worksheet that you choose.

    The charts are independent of each other. However, Slicers apply to all four.

    Flattened PivotTable

    Creates a blank PivotTable. Instead of arranging some data values as column headers and others as row headers, a new column is added for each field that you add, and a Totals row is inserted after each group.

  3. Specify a location by doing one of the following:

    • To place it in a new worksheet starting at cell A1, click New Worksheet.

    • To place it in an existing worksheet, select Existing Worksheet, and then specify the first cell in the range of cells where you want to position it.

  4. Click OK.

    Excel adds the selected report to the specified location and displays the PowerPivot Field List so that you can add fields and customize it.

    For more information about adding fields and measures to a report, see the following topics:

    Create a PivotTable from PowerPivot Data (Tutorial)

    Create a PivotChart from PowerPivot Data (Tutorial)

    Measures in PowerPivot

Note

 If you create a PivotChart, an associated PivotTable is created on a new worksheet. If you delete this PivotTable, the PivotChart becomes a standard chart that you can no longer change.

Show Details

To see the lowest level of details of a PivotTable cell, right-click a PivotTable cell in Excel, and then click Show Details. Or, you can double-click the cell. A new worksheet opens with the underlying data that contribute to the value of the specified cell. This feature is also referred to as Drillthru and Drillthrough.

Moving or Copying Excel Worksheets

Excel 2010 includes a new feature that enables you to move or copy a worksheet to a new workbook or to a different location within the existing workbook. Moving and copying within an existing workbook is fully supported by PowerPivot. However, moving or copying to a new workbook is not supported for worksheets that contain PivotTables or PivotCharts that are based on PowerPivot data. If you require data in a new workbook, we recommend that you save a copy of the original workbook and use that as a starting point for the new workbook. If you no longer require that data in the original workbook, you can delete it.

See Also

Concepts

Create and Change the Field Layout in a PivotTable or PivotChart Report

Filter Data Using Slicers

Create a Measure in a PivotTable or PivotChart

Other Resources

Overview of Reports, Charts, and PivotTables