Microsoft Excel Pivot Charts: How to Create and Use

A Microsoft Excel PivotChart is a chart linked to a PivotTable, so you can summarize and explore data by moving fields, filtering categories, or changing the summary. In Excel for Microsoft 365 on Windows, select a cell in your data and choose Insert > PivotChart, or select an existing PivotTable and choose PivotTable Analyze > PivotChart.

What a PivotChart is

A PivotChart visualizes summarized data rather than simply plotting every row in a list. It is paired with a PivotTable, Excel’s tool for grouping and aggregating records. For example, a sales list with columns for Date, Region, Product, and Revenue can become a chart of revenue by region, with product categories shown as separate series. You can then rearrange the fields to compare products by month or regions by product without rebuilding the chart from scratch.

The chart and its PivotTable are connected. Changes to the fields, filters, or value summaries in the PivotTable are reflected in the chart, and field changes made from the chart’s field controls update the associated PivotTable. This connection is what makes it a PivotChart, rather than an ordinary chart built from a fixed cell range.

PivotCharts are useful for interactive reports and exploratory analysis, especially when the underlying dataset has many records. They are not a replacement for a PivotTable: the chart is best for spotting patterns and comparisons, while the table can show exact totals and detailed breakdowns.

How the chart organizes data

A PivotChart uses the same field roles as its PivotTable. The field names come from the column headers in the source data. In the PivotChart Fields pane, you can place fields in areas that determine how the chart is drawn:

Axis (Categories): groups along the chart’s horizontal axis, such as regions, dates, or product names.
Legend (Series): divides the data into colored series, such as a separate series for each product.
Values: the numbers being summarized, such as revenue or units sold.
Filters: a report-level filter that limits which records appear in the PivotTable and chart.

Excel chooses a default summary based on the data. A numeric field is commonly summarized by Sum, while a field containing text may be summarized by Count. Check the result: if Revenue appears as a count rather than a sum, the source may contain numbers stored as text, blanks, or other inconsistent entries. Use Value Field Settings to choose an appropriate calculation, such as Sum, Count, or Average.

How to create one in Excel

Start with a tidy rectangular dataset. Each column should have a unique, nonblank header, and each row should represent one record. Avoid merged cells, blank header rows, and subtotals inside the source range; those make it harder for Excel to identify and summarize the records correctly. For a sales report, one row might represent one transaction, with separate columns for date, region, product, quantity, and revenue.

Click a cell in the data. If the source is not already an Excel table, select the complete range, including its headers.
Choose Insert > PivotChart. In the Create PivotChart dialog, check the table or range and choose whether to put the report on a new worksheet or an existing worksheet.
Select OK. Excel creates the associated PivotTable and chart, then displays the PivotChart Fields pane.
Drag fields into Axis (Categories), Legend (Series), Values, or Filters. For example, put Region on the axis and Revenue in Values to compare revenue by region.
Choose a suitable chart type and format the title, labels, and number display so readers can understand the result.

If you already have a PivotTable, click inside it and choose PivotTable Analyze > PivotChart, then select a chart type. The chart is based on that PivotTable’s layout and data. Excel for Mac has PivotChart support, although ribbon placement and some editing options can differ by version. Excel for the web also has a different feature set and interface; if a control is unavailable there, open the workbook in the desktop app.

Arrange fields to answer a question

Field placement should follow the question you want the chart to answer. To compare total revenue across regions, place Region on the category axis and Revenue in Values. To compare product performance within each region, keep Region on the axis and place Product in the legend. The resulting grouped or stacked chart shows the product breakdown for each region. If there are many product categories, the legend may become crowded; filtering to a smaller set can make the comparison clearer.

Date fields can often be grouped into larger periods, such as months, quarters, or years. If Excel treats dates as separate text labels instead, check that the source column contains real Excel dates consistently, not a mixture of dates and text. You can also change how a value is displayed without changing the source records. For instance, Value Field Settings includes options under Show Values As, such as percent of grand total or running total in. A chart showing each region’s percentage of total revenue answers a different question from one showing raw revenue, so label the chart accordingly.

To rearrange fields, drag them between the areas in the field pane, or use the field list controls to remove fields that are no longer needed. If the pane is hidden, select the PivotChart and use PivotChart Analyze > Field List. The exact contextual ribbon tabs appear when the chart or its associated PivotTable is selected.

Filters, slicers, and interactive reports

A PivotChart can be filtered through its field buttons, through the associated PivotTable, or with a slicer. Field buttons let a reader filter categories or series directly on the chart. To hide them when they clutter a finished report, select the chart and use PivotChart Analyze > Field Buttons. Hiding buttons changes the display, not the underlying data or filter state.

Slicers provide clickable buttons for fields such as Region or Product. Select the PivotTable, then choose PivotTable Analyze > Insert Slicer, select the fields, and confirm. A date field can use a Timeline through PivotTable Analyze > Insert Timeline when Excel recognizes it as a date. Selecting a slicer item filters the connected PivotTable and chart, making a report easier to explore than a set of dropdowns.

