Create a PivotChart in Excel 2016 for Windows

It can be hard to see the big picture when you have data in a huge PivotTable or when you have a lot of complex worksheet data that includes text and numbers with column headings, like this:

Complex worksheet data

A PivotChart can help you make sense of this data. While a PivotChart shows data series, categories, and chart axes the same way a standard chart does, it also gives you interactive filtering and zoom controls right on the chart so you can quickly analyze a subset of your data.

PivotChart with filter buttons and zoom controls
PivotChart with filter buttons (Country, Salesperson) and zoom buttons

For worksheet data, you can create a PivotChart without creating a PivotTable first. You can even create a PivotChart that is recommended for your data. Excel will then automatically create a coupled PivotTable. Here’s how:

  1. Click anywhere in the data.

  2. On the Insert tab, in the Charts group, pick Recommended Charts.

    Recommended Charts button on the Insert tab

  3. On the Recommended Charts tab, pick any chart with the PivotChart icon PivotChart indicator button in the top corner. A preview of your PivotChart appears in the Preview pane.

    Insert Chart dialog box showing recommended PivotCharts

  4. Once you find the PivotChart you like, click OK.

    Tip:  If you don’t find a PivotChart you like, click PivotChart on the Insert tab instead of Recommended Charts.

  5. In the PivotChart that appears, click any of the buttons with field names, and then pick the sort or filtering options you want.

    Filter and sort options for a PivotChart

    To zoom in or out on the PivotChart, click the plus or minus icons.

Tip:  To remove a PivotChart you no longer want, select it, and then press Delete.

Other ways to create a PivotChart

If you already have a PivotTable, you can base a PivotChart on that PivotTable. Or if you’ve connected to an external Online Analytical Processing (OLAP) or a Data Model data source, you can create a standalone, “de-coupled” PivotChart, without creating a PivotTable at all.

Create a PivotChart for an existing PivotTable

Connect to external data to create a PivotChart

Use an existing external data connection to create a PivotChart

Create a PivotChart for an existing PivotTable

  1. Click anywhere in the PivotTable to show the PivotTable Tools on the ribbon.

    PivotTable Tools ribbon with Analyze and Design tabs
  2. Click Analyze > PivotChart.

    PivotChart button on the Analyze tab

  3. In the Insert Chart box, click the chart type and chart subtype you want. You can use any chart type except an XY (scatter), bubble, or stock chart.

    Insert Chart dialog box for PivotCharts

    To learn more about the chart types, see Available chart types.

  4. Click OK.

  5. In the PivotChart that appears, click any of the buttons with field names, and then pick the sort or filtering options you want.

    Filter and sort options for a PivotChart

    To zoom in or out on the PivotChart, click the plus or minus icons.

Connect to external data to create a PivotChart

  1. Click Data > From Other Sources, and then pick the data source you want. For example, pick From Analysis Services to connect to an Online Analytical Processing (OLAP) cube file.

    From Other Sources button on the Data tab

  2. Follow the steps in the Data Connection Wizard, and click Finish.

  3. In the Import Data box, pick PivotChart, and the location where you want to put the data, and then click OK.

    An empty PivotChart appears and the Field List is shown so you can add or rearrange fields in your PivotChart.

  4. In the Field List, pick the fields you want to show in the PivotChart.

    Field List showing a field section and an areas section

  5. After you create a PivotChart, you can customize it, much like you’d do with any standard charts. When you select the PivotChart, two buttons appear next to the chart so you can quickly add or change chart elements such as titles or data labels, or change the chart style and colors of your PivotChart the same way you would in a standard chart.

    PivotChart with filter buttons, zoom icons, and chart element buttons in Excel 2016 for Windows

  6. The PivotChart Tools are shown on the ribbon. On the Analyze, Design, and Format tabs, you can pick options to work with or customize your PivotChart.

    PivotChart Tools on the ribbon

Use an existing external data connection to create a PivotChart

  1. Click Insert > PivotChart.

    PivotChart button on the Insert tab

  2. In the Create a PivotChart box, click Use an external data source, and then click Choose Connection.

  3. In the Existing Connections box, on the Connections tab, double-click the connection you want.

  4. Click OK.

    An empty PivotChart appears and the Field List is shown so you can add or rearrange fields in your PivotChart.

  5. In the Field List, pick the fields you want to show in the PivotChart.

    Field List showing a field section and an areas section

  6. After you create a PivotChart, you can customize it, much like you’d do with any standard charts. When you select the PivotChart, two buttons appear next to the chart so you can quickly add or change chart elements such as titles or data labels, or change the chart style and colors of your PivotChart the same way you would in a standard chart.

    PivotChart with filter buttons, zoom icons, and chart element buttons in Excel 2016 for Windows

  7. The PivotChart Tools are shown on the ribbon. On the Analyze, Design, and Format tabs, you can pick options to work with or customize your PivotChart.

    PivotChart Tools on the ribbon

See Also

Create a PivotTable in Excel 2016 to analyze worksheet data

Sort data in a PivotTable

Filter data in a PivotTable in Excel 2016

Update (refresh) data in a PivotTable

Share Facebook Facebook Twitter Twitter Email Email

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!

×