Create a Pivot Chart in Excel: Step-by-Step Guide

To create a PivotChart in Excel for Microsoft 365 on Windows, select a cell in your data, choose Insert > PivotChart, confirm the data range and where to place the chart, then use the PivotChart Fields pane to arrange fields. Excel creates a chart linked to a PivotTable, so you can summarize, filter, and rearrange the data without building a separate chart from scratch.

What a PivotChart does

A PivotChart is an interactive chart connected to a PivotTable. The PivotTable organizes records into categories and calculations; the chart displays that summary visually. For example, a sales list with one row per transaction can become a chart showing total sales by month, with product categories available as filters. You can change the chart’s fields to look at a different breakdown without editing the original records.

This connection is useful, but it is also important to understand: the chart and its associated PivotTable share their layout and filtering. Changing a field in one can affect the other. A PivotChart is not simply a picture of a PivotTable, and it is not independent of the summary underneath it.

Prepare the source data

Good source data makes the PivotChart easier to build and more reliable. Use a rectangular table with one header row and one record per row. Each column should contain one kind of information, such as date, product, region, or sales amount. Avoid merged cells, blank header names, and subtotals embedded between records.

For instance, a sales table might have columns named Date, Product, Region, Units, and Sales. Each row would describe one sale or transaction. Repeated product and region names are expected; Excel groups those values when you place them in chart categories or filters. Make sure dates are stored as actual Excel dates and amounts as numbers, rather than text that only looks numeric.

For a source list that will keep growing, convert it to an Excel Table first. Select a cell in the list and choose Insert > Table, confirm that the table has headers, and select OK. A PivotTable based on an Excel Table can include rows added to that table when refreshed, whereas a PivotTable based on a fixed range may not include new rows outside its original source range.

Create a PivotChart step by step

Select a cell in the source data or Excel Table. You do not normally need to highlight the entire data range if the list has a clean, contiguous layout.
On the ribbon, choose Insert > PivotChart. If you do not see the command, look in the Charts area of the Insert tab or use the ribbon search box to find PivotChart.
In the dialog, check the table or range shown for the source. Choose whether the PivotChart should go on a new worksheet or an existing worksheet, then select OK.
In the PivotChart Fields pane, select fields or drag them into the available areas. Put a category such as Product or Month in Axis (Categories), a numeric field such as Sales in Values, and optional fields such as Region in Legend (Series) or Filters.
Choose a chart style and adjust its title, labels, colors, or formatting using the chart tabs on the ribbon. The exact contextual tab names appear when the chart is selected.

Excel may place the PivotChart and PivotTable on the same worksheet or put the PivotTable on a separate sheet, depending on the creation route and placement choices. The PivotTable is the chart’s summary source, even if you move it or arrange the sheets differently. Keep it available while you configure fields and troubleshoot results.

Arrange fields to answer a question

The field areas determine what the chart communicates. Axis (Categories) creates the labels along the chart’s category axis. Values supplies the calculation, such as Sum of Sales or Count of Orders. Legend (Series) separates values into colored series, while Filters lets a reader narrow the whole report by a field without adding it to the chart’s main categories.

Suppose you want to compare monthly sales by region. Place Date in Axis (Categories), Sales in Values, and Region in Legend (Series). If Excel groups the dates into years, quarters, or months, check that they are genuine dates and use the available date grouping to select the level that suits the question. If your data already contains a Month column, that field can be used instead, though a separate year field may be needed when records span multiple years.

Excel usually sums a numeric field in Values, but may count it if the column includes text, blanks, or inconsistent data. If the chart shows a count instead of a total, open the field’s menu in the Values area, choose Value Field Settings, and select the appropriate summary, such as Sum, Count, or Average. Confirm that the source values are truly numeric before changing the setting; choosing Sum cannot turn text entries into amounts.

Change the chart and add interactive filters

With the chart selected, use the contextual chart tabs to choose a supported chart type and adjust design elements. Column charts work well for comparing categories, while line charts are often useful for showing change over time. Keep the number of series manageable: too many colored lines or columns can make a chart difficult to read. A descriptive chart title should state the measure and the grouping, such as Sales by Month and Region.

To let readers filter with visible buttons, select the PivotChart and choose PivotChart Analyze > Insert Slicer, then select the fields to filter. A slicer displays clickable category buttons, such as North, South, and West. For date-based reports, Excel can also provide a timeline control when the PivotTable includes a usable date field. Slicers and timelines are helpful for dashboards because they make report controls easier to discover than small drop-down arrows.

You can also use the filter buttons displayed on a PivotChart to include or exclude categories or series. Filtering changes the view of the PivotTable and its chart; it does not delete the excluded records from the source data. If you need one slicer to control more than one PivotTable or PivotChart, check that the reports share a compatible PivotTable source, then use the slicer’s report connections option. Not every report can be connected, particularly when the underlying PivotTables do not share the required source or cache.

