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

To create a PivotChart in Microsoft Excel for Microsoft 365 on Windows, select a cell in your data, choose Insert > PivotChart, confirm the table or range, choose where to place the PivotTable, and select OK. Then arrange fields in the PivotChart Fields pane to choose what the chart compares. A PivotChart is connected to a PivotTable, so you can summarize, filter, and explore the underlying data without building each chart series by hand.

What a PivotChart Does

A PivotChart turns a large list of records into a visual summary. Instead of plotting every transaction or row, it groups values by categories and summarizes a numeric field. For example, a sales list with Date, Region, Product, and Revenue columns can become a chart of monthly revenue, with separate colors for each region. You can change the grouping or filter the view using the PivotChart’s field controls.

PivotCharts work together with PivotTables. The PivotTable performs the grouping and calculation; the chart displays that summary. Changing a field, filter, or value calculation in one can change the other. This link is useful for interactive analysis, but it also means a PivotChart is not simply a regular chart placed over a fixed range of cells.

Prepare the Source Data

Good source data makes the chart easier to create and less likely to produce confusing summaries. Arrange records in a simple rectangular list: one header row, one record per row, and one type of information per column. Avoid blank rows or columns within the list, merged cells, and multiple header rows. Give every column a distinct, descriptive header, such as Date, Region, Product, and Revenue.

Check that values in each column have consistent data types. Dates should be stored as dates rather than a mixture of dates and text, and revenue should be numeric rather than text containing manually typed currency symbols. If a value column contains text or blanks, Excel may count records rather than sum the values, or may omit entries from a calculation.

For a source list that will grow, convert it to an Excel table before creating the chart. Select a cell in the data and press Ctrl+T on Windows, or Command+T on Mac, check that the range and header setting are correct, and select OK. A table can expand when rows are added, which makes it a convenient PivotTable source. New rows still need to be included in the next refresh before they appear in the chart.

Create the PivotChart in Excel

Select a cell in the source list or Excel table. You do not need to select the entire list if the data is arranged as a continuous range.
On the ribbon, choose Insert > PivotChart. In the Create PivotChart dialog, check the table or range. If the selection is wrong, enter the correct range or table name.
Choose whether the associated PivotTable should go on a New Worksheet or an Existing Worksheet. If you choose an existing sheet, specify a cell with room for the PivotTable.
Select OK. Excel creates the PivotTable and chart and displays the PivotChart Fields pane. If the pane is hidden, select the chart and use PivotChart Analyze > Field List.
Drag fields into the pane’s areas, or select their checkboxes and adjust their placement. The chart updates as you build the summary.

You can also create a chart from an existing PivotTable: select a cell in that PivotTable, choose PivotTable Analyze > PivotChart, select a chart type, and confirm. The PivotTable Analyze tab appears when a PivotTable is selected. Excel for Mac has PivotChart tools too, although ribbon placement and dialog wording can vary by build; use the Insert tab or the PivotTable Analyze tab when working from an existing PivotTable. There is no single dedicated PivotChart keyboard shortcut.

Choose and Arrange the Fields

The field areas determine the chart’s meaning. In the PivotChart Fields pane, Excel labels the main areas Axis (Categories), Legend (Series), Values, and Filters. These correspond closely to the familiar PivotTable areas of Rows, Columns, Values, and Filters.

Axis (Categories): Put a category here to define the chart’s horizontal axis, such as Month, Product, or Region.
Legend (Series): Put a field here to split the data into colored or otherwise distinct series, such as Region or Salesperson.
Values: Put a numeric field here to determine what is measured, such as Revenue or Units Sold.
Filters: Put a field here when you want to filter the whole report, for example to show one year or one department.

For example, to compare monthly revenue across regions, place Date in Axis (Categories), Region in Legend (Series), and Revenue in Values. Excel may group dates into months, quarters, or years; you can change or remove grouping from the associated PivotTable. If you see Count of Revenue instead of Sum of Revenue, open the value field’s settings and choose Sum, provided the source values are numeric. A chart with too many series or categories can become difficult to read, so start with one clear question and add fields only when they improve the comparison.

Choose a Chart Type and Format It

Choose a chart type that suits the comparison. Clustered columns or bars are useful for comparing categories; lines are often helpful for a trend over time; and stacked charts show how components contribute to a total. A pie chart is usually clearer with a small number of parts that make up one whole. If the chart is crowded, filter the categories or remove a series before shrinking labels to an unreadable size.

