How to Make a Pivot Chart in Excel

A pivot chart in Excel is a chart built on top of a PivotTable. It summarises your data the same way the pivot does, and it changes automatically when you filter, rearrange or refresh that pivot. This guide shows how to make one in a few clicks, how to filter it, and how to fix the problems people hit most often.

What is a pivot chart?#

A pivot chart (Excel calls it a PivotChart) is a chart that takes its data from a PivotTable instead of from a fixed range of cells. The PivotTable does the summing and grouping, and the chart draws the result.

That link is the whole point. In a normal chart, if you want to see sales by product instead of by region, you build a new summary and a new chart. In a pivot chart, you drag a different field into the pivot and the chart redraws itself.

All the examples use this small sales table in A1:D9:

If you have never built a PivotTable, start with the beginner's guide to pivot tables and come back.

How to make a pivot chart from a PivotTable#

If you already have a PivotTable, adding a pivot chart takes four clicks. Say your pivot has Region in Rows and Sum of Sales in Values:

1. Click any cell inside the PivotTable.
2. Go to PivotTable Analyze > PivotChart. (In older versions the tab is called Analyze or Options.)
3. Choose a chart type, such as Clustered Column, and click OK.
4. Drag the chart to a clear spot, or cut and paste it onto its own sheet.

You get three columns: East at 1,250, North at 2,100 and South at 2,050. The Grand Total is not plotted. On Windows, a quick shortcut is to click inside the pivot and press Alt+F1, which inserts a pivot chart of the default type.

These steps work in Excel for Windows and current versions of Excel for Mac. Excel for the web has fewer chart options, so use the desktop app if you don't see PivotChart.

How to make a pivot chart straight from your data#

You can also skip the separate PivotTable step and build both at once:

1. Click any cell in your data.
2. Go to Insert > PivotChart (on some versions, Insert > PivotChart > PivotChart).
3. Check the range and choose New Worksheet, then click OK.
4. In the field list, drag Region to Axis (Categories) and Sales to Values.

Excel creates an empty PivotTable and chart on a new sheet and fills both as you drag fields. Every pivot chart needs a PivotTable behind it, so this method still makes one. It just saves you a step.

Tip: turn your data into an Excel table first (Windows: Ctrl+T; Mac: ⌘T). Then new rows you add later are included when you refresh.

How pivot fields map to the chart#

Each area of the PivotTable field list has a chart name, and knowing the mapping makes pivot charts easy to plan:

Put Product into Columns and the chart becomes a clustered column chart with a Laptop bar and a Monitor bar for each region. North shows Laptop 1,800 and Monitor 300. Move Month to Filters and choose Feb, and the chart shows February only: North drops to 600.

Column and bar charts suit most pivot charts. The guides to making a bar chart and choosing the right chart type explain which one to pick. Pivot charts can't be scatter, bubble or stock charts, and newer types such as treemap, sunburst, histogram and waterfall aren't available either.

Filter a pivot chart with field buttons and slicers#

You can filter a pivot chart in three ways, and each one filters the PivotTable too:

• Field buttons on the chart itself. Click the Region button at the bottom, untick East and click OK.
• The PivotTable's own filters, such as the drop-down arrow next to Row Labels.
• Slicers, which are clickable buttons for each item. Select the chart, go to PivotChart Analyze > Insert Slicer, and tick Month.

Slicers are the friendliest option when other people use the file. One slicer can even control several pivot charts that share the same data. The guide to Excel slicers shows how to connect them.

Field buttons can make a chart look busy in a report. To hide them, select the chart and choose PivotChart Analyze > Field Buttons > Hide All.

Common pivot chart problems and fixes#

Most pivot chart problems come from the link to the PivotTable. Here are the usual ones:

1. The chart doesn't show new data. Pivots don't update on their own. Click inside the PivotTable and choose PivotTable Analyze > Refresh (Windows: Alt+F5), or Data > Refresh All for every pivot in the file. See how to refresh a pivot table for data that grows.
2. The chart changed when someone filtered the pivot. That is by design. If you need a frozen chart, copy the PivotTable, paste it as values, and make a normal chart from that copy.
3. Custom colours disappeared. Formatting on single bars can reset when you refresh or rearrange the pivot. Apply colours through a chart style or set them again after big changes.
4. The bars are in a strange order. The chart follows the pivot's sort order. Sort the PivotTable, for example largest to smallest, and the chart follows.
5. Blank or "(blank)" bars. Empty cells in the source data become a "(blank)" item. Fill them in, or filter "(blank)" out of the pivot.
6. The chart type you want is greyed out. Scatter, bubble, stock and some newer types don't work with pivots. Build a normal chart from a summary range instead.

Check a pivot chart with a formula#

It is good practice to check one bar with a formula, especially before a chart goes into a report. The North bar should equal every North sale added together:

=SUMIF(B2:B9,"North",D2:D9)

The result is 2,100, the same as the North bar. To check a bar that has two conditions, such as North Laptop sales, use SUMIFS:

=SUMIFS(D2:D9,B2:B9,"North",C2:C9,"Laptop")

That returns 1,800. If a formula and the chart disagree, the pivot usually needs a refresh or its source range misses some rows.

Practise it#

Pivot charts are only as good as the PivotTable behind them. Practise building PivotTables in a live spreadsheet, then move on to report filters, nested rows and refreshing in Pivot tables 2. Try the exercise on this page too: it checks the North bar with SUMIF, the same habit that catches a stale chart.