Contextures

Pivot Chart Source Data

After you create a pivot table, you can add a pivot chart that is based on the pivot table. We'll see how to create a pivot chart, and then connect that pivot chart to a different pivot table. 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.



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.

  • 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

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 

Related Tutorials

FAQs - Pivot Tables

Pivot Table Introduction

Pivot Chart Source Data

Combination Column Line

Pivot Chart Number Formatting

Pivot Chart VBA

Pivot Chart Field List Macro

30 Excel Functions in 30 Days

 

 

 

 

 

 

pivot power premium

 

 

 

pivot xtreme

 

pivot power premium

 


Last updated: August 14, 2016 3:31 PM