Select the chart to access the Chart Design and Format tabs. Use Chart Design to change the chart type, apply a style, or add chart elements such as a title, axis titles, and data labels. Use the Format tab for visual adjustments. Give the chart a title that states what is being measured and the grouping, such as Monthly Revenue by Region. Check that number formats and axis scales are appropriate; a currency value axis, for example, is easier to interpret when formatted as currency.

PivotCharts do not support every chart type or chart feature available to standard Excel charts. Some specialized types, including XY scatter, stock, and combination charts, are not available as PivotCharts. If a required chart type is unavailable, use a regular chart based on a summarized range instead.

Filter and Explore the Chart

PivotChart field buttons let readers filter categories or series directly on the chart. Select a field button, choose the items to show, and apply the selection. Depending on the layout, the buttons may appear over the chart; you can hide or show them from PivotChart Analyze > Field Buttons. Hiding the buttons changes the display, not the underlying report or its filters.

For a more visual control, select the PivotTable or PivotChart and choose PivotTable Analyze > Insert Slicer, then select the field or fields to filter. A slicer can make it obvious which items are selected. For date-based PivotTables, the Analyze tab may also offer Insert Timeline. A slicer or timeline filters the connected PivotTable and its PivotChart; if several PivotTables need to respond together, they must share a compatible data source and be connected through the report connections or connections options.

Refresh Data and Fix Common Problems

A PivotChart summarizes a cached view of its source, so changes to the source list do not always appear immediately. Select the PivotTable or PivotChart and choose PivotTable Analyze > Refresh. To refresh multiple connections and reports in the workbook, use Data > Refresh All. If you added rows below an ordinary fixed range, check the PivotTable data source and extend it; using an Excel table as the source helps avoid that range-maintenance step. Refresh after adding table rows.

The chart is blank or incomplete: Check that the intended source range was selected and that filters are not excluding all the records.
Values are counted instead of summed: Inspect the source column for text-formatted numbers or inconsistent entries, then set the value field summary to Sum and refresh.
Dates appear as individual daily entries: Group the date field in the associated PivotTable if a month, quarter, or year summary is more useful.
New records do not appear: Refresh the report and confirm the new rows are inside the source table or PivotTable range.
The chart looks cluttered: Reduce the number of series, filter less relevant categories, or choose a bar or line chart that better suits the data.

Be cautious when changing the source structure after creating the PivotTable. Renaming or deleting a source column can leave the PivotTable with a missing field or prevent a refresh. If you reorganize the list, verify the source and field layout, then refresh and check the chart before sharing it.

PivotChart or Regular Chart?

Choose a PivotChart when readers need to filter, regroup, or compare a large dataset interactively, or when the summary should update alongside a PivotTable. It is particularly useful for dashboards and exploratory reports where the same values may need to be viewed by different categories.

Choose a regular chart when you need precise control over individual data points, a chart type that PivotCharts do not support, or a fixed presentation that should not change with PivotTable filtering. One approach is to build a PivotTable, copy its summary, and create a regular chart from the copied values. That chart will not automatically stay linked to the PivotTable summary, so you must update the copied range and chart when the summary changes.

Frequently Asked Questions

Can I create a PivotChart without a PivotTable?
Excel creates or uses an associated PivotTable for a PivotChart. When you start with a data range, Excel creates the supporting PivotTable as part of the process. You can place that PivotTable on another worksheet, but the chart still depends on it.

How do I change the data shown in a PivotChart?
Select the chart and use the PivotChart Fields pane to move fields among Axis (Categories), Legend (Series), Values, and Filters. You can also use field buttons, slicers, or a timeline to filter the report without rebuilding the chart.

Why is Excel counting my numbers instead of adding them?
Excel may treat the field as text because some source entries are stored as text, contain nonnumeric characters, or have inconsistent values. Correct the source data, refresh the PivotTable, then choose Sum in the value field settings if needed.

Will a PivotChart update when I add rows?
Rows added to an Excel table used as the source can be included when you refresh the PivotTable. With a normal cell range, you may also need to change the source range so that it covers the new records.

Can I use a slicer with a PivotChart?
Yes. Insert a slicer from PivotTable Analyze > Insert Slicer, choose a field, and use the slicer buttons to filter the connected report. The PivotChart reflects the same filtered PivotTable data.

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.