When several PivotTables or PivotCharts are in a report, do not assume one slicer controls all of them. A slicer can connect only to compatible PivotTables that share the relevant data source or PivotTable cache. Select the slicer and look for Slicer > Report Connections (the tab may be named Slicer in some ribbon layouts) to choose the reports it should control. If a report is missing from the connections list, its PivotTable may have been created from a different source or cache.

Refresh the chart when source data changes

A PivotChart does not necessarily recalculate immediately when source records are edited or appended. Refresh its associated PivotTable to bring in updated values: click inside the PivotTable and choose PivotTable Analyze > Refresh. To update all refreshable PivotTables and connections in a workbook, use Data > Refresh All. The chart then reflects the refreshed summary.

For regularly expanding data, format the source as an Excel table before creating the PivotTable. A table can include newly added rows when the PivotTable is refreshed, avoiding the common problem of a fixed source range ending above the latest records. If the source is an ordinary range and new rows sit outside it, use PivotTable Analyze > Change Data Source to select the expanded range, then refresh.

Refreshing updates the summary from the source; it does not correct errors in the source itself. Check for missing values, inconsistent category spelling, dates stored as text, or numeric amounts stored as text. For data pulled from an external connection, refresh may also depend on network access, permissions, or credentials. Save the workbook after updating it if you need the refreshed results to be available when it is reopened.

Choose a chart type that fits the comparison

Column and bar charts are useful for comparing categories, particularly when the values are totals such as revenue by region. A line chart is often easier to read when the horizontal axis represents time and the goal is to see a trend. A stacked column or bar chart can show both category totals and their composition, but small segments may be difficult to compare. A pie chart is most readable with only a few categories that make up a meaningful whole; it is a poor choice for dozens of products.

Choose the chart type using PivotChart Design > Change Chart Type. Available choices depend on the data and chart setup. PivotCharts do not support every chart type available for ordinary charts; in particular, scatter, stock, and combo chart types are not supported as PivotCharts. If you need one of those types, or need to control individual series in a way the PivotChart interface does not allow, build a regular chart from a prepared summary range instead.

Also consider the number format and labels. Currency values should be formatted consistently, and a chart title should state both the measure and grouping, such as Revenue by Region. A chart that displays percentages should say so rather than leaving viewers to infer whether the values are totals, averages, or shares.

PivotChart or regular chart?

Choose a PivotChart when people need to change the grouping, filter categories, or interact with a summarized dataset. It is particularly useful when a report needs to move between views, such as sales by month, sales by region, and sales by product, without maintaining separate chart ranges for every view. Its PivotTable connection also makes aggregation and filtering part of the report rather than a separate manual preparation step.

Choose an ordinary chart when the visual must use a specialized chart type, when the plotted values need custom calculations outside PivotTable summaries, or when you want precise control over the source series and cell references. A regular chart can be built from cells that contain formulas or a carefully prepared summary. The tradeoff is that it will not automatically provide PivotTable field rearrangement and filtering. One practical approach is to use a PivotTable to prepare the summary and create a regular chart from that summary if PivotChart limitations prevent the desired design.

Common problems and fixes

The chart shows Count instead of Sum: Check Value Field Settings and inspect the source column for numbers stored as text or inconsistent values. Correct the source, then refresh.
New records do not appear: Refresh the PivotTable. If rows were added beyond a normal source range, use Change Data Source or convert the data to an Excel table for future expansion.
Dates appear individually or group incorrectly: Confirm that every value in the date column is a valid date and that there are no text entries or blanks interfering with grouping.
The chart is crowded: Filter categories, reduce the number of series, or choose a bar chart for long category labels. Avoid adding every field to the chart at once.
Changing one chart affects another: The charts may share a PivotTable or cache. Review slicer Report Connections and consider separate PivotTables when the reports need independent filtering.

Another common misconception is that changing a value in the chart itself changes the underlying source record. It does not: a PivotChart displays an aggregated view. Edit the source data when a transaction is wrong, then refresh the PivotTable. Similarly, hiding a category in a chart filter does not delete that category from the source.

Frequently Asked Questions

How do I create a PivotChart in Excel?

Select a cell in the source data and choose Insert > PivotChart. Confirm the range and worksheet location, then use the PivotChart Fields pane to assign categories, series, values, and filters. To chart an existing PivotTable, select it and choose PivotTable Analyze > PivotChart.

Does a PivotChart update automatically?

It follows changes to its PivotTable layout and filters, but edited or newly added source records may require a refresh. Use PivotTable Analyze > Refresh or Data > Refresh All. An Excel table is a useful source when rows are added over time.

Can I make a PivotChart without a PivotTable?

No. A PivotChart is tied to a PivotTable, and Excel creates the associated PivotTable when you create a PivotChart directly from source data. If you need a chart without that PivotTable relationship, create an ordinary chart from a cell range instead.

Can a PivotChart use slicers?

Yes. Add a slicer from PivotTable Analyze > Insert Slicer, or add a Timeline for a recognized date field. These controls filter the connected PivotTable and its PivotChart. Use Report Connections to check which compatible PivotTables a slicer controls.

Why can’t I choose a combo or scatter chart?

PivotCharts do not support some chart types, including combo, scatter, and stock charts. If one of those is required, create a summary in cells and build a regular chart from that range, or use another supported PivotChart type.

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.