Working with Pivot Charts in Excel

About Pivot Charts

If you select a pivot table and insert a chart, Excel inserts a pivot chart. A pivot chart is a special Excel chart, with some strengths and some limitations. I used to avoid pivot charts because of these limitations, which included not being able to hide the field buttons and not being able to resize the plot area or move axis and chart titles. But Microsoft has kept improving them, and now the few remaining limitations seem pretty reasonable given the power and constraints of pivot tables themselves.

  1. A pivot chart is linked to its parent pivot table. Changes to the pivot table are reflected in the pivot chart, and vice versa. If the pivot table changes size, the pivot chart changes the number of its plotted series, and changes the lengths of these series, to accommodate the updated pivot table size.
  2. The pivot chart has optional field buttons that allow the same filtering capabilities directly in the chart that are available to the pivot table.
  3. All data in the pivot table is plotted in the pivot chart, except for subtotals and grand totals. No data from outside the pivot table is allowed in the pivot chart.
  4. Pivot charts are plotted with category labels and series values in columns only. Pivot charts cannot be plotted by row.
  5. Not all chart types are available in pivot charts. Line, column, area, bar, and pie charts are available, for example, but XY scatter charts and bubble charts are not.

Often, pivot charts are exactly what is needed. Sometimes, however, a regular chart must be used. For example, you might want a scatter plot of the pivot table’s data. Or you might want to add data from another source.

My colleague Debra Dalgleish has a brief Pivot Chart Tutorial on her web site, as well as a FAQ page on Pivot Tables and Pivot Charts. Debra also wrote Using Pivot Tables in Microsoft Excel on this blog.

This article will answer the following common questions about pivot charts in Excel.

  • How do I disconnect a pivot chart from its pivot table?
  • How do I copy a pivot chart and link it to another pivot table?
  • How do I convert a pivot chart into a regular chart and preserve its links to the pivot table?

The last question could be answered by Making Regular Charts from Pivot Tables. But the question is often asked by someone who has spent significant time formatting his pivot chart, and doesn’t want to lose this formatting or be forced to recreate it.

We’ll start by reviewing regular charts and how their data is handled. Then we’ll examine differences between regular charts and pivot charts. Finally we’ll investigate answers to the questions above.

Regular Charts

The screenshot below shows a table with some simple data located in B2:D14. The data is rearranged in F3:I7 (a pivot table could have done this). Below that is a regular Excel chart which plots the data from this second range.

Chart Source Data Highlighting

When you select a chart that has a well-behaved* source data range, the chart’s data range is highlighted in the worksheet. The highlighting for our simple chart in Excel 2013 and 2016 is shown below: the X values (category labels) are purple, the Y values are blue, and the series names are red. You can click and drag the highlighted borders to move the chart data, and you can click and drag on the highlighted corners to resize the chart data.

*Well-behaved means that the Y values of the series are in adjacent rows or columns, in order. Y values and X values (if present) must be aligned: in the chart below, the X values and all sets of Y values all begin on the same row and all end on the same other row, and the series names are aligned with the Y values.

When you select a plotted series, the data for that series is highlighted in the worksheet. The highlighting for the first series of our simple chart in Excel 2013 and 2016 is shown below: the X values (category labels) are purple, the Y values are blue, and the series names are red. You can click and drag the highlighted borders to move the chart data, and you can click and drag on the highlighted corners to resize the chart data. Note that our series is plotted by columns.

Chart Series Data Highlighting

When a series is selected, you can also see the corresponding SERIES formula in the formula bar. This series formula has the following components:

  • Series Name: Pivot!$G$3
  • Category Labels (X Values): Pivot!$F$4:$F$7
  • Y Values: Pivot!$G$4:$G$7
  • Plot Order: 1

You can edit this formula in place to adjust the chart data.

Another way to adjust a chart’s data is the Select Data Source dialog. To open this dialog, click the Chart Tools > Design tab > Select Data button, or tight-click on the chart and click Select Data from the pop-up menu.

Select Source Data Dialog

Here is the Select Source Data dialog for our regular chart. The box at the top shows the entire source data, which was highlighted when we selected the entire chart. You can edit this as text, or select another source data range in a worksheet. Caveat: if your selection in the Chart data range box intersects a pivot table, your chart will be converted into a pivot chart based on that pivot table.

