Home > Pivot > Layout > Pivot Chart Pivot Chart Source Data How to create a pivot chart with shortcut keys, or create a pivot chart from source data, before making a pivot table. See how to change pivot chart source data, to connect to a different pivot table. Video and written steps.

Video: Create a Pivot Chart

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

The written instructions are below the video.

Create Pivot Chart - Shortcuts

To quickly create a pivot chart, you can use one of the following keyboard 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 new pivot chart will be automatically created using the Excel workbook's default chart type.

Change Pivot Chart Appearance

After the pivot chart is created, you can change it to a different chart type, and adjust the chart's formatting.

For example, in the chart below:

• chart type was changed to stacked bar chart
• axis items were changed to show in reverse order
• axis crossing was set to cross at the maximum value

Tip: For help with numbers in the pivot chart, go to the Pivot Chart Number Formatting page

Video: Create Pivot Chart from Data (2013)

In Excel 2013 , you can create a pivot chart directly from the source data, without creating a pivot table first.

NOTE: For Excel 365 instructions, go to the next section.

Watch this short video, to see how it works, and the written steps are below the video.

Create Pivot Chart from Data (2013)

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

NOTE: For Excel 365 instructions, go to the next section.

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

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

Create Pivot Chart from Data (365)

Follow these steps to create a pivot chart in Excel 365, from the sourcedata, before making a pivot table.

• Select any cell in the source data table
• On the Ribbon, click the Insert tab
• Next, click the arrow below the Pivot Chart command, to see the drop-down menu
• Click on Pivot Chart
• In the Create PivotChart dialog box, the table range should be entered automatically.
  • If not, click in the Table/Range box, then select the source data table on the worksheet
• Click the OK button, to create the pivot chart

Add Fields to Pivot Chart

When you click OK,

• the new pivot chart is inserted on a new worksheet.
• the pivot chart's connected pivot table is automatically created on the same sheet.
• both the pivot chart and pivot table are empty

Follow these steps to add fields to the pivot chart and pivot table

• If it is not already selected, click on the empty pivot chart to select it
• In the PivotChart Fields pane, check the fields that you want in the pivot chart
  • OR, drag each field to one of the Area boxes
• The selected fields are automatically added to the pivot table that is connected to the pivot chart

In this example,

• Region was dragged to the Legend area
• Day was dragged to the Axis area
• Amt was checked in the PivotChart field list
  • Excel added it to the Values area by default, because it is a numeric field

Check Pivot Chart Source Data

To see which pivot table a pivot chart is connected to, you can follow these steps to see the data source information:

1. On the worksheet, click on a pivot chart, to select it
2. On the Excel Ribbon, click the Design tab, under PivotChart Tools
3. Click Select Data, to open the Select Data Source dialog box.
4. At the top of the dialog box, you can see the address of the chart's data range, in light-coloured font
• NOTE: You can't make any changes to the data source range in this dialog box.
• If you click on the Chart Data Range box, nothing happens
5. Close the Select Data Source window, to return to the worksheet.

Video: Link Pivot Chart to Different Pivot Table

In an Excel file, you might have a couple of pivot tables on different worksheets.

If you spend time creating a pivot chart for one of those pivot tables, you might want an identical pivot chart for the pivot table. That would save you lots of time!

Unfortunately, there's no built-in way to make a copy of a pivot chart, and link it to a different pivot table. Unlike a normal Excel chart, you cannot change data source in a pivot chart.

However, if you watch the video below, you'll see a workaround for this problem. The written instructions are below the video, showing how to copy a pivot chart, and connect it to a different pivot table.

Link Pivot Chart to Different Pivot Table

The written steps below show how to copy a pivot chart, and connect it to a different pivot table. There are just a few easy steps!

1) Copy and Paste Pivot Chart

The first step is to copy the pivot chart, and paste it into a different workbook, temporarily.

This will unlink the pivot chart from its original PivotTable:

1. Right-click the pivot chart's Chart area or border
2. In the popup menu, click Copy.
3. On the Excel Ribbon, click the File tab
4. Click New, then create a new blank workbook.
5. On the Ribbon, click the Home tab, and click Paste (or use the Ctrl + V shortcut)

Check the Unlinked Chart

By copying the pivot chart, and pasting it into a new workbook, you unlinked it from the original pivot table.

To check the chart, and see that it is unlinked, follow these steps:

• Click on a series in the pasted chart
• Look in the Excel formula bar
• Instead of references to the original pivot table, there are hard-coded values

Also, when the pasted chart is selected, the Excel Ribbon shows "Chart Tools" now, instead of "PivotChart Tools" .

• Or, in some versions of Excel, the Ribbon shows "Chart Design" and "Format"
• instead of "PivotChart Analyze" tab, "Design" and "Format"

2) Link to Different Pivot Table

Next, to link the pivot chart to a different pivot table, you'll move the new pivot chart, and set its source data.

You will have 2 workbooks open:

1. New workbook, where the pivot chart was pasted
2. Original workbook, with a pivot table you want to connect the new pivot chart to

A) First, to move the pivot chart back to the original workbook, follow these steps:

1. In the new workbook, right-click the pivot chart's Chart area or border
2. Then, in the popup menu, click Cut.
3. Switch to the original workbook, where the target pivot table is
4. Select the worksheet where you want to put the pivot chart
5. Paste the pivot chart onto the worksheet
• Optional: Delete the original pivot chart, if you no longer need it

B) To connect the pivot chart to the target pivot table, follow these steps:

1. Click on the new pivot chart, to select it
2. On the Excel Ribbon, click the Design tab, under Chart Tools
3. Click Select Data, to open the Select Data Source window
4. Click any cell in the pivot table, then click the OK button

Automatically Connected

The unlinked, static chart automatically changes back to a pivot chart, connected to the pivot table that you set as its new source.

If you open the Select Data Source window again, you'll see the reference to the new pivot table.

Download the Workbook

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

Pivot Chart Macros

An Excel pivot chart layout is tied to the layout of the pivot table it's connected to. If you change the pivot chart layout, the pivot table will also change, and vice versa.

Before you make changes, you can use a macro to list all fields currently in the pivot chart layout. To get the VBA code, and a sample workbook with the code module, go to the Pivot Chart Macros page.

Related Tutorials

FAQs - Pivot Tables

Pivot Table Introduction

Pivot Chart Compare Years

Combination Column Line

Pivot Chart Number Formatting

Pivot Chart Macros