Share via

  • Article

Create a PivotChart from PowerPivot Data (Tutorial)

Once you've added data to your PowerPivot workbook, PivotCharts help you efficiently summarize, analyze, explore, and present your data. PivotCharts provide an interactive graphical representation of your data and help you see comparisons, patterns, and trends.

This tutorial 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 and PivotChart reports

Overview of PivotTable and PivotChart reports

Prerequisites

This topic is part of a PowerPivot for Excel tutorial, which should be completed in order. For information about prerequisites and the first step in the tutorial, see PowerPivot for Excel Tutorial Introduction.

Add a PivotChart to Your Analysis

You'll continue using the PowerPivot workbook you created in the previous tasks. It already has the data imported, relationships created, and PivotTables added.

Important

Always create PivotCharts from the PowerPivot window or the PowerPivot tab in the Excel window. There is also a PivotChart button on the Insert tab in the Excel window, but standard Excel PivotCharts cannot access your PowerPivot data.

To Add a PivotChart to Your Analysis

  1. Start on the worksheet that contains your Sales by Channel PivotTable.

  2. On the PowerPivot tab in Excel, click the arrow below PivotTable and select PivotChart.

  3. Select Existing Worksheet and click OK.

    Excel adds an empty PivotChart to the same worksheet that contains your Sales by Channel PivotTable.

  4. Select the empty PivotChart and in the PivotChart Tools tab, select Design and then Change Chart Type.

  5. Select the first chart in the Line group and then click OK.

  6. In the FactSales table, select the SalesAmount field. Ensure this field displays in the Values window of the Field List.

  7. In the DimChannel table, select the ChannelName field. In the PivotTable Field List, move this field from the Axis Fields box into the Legend Fields box.

  8. In the DimDate table, select CalendarQuarter. In the PivotTable Field List, move this field from the Values box into the Axis Fields box.

    The PivotChart and PivotTable now display the same data – in very different layouts.

  9. Format the display of the data to make it easier to read and compare. Right-click the axis numbers and select Format Axis.

    1. Click Number and in the Category list, select Currency.

    2. Set Decimal places to 0, and then click Close.

    3. Right-click the Sum of SalesAmount axis label and select Value Field Settings.

    4. Change Custom Name to Sales by Channel and then click OK.

To Add Another PivotChart to Your Analysis

  1. Start on the worksheet that contains your Profit by Category PivotTable.

  2. On the PowerPivot tab in Excel, click the arrow below PivotTable and select PivotChart.

  3. Select Existing Worksheet and click OK.

    Excel adds an empty PivotChart to the same worksheet that contains your Profit by Category PivotTable.

  4. In the FactSales table, select the TotalProfit field. Ensure this field displays in the Values window of the Field List.

  5. In the ProductCategory table, select the ProductCategoryName field. Ensure this field displays in the Axis Fields window of the Field List.

  6. In the PivotChart Tools tab, select Design and click Change Chart Type.

  7. Scroll down and select the first Pie chart type and click OK.

  8. In the Chart Styles group, select the 3-dimensional style with a black background and click OK.

  9. On your pie chart, select the title (Total) and change it to Profit % by Category.

  10. Next, you add and format data labels.

    1. On your pie chart, right-click and select Add Data Labels.

    2. Right-click again and select Format Data Labels.

    3. Highlight Label Options, then select the Percentage checkbox and deselect Value.

    4. Click Close. Resize the chart to make sure that all product categories are displayed.

    5. Resize the chart to make sure that all product categories are displayed.

  11. Save your PowerPivot workbook.

Note

This PivotChart displays percentage values for each product category. Because FactSales and other tables are filtered, the percentages are percentages of total sales in six of the eight categories of Contoso products.

These are simple analyses of your data. To dig deeper, you will add Slicers.

Delete a PivotChart

Keep the PivotCharts in the workbook in order to complete the tutorial, but if you want to delete a chart at some point, follow these steps.

To Delete a PivotChart

  1. To delete a PivotChart, click inside the PivotChart.

  2. Right-click and select Cut.

    Deleting the PivotChart does not delete any associated PivotTable reports or data.

Next Step

To continue this tutorial, go to the next topic: Add Slicers to PivotTables (Tutorial).