Click the Edit button under Axis Labels in the bottom right part of the dialog, and the Axis Labels dialog appears, showing the range containing the axis labels. You can edit this as text, or select another axis label range.

Select a series in the bottom right part of the dialog and click the corresponding Edit button, and the Edit Series dialog appears, showing the range containing the Y values. You can edit this as text, or select another range of values.

If you click the Switch Row/Column button, the same data is used as the source data, but its orientation is switched. The category axis labels become the series names, and the series names become the axis labels. Note that our chart now has four series with three points each (and three axis labels), and the red and purple highlighted regions have changed places.

When we select the first series, we see that it is now aligned in rows.

If your chart’s source data intersects a pivot table, clicking Switch Row/Column will convert your chart into a pivot chart based on that pivot table.

Disconnecting From Worksheet Data

Here’s a little-known debugging trick for Excel formulas. You can use the F9 function key or the Ctrl+= shortcut (hold the Ctrl key while you press the = key) to evaluate part or all of a formula in the formula bar. Fortunately this works for a SERIES formula.

If you click in the formula bar and click F9 or Ctrl+=, every section of the formula is evaluated (and the links are disconnected), as shown in this before-and-after screenshot.

Click Esc to restore the original formula, or Enter to keep the evaluated formula.

If you select just part of a formula and then click F9 or Ctrl+=, just the selected part of the formula is evaluated. In this screenshot, the Y value range of G4:G7 is converted to the array {1;3;2;4}.

To unlink a regular chart from its worksheet data, select each series, click in the formula bar, and press the F9 key.

Copying the Chart

You can copy a chart and paste it anywhere in the same workbook, even onto a different worksheet, and nothing happens to the chart. It shows the same data that it was linked to (on the original sheet), and the series formulas still link back to the original data. This is familiar, expected behavior, although when you want to link the chart to the data on its new parent worksheet, it’s not so welcome. But see Make a Copied Chart Link to New Data if that’s what you need to do.

When you copy a regular chart to a new workbook, it still points back to the original data, which in the SERIES formula is referenced to the original workbook as well as the original worksheet, as “[Pivot.xlsx]Pivot”. So the regular chart behaves exactly as expected.

Pivot Charts

The screenshot below shows a table with the same simple data located in B2:D14. A pivot table in F2:I7 has rearranged the data. Below the pivot table is an Excel pivot chart which plots the data from the pivot table. Note the field buttons in the pivot chart, corresponding to the controls in the pivot table.

We can hide the field buttons (Pivot Chart Tools > Analyze ribbon tab > Field Buttons) and the chart will look just like our regular chart. But it still has the capabilities and limitations of a pivot chart.

Chart Source Data Highlighting

When the pivot chart is selected, no chart data highlights appear in the worksheet.

Chart Series Data Highlighting

When a series is selected in the pivot chart, no series data highlights appear in the worksheet. The SERIES formula appears in the formula bar, but you cannot edit the series data by editing the series formula. You can only change the series plot order by changing the last parameter in the series formula.

Select Source Data Dialog

Here is the Select Source Data dialog for our pivot chart. The box at the top shows that the source data is our pivot table; this cannot be changed. The axis labels cannot be edited, nor can the series values be edited.

If you click the Switch Row/Column button, the chart changes its appearance to match how our regular chart changed: three series of four categories becomes four series of three categories. But the chart’s data orientation didn’t change, because pivot charts can only plot columns of data. Instead, Excel switched the fields in the rows area of the pivot table with those in the columns area. The X and Series field buttons in the chart have changed places as well.

The series formula shows that the first series of our pivot chart is still plotted by column, with the category labels in column F and Y values in column G.

Disconnecting From Worksheet Data

Excel does not let you evaluate part or all of a pivot chart’s SERIES formula using the F9 or Ctrl+= trick, so you can’t use it to disconnect the pivot chart from its pivot table.

Copying the Chart

You can copy a pivot chart and paste it anywhere in the same workbook, even onto a different worksheet, and nothing happens to the chart. It shows the same data that it was linked to (in the original pivot tables), and the series formulas still link back to this pivot table.

Answering Those Questions

How do I disconnect a pivot chart from its pivot table?

