Pivot chart in Excel: create a chart from a pivot table, step by step

A pivot chart is an Excel chart tied to a PivotTable: filter, rearrange or refresh the table and the chart changes with it. To create one, click inside the PivotTable and choose PivotTable Analyze > PivotChart, then pick a chart type. This guide builds one from the ten-line sales ledger used in how to make a pivot table in Excel, then adds slicers and the check that ties the chart back to the ledger.

What a pivot chart is

A regular chart plots fixed cells. A pivot chart plots a PivotTable, so it changes shape whenever the PivotTable does.

Microsoft's overview of PivotTables and PivotCharts puts it this way: "Standard charts are linked directly to worksheet cells, while PivotCharts are based on their associated PivotTable's data source." So you cannot change its data range or switch rows and columns in Select Data Source; you move fields in the PivotTable instead.

The rows you need

A flat list, one row per invoice line, with a date, the categories you want to chart by, and one amount column. The ledger from the pivot table guide has exactly that: ten invoice lines for Q1 2026 in USD, with Date, Customer, Region, Product line and Net amount, including one credit memo of -1,200.

Format it as an Excel Table (Ctrl+T) named Ledger. A Table grows when rows are added, so the PivotTable, and the chart on top of it, pick up next month's lines on refresh. A chart built on a fixed range such as A1:E11 never sees row 12.

Step by step: create a chart from a pivot table

In Excel for Microsoft 365 on Windows:

1. Click any cell in Ledger and choose Insert > PivotTable > From Table/Range, New Worksheet. Name the sheet Pivot; the PivotTable starts at A3.
2. Drag Date to Rows. If Excel shows one row per day, right-click a date, choose Group, select Months and Years, and click OK.
3. Drag Product line to Columns and Net amount to Values (Sum of Net amount).
4. Click any cell in the PivotTable and choose PivotTable Analyze > PivotChart. Microsoft's Create a PivotChart page uses Insert > PivotChart for the same step; both open the Insert Chart dialog.
5. Choose Column > Clustered Column and click OK.

To build both at once, start from a cell in Ledger and choose Insert > PivotChart. On a Mac and in Excel for the web, Microsoft's steps differ: create the PivotTable first, select a cell in it, then insert an ordinary chart from the Insert tab (Insert Chart on the web), and the chart is linked to the PivotTable.

Worked example: ten invoices

The PivotTable behind the chart, rows grouped by month:

The chart shows three pairs of columns, one per month, with Ambient and Chilled side by side. January Ambient is Northgate Retail's $3,150 plus Brightwell Inc.'s $8,760: $11,910. March Chilled is Brightwell Inc.'s $9,900 less the Harlow Foods credit memo of $1,200: $8,700.

What the chart seems to say is that sales halved: March at $12,500 against January at $24,310, down 48.6%. Read the dates before you believe it. The last line in the ledger is dated March 20. If this export was pulled on March 20, March is a partial month plotted beside two full ones. Compare like with like, the first 20 days of each month:

March to the 20th is down 19.6% on January to the 20th (12,500 against 15,550), not 48.6%. A real fall, but less than half the one the chart suggests. Label the last column "Mar to date"; fiscal calendars and period cuts covers stating the period cut on every report.

The check

A pivot chart is only as right as the PivotTable under it. Two checks:

=SUM(Ledger[Net amount])

returns 56,260, the PivotTable grand total. Then pull a single figure by name rather than by cell address, so it survives a layout change:

=GETPIVOTDATA("Net amount",Pivot!$A$3,"Product line","Chilled")

returns 31,050, the Chilled column total. Each month's pair of columns must also add to that month's total: 11,910 + 12,400 = 24,310 for January. Clear every slicer before checking.

Filtering with slicers and timelines

Click the chart or the PivotTable, choose PivotChart Analyze > Insert Slicer (PivotTable Analyze > Insert Slicer from the table), tick Region, and click OK. Microsoft's slicer guide covers the dialog. Click Northeast:

The table and the chart filter together. For dates, PivotTable Analyze > Insert Timeline adds a slider by month, quarter or year; Microsoft's timeline page shows it.

One slicer can drive several PivotTables, and so several charts, if they share the same data source: select the slicer, then Slicer > Report Connections, and tick each PivotTable. That is how one Region slicer filters every chart on an Excel dashboard.

Formatting that survives refresh

• Number formats in the field, not the cells. Right-click a value, choose Value Field Settings > Number Format, and set #,##0. Cell formatting applied directly can be lost when the pivot refreshes.
• Chart styles stick. Colors and the style you pick on the Design tab survive refresh. Microsoft notes that trendlines, data labels, error bars and other changes to data sets are not preserved, so add them last or recheck them after each refresh.
• Hide the field buttons for print. PivotChart Analyze > Field Buttons > Hide All removes the gray filter buttons from a chart going into a monthly sales report. The slicer still works.

Pivot chart limits

Microsoft documents that a PivotChart can use any chart type "except an xy (scatter), stock, or bubble chart." If you need one of those, copy the PivotTable and paste values to a normal range, then chart that range. You lose the link: the chart will not follow filters or refreshes, so date the copy.

Every visible item in the PivotTable becomes a category or series, so a pivot with 40 customers gives 40 columns. Filter to the top items first.

Where it goes wrong

• A partial last period plotted beside full ones. March to the 20th looked like a 48.6% fall; like for like it is 19.6%.
• Source not formatted as a Table. Refresh misses new rows and the chart stops at the old range without warning.
• Dates grouped without Years. Grouping by Months alone merges January 2025 and January 2026 into one column.
• Count instead of Sum. One blank or text cell in Net amount makes Excel summarize by Count, and the chart plots numbers of lines, not dollars.
• An unsupported chart type found late. Scatter, stock and bubble are not available as PivotCharts; decide before building the report.

Board-ready charts from your own files

Covirage computes the month-by-product-line tables from your uploaded ledger with deterministic tools, states the period cut on every table, and checks the grand total against the source; the external AI model explains the movement and never does the arithmetic. See board reporting, and when one sheet is no longer enough, Power Pivot in Excel for pivots across related tables. For full layouts, see Excel dashboard examples; for the board pack the charts go into, the board report template.