Refresh the chart when data changes

A PivotChart summarizes PivotTable data, which may not update the instant that source records change. To refresh it, select the PivotTable or chart and choose PivotTable Analyze > Refresh, or use Data > Refresh All to refresh workbook connections and reports. The PivotChart reflects the refreshed PivotTable.

If new records are missing after a refresh, inspect the PivotTable’s data source. A fixed source range may stop at the old last row. Change the source to include the additional rows, or use an Excel Table as the source so the table can expand with added records. Also check whether new rows contain the same headers and data types as the existing columns. Refreshing updates the report from its source; it cannot correct an incomplete range or inconsistent source data by itself.

PivotChart versus a regular chart

Choose a PivotChart when you want a chart that readers can reorganize or filter using PivotTable fields. It is especially useful for exploring a large list through different combinations, such as sales by product, month, and region. Its field pane, filters, slicers, and relationship to the PivotTable make it suited to analysis.

A regular chart is often preferable when you need precise control over individual data points, want a chart independent of a PivotTable, or need a chart type that PivotCharts do not support. Certain types, including XY scatter, stock, and bubble charts, are not available as PivotCharts. Some formatting and chart-layout options can also be more constrained. In that case, create a summary table with the exact values you want and insert a regular chart from that table. The trade-off is that the chart will not automatically offer PivotTable-style field rearrangement.

For a fixed presentation graphic with a small, carefully selected range, a regular chart may be simpler. For an exploratory report where the audience needs to change categories or filter results, a PivotChart usually offers more flexibility. The best choice depends on whether interaction and rapid reorganization matter more than complete chart-format control.

Common problems and practical fixes

The wrong chart appears: Review the field areas. A text field in Values may be counted, while a numeric field in Axis (Categories) may produce an unhelpful set of labels. Move each field to the area that matches its purpose.
Amounts are counted instead of summed: Check for numbers stored as text, mixed entries, or blanks in the source column. Correct the source data, refresh, and check Value Field Settings.
New rows are not included: Refresh the report and inspect its source range. Expand a fixed range or base the PivotTable on an Excel Table.
Dates are grouped unexpectedly: Confirm that the source cells contain valid dates and decide whether the chart should show days, months, quarters, or years. Mixed text and date values can prevent expected grouping.
The chart seems to change when the PivotTable changes: That behavior is normal. The chart and PivotTable are linked; changing their shared fields or filters changes the report view.
The chart type you need is unavailable: Use a supported PivotChart type or create a regular chart from a separate summary table.

Before sharing a report, check that filters have not left the chart showing only a subset of the data, that the values use the intended calculation, and that the chart title and axis labels make the units clear. A chart showing 12,000 could mean dollars, units, or orders; labels and context prevent that ambiguity.

Excel for Mac and Excel for the web

The steps above use Excel for Microsoft 365 on Windows. Excel for Microsoft 365 on Mac also supports PivotCharts, but the ribbon layout and the placement or wording of commands can differ between builds. Look on the Insert tab to create a PivotChart, and select the chart or PivotTable to reveal its contextual controls. If a command is not visible, use Excel’s command search or check the ribbon tabs that appear after selecting the report.

Excel for the web has a different interface and may not expose every desktop command or chart-formatting option. If a workbook’s PivotChart needs a feature that is unavailable in the browser, open the workbook in the desktop Excel app. Avoid assuming that a missing command means the chart itself is invalid; it may be an interface or feature-availability difference.

Frequently Asked Questions

Can I create a PivotChart without a PivotTable?
A PivotChart relies on a PivotTable summary, even when Excel creates both as part of one command or places the supporting table out of the way. You need the PivotTable structure for the chart’s fields, calculations, and filters.

How do I add a field to an existing PivotChart?
Select the PivotChart to display the PivotChart Fields pane. Check the field or drag it into Axis (Categories), Values, Legend (Series), or Filters. If the pane is closed, use the contextual PivotChart Analyze tab to show the field list.

Why does my PivotChart show Count of Sales instead of Sum of Sales?
Excel may be treating some entries in the Sales column as text or may be encountering inconsistent values. Correct the column so it contains numeric amounts, refresh the PivotTable, and use Value Field Settings to select Sum.

Will a PivotChart update automatically when I add data?
Not necessarily. Refresh the PivotTable or choose Data > Refresh All, and make sure the added rows are within the source. An Excel Table is often a convenient source for data that grows over time.

Can I use a PivotChart as a normal chart?
You can format and position it like a chart, but its fields and filters remain linked to its PivotTable. If you need unsupported chart types or independent control over each plotted range, create a regular chart from a separate summary table instead.

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.