Interesting things happen when you copy a pivot chart to a different workbook. The first thing you may notice is that the field buttons have disappeared, because the pivot chart has been converted to a regular chart. The second thing you’ll notice, if you check out the SERIES formula, is that the links to worksheet ranges have been changed into literal arrays of strings and numbers, as if we used our F9 (Ctrl+=) trick to evaluate the formula.

There’s the answer to our first question, how to unlink a pivot chart from its pivot table. Simply copy the pivot chart to a different workbook. Once the links are broken, you can copy it anywhere, even into the original workbook, and it will remain disconnected from the pivot table.

How do I copy a pivot chart and link it to another pivot table?

In the screenshot below, I’ve copied my unlinked chart and pasted it into the original workbook, in a different worksheet with a different Table of data and a different Pivot Table. As noted above, it’s still disconnected.

Here is the Select Source Data dialog for our unlinked chart. The Chart Data Range box at the top is empty, because the chart’s data is hard-coded into the chart’s SERIES formulas. You can click in the box and select a data range from the worksheet.


If you select a cell or range that overlaps with a pivot table and click OK, the chart will become a pivot chart and use the data from the selected pivot table. In the screenshot below the chart now has field buttons, so we know it has been converted into a pivot chart. The SERIES formula shows links to the data in the pivot table.

There’s the answer to our second question, how to copy a pivot chart but link it to a new pivot table. Copy the pivot chart to a different workbook to disconnect it from the first pivot table, then copy the chart to the sheet with the second pivot table, then use the Select Data dialog and select the new pivot table in the Chart Data Range box.

How do I convert a pivot chart into a regular chart and preserve its links to the pivot table?

If we avoid the Chart Data Range box, we can still use the Select Data Source dialog to reconstruct links to the pivot table data. This is essentially the technique in Making Regular Charts from Pivot Tables, but we’re using the pivot chart which may have had custom formats applied.

Under Horizontal (Category) Axis Labels, click Edit, and the Axis Labels mini-dialog will appear, showing the literal array of labels.

You can clear the box and select the axis label range from the worksheet using your mouse. Then click OK.

Now under Legend Entries (Series), select the first series from list, and click Edit. The Edit Series mini-dialog appears with the series name as a string, and the series values as a literal array of numbers.

You can clear each box and select the cell containing the series name and the column of cells containing the series values. Click OK, then repeat for the rest of the series in the chart.

The Select Data Source dialog now looks like this, with the Chart Data Range box displaying the range containing all of the pivot table data. Don’t click in this box, and don’t click Switch Row/Column, or your chart will become a pivot chart.

Click OK, and notice how the chart now plots the pivot table data. The data is highlighted in the worksheet, and the chart has no field buttons, because it remains a regular chart.

Selecting a single series shows the data is plotted by column, but again, the series highlights verify that the chart is not a pivot chart.

And this is the answer to our last question, how to convert our pivot chart to a regular chart but maintain links to the pivot table’s data. Actually, I’ve linked it here to a new pivot table, but I could link it to the original pivot table in the same way. Strictly speaking, this approach didn’t actually maintain the links, as we had to reconstruct them. There is no way to maintain the links while converting the pivot chart into a regular chart.


Peltier Tech Charts for Excel

Pivot Chart Formatting Changes When Filtered

This is a guest post written by my colleague Jon Acampora.

The Problem

When you apply a filter or slicer to a PivotChart the custom formatting can change with each change in the filter.  This can be very frustrating and require you to apply the custom formatting to each possible view or slice of the data.

The screencast below shows this problem.  I customized the colors of the lines to green and grey for the Year 2011, but the line colors change back to the default colors when I filter the data for 2012.  This happens when the data is filtered by a slicer or manually filtered it in the PivotTable.

Pivot Chart Formatting Changes by Filter Slicer Excel

This post will explain why this happens and present an alternative solution that will save you time when creating PivotCharts with customized formatting.  VBA or macros will NOT be needed for this to work, so it should be easy for anyone to implement.

Why Does This Happen?

It’s all about the cache…

Any changes to the formatting of a data series in a PivotChart are stored in a cache inside the Excel file.  In the example below I made a few changes to the formatting of the lines in the chart from their default settings.  The colors of the lines and the shapes of the markers were changed when the PivotTable was filtered for the Year 2011.  As you can see in the screencast, when the PivotTable is filtered for the Year 2012 the line colors and markers change back to the default settings.

