Contextures

Pivot Chart Source Data

Create a pivot chart with shortcut keys, or create a pivot chart from the source data, before making a pivot table. See how to change pivot chart source data, to connect to a different pivot table.

Create a Pivot Chart

To quickly create a pivot chart, you can use keyboard shortcuts, as shown in this short video tutorial. Written instructions are below the video.

Create a Pivot Chart

To quickly create a pivot chart, you can use one of the following shortcuts.

  • Select any cell in the pivot table
  • Press F11, to create a chart on a new chart sheet
  • OR, press Alt + F1, to create an embedded chart on the active sheet

The pivot chart will be in the default chart type. You can change to a different chart type, and adjust the chart's formatting. The chart below is a stacked bar chart, with the axis items in reverse order, and the axis crossing at the maximum value.

pivot chart formatted

Create a Pivot Chart Before Pivot Table

In Excel 2013 and later, you can create a pivot chart directly from the source data, without creating a pivot table first. Watch this short video, to see how it works, and the written instructions are below the video.

Create a Pivot Chart from the Data

Follow these steps to create a pivot chart in Excel 2013, before making a pivot table.

  • Select any cell in the source data table
  • On the Ribbon, click the Insert tab, and click Recommended Charts
  • Click on one of the charts samples that has a pivot table symbol at the top right

recommended charts pivot charts

The pivot chart will be inserted on a new worksheet, and its connected pivot table will be created on the same sheet.

pivot chart and pivot table

Change the Pivot Chart Source Data

This is a useful technique if you've spent a long time setting up a pivot chart, with specific formatting, and you want to use a copy of that chart with a different pivot table.

Unlike a normal chart, you can't change the source data in a pivot chart. To see the data source for the selected chart:

  1. On the Excel Ribbon, click the Design tab, under PivotChart Tools
  2. Click Select Data, to open the Select Data Source window.

You can see the address of the chart's data range, but you can't make any changes to the range.

pivot chart data source

Watch the video below, to see a workaround for this problem. The written instructions are below the video.

Unlink the Pivot Chart

Although you can't directly change the pivot chart source, you can follow a few easy steps, to make the change.

We'll make a copy of the original chart, and then unlink it from the pivot table, to create a static chart:

  1. Right-click the pivot chart's Chart area or border, and in the popup menu, click Copy.
  2. On the Ribbon, click the File tab, and click New, then create a new blank workbook.
  3. On the Ribbon, click the Home tab, and click Paste.

If you click on a series in the pasted chart, you'll see the labels and values, instead of references to the original pivot table. The Ribbon tab says "Chart Tools" now, instead of "PivotChart Tools".

static chart data source

Link to a Different Pivot Table

Next, you can link the chart to a different pivot table.

  1. Right-click the pivot chart's Chart area or border, and then in the popup menu, click Cut.
  2. Switch to the workbook with the pivot table that you want to use as the chart's data source.
  3. Paste the chart back into the workbook, and delete the old copy of the chart.
  4. With the new chart selected, on the Excel Ribbon, click the Design tab, under Chart Tools
  5. Click Select Data, to open the Select Data Source window
  6. Click any cell in the pivot table, and click OK

static chart change data source

The static chart changes back to a pivot chart, and if you open the Select Data Source window again, you'll see the reference to the new pivot table.

pivot chart new data source

Get the Workbook

Get the Excel workbook to try techniques shown on this page. The zipped Pivot Chart Source Data workbook is in xlsx format, and does not contain macros.

Related Tutorials

FAQs - Pivot Tables

Pivot Table Introduction

Pivot Chart Compare Years

Combination Column Line

Pivot Chart Number Formatting

Pivot Chart Macros

Last updated: July 11, 2021 11:03 AM