Create a PivotChart

Sometimes it can be hard to see the big picture when you have raw data that hasn’t been summarized, like the example below. Many times, your first step will be to create a PivotTable to summarize and analyze your data in a structured format, which is well and good, but not everyone can look at numbers and quickly see what's going on. PivotCharts are a great way to add data visualizations to your data.

Household expense data

Corresponding PivotChart

Sample household expense data to create a PivotTable with Months, Categories and Amounts

Example of an Excel PivotChart

If you just have plain worksheet data and no existing PivotTable, you can choose a PivotChart that is recommended for your data, and 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 a PivotChart you like, click OK. Excel will create a new worksheet for you with both a PivotTable, and a PivotChart.

  5. You can arrange the PivotChart to be next to the PivotTable to view both the pivoted data and the chart, or you can place the PivotChart on top of the data if you only want to see the Chart.

  6. Next you can format your PivotChart.

If you tried the Recommended Charts option, and didn't find one you liked, you can try on your own.

  1. Select any cell within your worksheet data, and go to Insert > Pivot Chart > PivotChart.

  2. Excel will display a dialog asking where you want to place your PivotChart. It's generally a good idea to choose the New Worksheet option, so you can experiment with making your chart look the way you want without affecting anything else on the worksheet.

    Excel Insert > PivotChart options
  3. Press OK, and Excel will add a new worksheet with a blank PivotTable and PivotChart.

    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. Next you can format your PivotChart.

If you’ve already created a PivotTable, and set it up the way you want it, then this method will create the most complete PivotChart for you.

  1. Select any cell within your PivotTable range.

  2. Go to PivotTable Tools > Analyze > PivotChart .

  3. Select the chart type you want, and press OK.

    Excel will place a new PivotChart on the same sheet with the PivotTable you based it on. For this example, we chose a standard Column chart. If you're not sure which chart type you want, you can select them one at a time, and Excel will live preview them for you in the Insert chart pane.

    PivotChart example with Live Preview of chart type, and a Column chart selected

    Certain charts don’t support PivotTable data, so if you select one of those, Excel will let you know.

  4. Next you can format your 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 dialog 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. Next you can format your PivotChart.

  1. Click Insert > PivotChart.

    PivotChart button on the Insert tab

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

  3. In the Existing Connections dialog 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

After you create a PivotChart, you can customize it, much like you’d do with any standard chart. When you select the PivotChart:

  1. 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 labels pointing to Field Buttons and Chart Tools

  2. Field Buttons - Excel automatically displays PivotTable Fields as buttons on the PivotChart. These allow you to filter a single PivotChart to display just the details you want to see. You can also use PivotTable Slicers to filter multiple PivotTables and PivotCharts at once instead of the Field Buttons. To remove them, click on either the PivotTable or PivotChart, then in the Analyze tab on the Ribbon, uncheck the Field List button.

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

  4. A PivotChart doesn't need to be on the same worksheet as its underlying PivotTable, so when your PivotChart looks the way you want, you can cut and paste it to a different location.

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

To create a PivotChart on the Mac, you need to create a PivotTable first, and then insert a chart. Once that is done, the chart will behave like a PivotChart if you change the fields in the PivotTable Fields list. 

  1. Create a PivotTable if you don't have one already. 

  2. Select any cell within the PivotTable.

  3. On the Insert tab, click a button to insert either a column, line, pie, or radar chart. Please note that other types of charts do not work with PivotTables at this time. For example, treemap charts, statistical charts, and combo charts do not work with PivotTables yet.

  4. After you insert a column, line, pie, or radar chart, you can pivot it by changing or moving fields using the PivotTable Fields list.

  5. You can also filter data in a PivotTable, and user Slicers. When you do that, the chart will also be filtered.

Need more help?

You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.

Related Topics

Create a PivotTable
Use the Field List to arrange fields in a PivotTable
Use slicers to filter data
Create a PivotTable timeline to filter dates

Connect with an expert
Contact us
Expand your skills
Explore training

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.

×