Excel Pivot Chart Source Range Unchanged on Filter

Even though the source range of the PivotChart remains the same, the formatting of the chart changes.  This is an odd behavior until you understand that the formatting properties are saved and remembered by Excel.

Excel actually stores this data in a cache with all the other chart properties.  This means that it remembers the exact formatting I applied for the Year 2011 only, and that is why the colors and formatting change back to my customization when the PivotTable is filtered for 2011.

To see this you have to dive under the hood of the Excel file.  I included the XML code for the PivotChart in the sample file (download below).  If you search through the code you can see a reference to “2011 – Coffee” and “2011 – Tea”.  Below each of those lines are rows of code that contain the custom formatting settings I applied.

Excel PivotChart Cache XML

Since there is no reference to 2012 or 2013 in the chart’s cache, the default formatting for the chart is applied.  I think it’s cool to be able to see the inner workings of the file and try to understand what is happening with the application.  You can see the components that make up any Excel 2007+ file by changing the file extension to “.zip”, then opening the folder.

Why does this matter?

This behavior is annoying for the most part.  If you click on a slicer or filter, you usually want to see the same custom formatting you applied for the other slices.

But understanding the behavior allows you to either use it to your advantage, or avoid it at design time.  I will explain a solution below that allows you to avoid the behavior, but still use the PivotTable filtering and slicers.

It should also be noted that this behavior does not always happen.  It depends on the setup of the PivotTable and the order of the row and column fields.  If you have a very simple pivot table that only has one field in the column area and one field in the row area, then you might not experience this change in formatting at all.  I am still trying to determine the exact parameters of the PivotTable that cause this to happen.  Please let us know if you have any insights on this.

Pros and Cons

The advantage of this unique formatting behavior is that it allows you to apply customized formatting for each slice of your data.  This could be useful if you want a series to stand out from the rest when the user is clicking through the slices.  You could use it as an attention grabber to highlight some important trends.

The disadvantage is that a lot of the time you want all slices to display the same custom formatting.  If you have a lot of slices then it becomes very tedious work to go through each slice and reapply the customized formatting.  And it will also require maintenance in future periods.  For example, when I get next year’s data for 2014 I will have to create custom formatting for that slice of the data.

Simple Workarounds

One way to prevent the formatting from changing is to create a regular chart that references the PivotTable data.  With either of the following solutions we want to avoid the PivotChart all together, and instead create a regular chart.  Again, the regular charts do NOT suffer from the formatting issues when filters are applied.

Solution #1

This can be accomplished by applying the technique in Jon’s article Making Regular Charts from PivotTables.  With that technique you basically create a regular chart and then carefully add the data series to the chart by referencing areas within the PivotTable.  You just have to be careful with the areas you select or reference because Excel has a tendency to want to convert the regular chart to a PivotChart.

Solution #2

The other option is to create a new area on the worksheet that contains a replica of the PivotTable.  This area just contains formulas that reference the PivotTable.  You can either use the direct cell references like (=C9) or the GETPIVOTDATA() function to point to the PivotTable.  You might want to use the GETPIVOTDATA function if you only want to display a subset of your PivotTable data in the chart.

The advantage of this technique is that once you have the formula based PivotTable setup, you can reference the entire area of the source data range to quickly create a chart that includes all your series.  It might save you time over having to add the series individually as you do in solution #1.

The sample file (download below) contains an example of the technique that I will explain here.

The first step is to recreate the PivotTable data by creating formulas that reference the pivot.  This can be done in cell adjacent to your PivotTable or on a separate worksheet.  Just remember to leave enough blank rows/columns between your PivotTable and formula based table in case your PivotTable expands when filters are applied/removed.

Excel PivotChart Formulas Linked to PivotTable

Step two is to create a regular chart using the new formula driven table as the source of the chart.  When the PivotTable is filtered or sliced, the formulas will automatically be updated and display the new numbers from the PivotTable.  The chart will also be updated and display the new data.

Excel Create Regular Chart to PivotTable Linked Formulas

That’s it!  You should now have a chart that retains it’s formatting when a slicer or filter is applied to a PivotTable.  An example of this behavior can be seen in the screencast below.

PivotChart vs Regular Chart Formatting Changes Slicer Excel

This solution works well for simple cases where the size of the data area stays the same every time the chart is updated.  If the number of rows/columns in your PivotTable is increasing/decreasing every time you update the data, then this solution might take more maintenance or tweaking to work for you.  But the general principle will help get you started and thinking about more advanced solutions if needed.

Jon has a great article on using VBA to create dynamic charts that explains a more advance approach to this issue.


Adding slicers to your PivotCharts and PivotTables is a great way to make your presentation interactive.  PivotCharts allow you to link your chart to a data source so they can be refreshed dynamically with little maintenance.   However, PivotCharts display some odd behavior when filtering charts with custom formatting.  Understanding this behavior and planning for it at design time will save you time and frustration.

The example file can be downloaded below.  Please leave a comment with any questions or suggestions.  Thanks!


Download PivotChart Formatting Changes On Filter Slicer.xlsx PivotChart Formatting Changes On Filter Slicer.xlsx (34.7 KiB)

About the Author

Jon AcamporaJon Acampora writes the blog at where he is focused on helping users improve their Excel and VBA skills.  His contributions include in-depth articles, video tutorials, and free add-ins to help save time and improve productivity. Jon is a newly-minted Microsoft Excel Most Valuable Professional (MVP).


Peltier Tech Charts for Excel

Making Regular Charts from Pivot Tables

Sometimes it’s desirable to make a regular chart from a pivot table, but Excel makes it difficult. If your active cell is in a pivot table, inserting a chart automatically inserts a pivot chart. Defining a source data range that intersects a pivot table automatically converts the chart into a pivot table. And once a chart becomes a pivot chart, it cannot be converted back into a regular chart.

In a moment I’ll show how to make a regular chart from a pivot table’s data. But first let’s review some of the strengths and weaknesses of pivot charts.

Pivot charts have some advantages over regular charts:

  • Pivot charts are dynamic
  • Pivot charts are interactive
  • Pivot charts can be pivoted

However, the same infrastructure that conveys these advantages also results in some shortcomings:

  • Some chart types (XY) are not available to pivot charts
  • The date scale axis is not available in pivot charts
  • All data from the pivot table must be included in the pivot chart
  • Data from outside the pivot table cannot be included in the pivot chart
  • Pivot chart formatting options are limited (alleviated to some extent in Excel 2007)
  • Pivot chart formatting may be lost when the pivot table is changed

When pivot charts were first introduced in Excel 2000, I was excited by the possibilities, yet discouraged by their limitations. I rarely used pivot charts because I felt the limitations far outweighed their capabilities. The formatting capabilities in Excel 2007 are much less constricting than in Excel 2003, so I am more likely to use pivot charts in 2007. While I strongly dislike working with Excel 2007 charts, Excel 2007 pivot charts are greatly improved. The restrictions on pivot chart data sources are still limiting.

Microsoft hosts a number of decent Pivot Table examples. I will use one of them for this tutorial. Go to the Microsoft example 25 easy PivotTable reports, which has a link to Excel 2002 Sample: PivotTable Reports. From this page you can download a self-extracting executable named Reports.exe, which contains four workbooks. The top few records of the Source Data worksheet of the SampleSalespersonReports.xls workbook looks like this:

Pivot Table Source Data

To create a pivot table in Excel 2003, select one cell within this data range, and choose Pivot Table and Pivot Chart Report from the Data menu. To keep things uncomplicated, place the pivot table onto a new worksheet. Drag the Order Date field to the rows area, the Country field to the Columns area, and the Order Amount field to the Data area. Group the Order Date field by month and year as described in Grouping by Date in a Pivot Table.

In 2007, select a cell in the data range, and choose PivotTable from the Insert ribbon tab. By default, manipulation of the fields is done in a Pivot Table Field List pane, not in the worksheet. You can override this setting if you’re willing to trade the new pivot capabilities introduced in 2007 for the convenient but apparently old-fashioned on-sheet button manipulation. In the PivotTable Field List, check the boxes in front of the fields you want in the pivot table, then drag them to the appropriate areas at the bottom of the pane. It’s basically the same as in 2003, just within the task pane.

The resulting pivot table looks like this:

Pivot Chart by Date and by Country

To make a pivot chart, select any part of the pivot table and insert a chart. Excel 2007 places the pivot chart on the active worksheet.

In Excel 2003 and earlier, by default the pivot table is created on its own chart sheet. This is rather inconvenient, since I always move the pivot chart to the same worksheet as the pivot table anyway. To avoid this sheet switching, I select a blank cell that isn’t touching the pivot table, and insert a chart without specifying any data. I then select the blank chart, invoke the Source Data command, and click in the pivot table, which instantly turns the chart into a pivot chart associated permanently with this pivot table.

Pivot Chart by Date and by Country

The pivot chart is linked to the pivot table, so any changes to the pivot table are reflected in the chart. In Excel 2003, the same dragging around of field buttons to pivot the table can also be accomplished in the chart, and the pivot table keeps up. A regular chart is not dynamic in this way. You could write VBA procedures that detect a pivot table change and adjust the chart’s series data accordingly, but that’s probably beyond the scope of any blog post.

However, a pivot chart doesn’t let you add data from outside the pivot table, or exclude data from the pivot table (though you could format a series to make it invisible).

In Excel 2003, you cannot change the size of the plot area or the position of the legend, chart title, or axis titles (other than deleting them). You can remove the pivot field buttons (Pivot Table toolbar > PivotChart dropdown > Hide PivotChart Field Buttons) to gain a little space, but you still can’t move or resize the chart elements.

Pivot Chart by Date and by Country without Pivot Field Buttons

Excel 2007 doesn’t have the Field Buttons cluttering up the chart. Instead there is a floating PivotChart Filter Pane that, together with the PivotTable Field List, serves the same function less intrusively. Excel 2007 also allows you to move or resize the individual chart elements. But the other inflexible features of pivot charts remain.

Pivot Chart by Date and by Country in Excel 2007

So you decide you need a regular chart to regain your formatting and source data flexibility.

Making a regular chart is actually easier than you think, as long as you are careful about setting the chart series data. You cannot set the overall source data range for the chart. You have to start with a blank chart, and add your data one series at a time.

Here is the protocol for creating a regular chart similar to the pivot chart above, using the pivot table’s data.

  1. Select a blank cell which is not touching the pivot table.
  2. Insert a chart. In Excel 2003, go to Insert menu > Chart, and select a chart type and subtype in step 1 of the Chart Wizard, and click Finish. In Excel 2007, simply select a chart type from the Insert tab, then choose the desired subtype.
  3. Right click the chart, choose Source Data or Select Data. In Excel 2003, click on the Series tab.
  4. Click the Add button to add a new series.
  5. Click in the Series Name box, and then select the cell with the series name (e.g., the country label UK).
  6. Click in the Values, Y Values, or Series Values box, then select the range containing the Y values (the Order Amount data under the UK label(.
  7. In Excel 2003, click in the Category (X) Axis Labels or X Values box, then select the range containing the category labels; In Excel 2007, click Edit under Horizontal (Category) Axis Labels, then select the range containing these labels. In both cases, this is the two-column range between the Years and Order Date field buttons at the top and the Grand Total label at the bottom.
  8. Repeat steps 4 through 7 as required for all series, including data inside and outside the pivot table.

Regular Chart by Date and by Country

If I have other data for a country not included in the pivot table, I can simply add it to a regular chart per the protocol above. A pivot chart allows no outside data.

Regular Chart by Date and by Country

If I want an XY chart, I can create a regular XY chart using the protocol above. A pivot chart cannot use the XY chart type, nor can it use a date-scale axis in a line chart.

Regular XY Chart comparing Countries


A follow-up post uses a regular chart, showing how to Create and Update a Chart Using Only Part of a Pivot Table’s Data.


Peltier Tech Charts for Excel

Grouping by Date in a Pivot Table

You can add flexibility to your data analysis by grouping variables. For example, you may have daily sales figures that fluctuate greatly in value, some days having no sales at all, some days with small values, and some with large values. A plot of this data is very noisy, and it’s hard to see any patterns.

Plot by Date

In a pivot table, you can group dates by various measures ranging from seconds to years, to get a clearer view of the data. Plotting by month will smooth out the noise of a daily plot.

Plot by Month

You can combine groupings, for example, to compare monthly sales by year.

Plot by Month by Year

[Read more…]


Peltier Tech Charts for Excel

Peltier Tech Charts for Excel 3.0