Excel PivotTable Charts: How to Create and Use Them

To create an Excel PivotChart in Microsoft 365 for Windows, click a cell in a PivotTable, choose PivotTable Analyze > PivotChart, select a chart type, and click OK. A PivotChart visualizes the PivotTable’s summarized data and changes when you rearrange or filter its fields.

What an Excel PivotChart Does

A PivotChart is a chart connected to a PivotTable, Excel’s tool for grouping and summarizing a larger list of records. Instead of manually selecting a fixed range of totals, you choose fields such as date, product, region, and sales, then arrange them to compare the summaries that matter. The chart presents those results visually, while the PivotTable provides the underlying layout and values.

For example, a sales list might contain one row per order, with columns for order date, salesperson, region, product, and revenue. A PivotTable can total revenue by region and product; a PivotChart can show those totals as clustered columns. Move Region to a different field area or filter the products, and the chart updates to reflect the new summary.

This connection is useful for exploring data, but it also explains an important limitation: a PivotChart is not an independent picture of an arbitrary cell range. Its categories, series, and filters are controlled through its associated PivotTable fields.

Prepare the Source Data

Good source data makes PivotTables and PivotCharts easier to build and less likely to produce misleading results. Keep the source as a rectangular list: one header row, one record per row, and one type of information in each column. Avoid merged cells, blank header cells, and completely blank rows or columns inside the list.

Use clear, unique column headings such as Date, Region, Product, and Revenue. Each row should describe one transaction or observation. If a sales record contains a date, product, region, and amount, put each value in its own column rather than combining them in a single text cell.

Check that dates are real Excel dates, not text that only looks like a date.
Keep numeric values numeric. Text entries mixed into an amount column can cause Excel to count records instead of summing values.
Represent missing information consistently. A blank region, for instance, may appear as a separate blank item in the PivotTable.
Consider converting the source range into an Excel table with Insert > Table. A table can expand as you add records, although the PivotTable still needs refreshing to include new data.

Before building a report, decide what one row represents. If the list contains monthly totals rather than individual sales, the PivotChart will summarize those monthly records; it cannot recover transaction-level detail that is absent from the source.

Create a PivotChart Step by Step

If a PivotTable already exists, select any cell inside it. On the Windows desktop version, open PivotTable Analyze > PivotChart, choose a suitable chart in the Insert Chart dialog, and select OK. Excel creates a chart linked to the PivotTable. Depending on the workbook and ribbon layout, the PivotChart command may also be available from the Insert tab.

To start with a source list instead, select a cell in the data and choose Insert > PivotChart. In the dialog, confirm the table or range and choose where to place the PivotChart report, then click OK. Excel creates the supporting PivotTable layout as part of the process. Use the PivotTable Fields pane to add the source fields you want to analyze.

Drag a descriptive field, such as Region or Product, to Axis (Categories) to define the chart’s categories.
Drag a numeric field, such as Revenue, to Values to create the plotted measure.
Add a field to Legend (Series) to split the measure into groups, such as revenue by product within each region.
Use Filters or the chart’s field controls to focus the report on a subset, such as a particular year.

Excel often places text categories on the axis and numeric values in Values automatically, but check the result rather than relying on the default. The exact field area names can vary with chart orientation and Excel build. On Excel for Mac, PivotCharts are supported, though ribbon labels and dialog placement can differ from Windows. Excel for the web also supports many chart and PivotTable tasks, but the desktop app may offer a fuller set of controls.

Arrange Fields and Check the Summary

In the PivotTable Fields pane, Axis (Categories) determines the labels along the chart’s category axis. Legend (Series) creates separate plotted groups, Values supplies the numbers, and Filters restricts what the report includes. Dragging a field between areas changes the analysis rather than changing the source data.

Check how Excel summarizes a numeric field. A column of numbers is commonly summed, while text or mixed data may be counted. To change the calculation, open the field’s menu in the Values area, choose Value Field Settings, and select an available summary such as Sum, Count, or Average. Use Sum for total revenue, for example, and Average when the question is about the typical value per record.

Also verify the meaning of the categories. A chart showing revenue by month may combine January from several years unless Year is included as a separate field or used as a filter. For date fields, Excel may offer date grouping such as months or years; grouping is only meaningful when the source dates are valid and the chosen periods answer the question.

When a value seems implausible, inspect the PivotTable before formatting the chart. A chart can display an incorrect aggregation or an unexpected blank category very neatly. Confirm the source rows, field placement, filters, and summary calculation first.

Choose a Chart Type That Fits the Question

Choose a chart according to the comparison you want readers to make, not simply because a particular style looks attractive. PivotCharts include familiar options such as column, bar, line, and pie charts. Some standard chart types, including XY (scatter), stock, and combo charts, are not supported as PivotCharts. If a needed type is unavailable, a regular chart based on a separate summary range may be more appropriate.

Clustered column or bar: Compare values across categories, such as revenue by region. A horizontal bar chart can be easier to read when category names are long.
Line: Show change over ordered time periods, such as monthly orders. A line is less suitable when the axis categories have no meaningful sequence.
Stacked column or bar: Compare totals while showing how each total is divided among groups. It can be difficult to compare the smaller components across categories.
Pie: Show parts of one whole when there are only a few categories. It is usually a poor choice for many products or for comparing several series.

Keep the number of categories and series manageable. A chart with dozens of product labels or a separate color for every item can become harder to interpret than the PivotTable. Filtering to a relevant period or grouping smaller categories into a clearly labeled group can make the main pattern more visible.

Filter, Refresh, and Format the Chart

PivotChart field buttons let readers filter or rearrange fields directly on the chart, depending on which buttons are displayed. Slicers provide clickable buttons for filtering fields such as Region or Product; create one from the PivotTable or PivotChart Analyze controls using Insert Slicer, then choose the field to filter. A timeline can offer date-based filtering when the PivotTable contains a suitable date field. These controls change the report view, not the underlying source records.

When source records change, refresh the PivotTable so the chart can reflect the updated summary. Use PivotTable Analyze > Refresh for the selected report, or Data > Refresh All to refresh workbook connections and PivotTables. If the source is an Excel table, new rows can be included when the PivotTable refreshes. If the source was a fixed cell range and new rows were added outside it, update the data source or convert the list to a table before refreshing.

For presentation, add a descriptive chart title and clear axis labels. Select the chart and use Chart Design > Add Chart Element to add or adjust elements such as a chart title or axis titles. Keep number formats consistent with the data: currency values should be identified as currency, and percentages should be displayed as percentages. Avoid excessive colors and unnecessary 3-D effects, which can make comparisons harder.

Common Problems and Their Fixes

The chart is blank or has no useful series: Check that a numeric field is in Values and a category field is on Axis (Categories). Also confirm that a filter has not excluded every record.
Excel counts instead of sums: Inspect the source column for text values, blanks, or numbers stored as text. Correct the source values, refresh, and set the Values summary to Sum if appropriate.
New records do not appear: Refresh the PivotTable and check whether the PivotTable source includes the added rows. A table-based source generally handles expansion better than a fixed range.
Months appear in an unexpected order: Check that the source contains real dates and review whether the date field is grouped. Text labels such as Jan, Feb, and Mar may sort differently from date values.
Changing one report affects another: PivotTables or PivotCharts that share a data cache or report connection may share filtering behavior. Check the report’s connections and use a separate PivotTable or source setup if independent analysis is required.

Another common misconception is that changing a PivotChart’s filter deletes source data. Filtering hides records from the current summary; it does not remove those rows from the source list. Still, a screenshot or exported chart may not make the active filters obvious, so label the report or retain visible slicers when readers need that context.

PivotChart or Regular Chart?

Use a PivotChart when you want an interactive summary that can be rearranged and filtered with PivotTable fields. It is particularly useful for dashboards and exploratory reporting, where a reader may want to switch from sales by region to sales by product without rebuilding the chart from scratch.

Use a regular chart when you need a chart based on a carefully selected range, a specialized chart type that PivotCharts do not support, or more independent control over the plotted series. A common compromise is to build a PivotTable for reliable aggregation, then create a regular chart from a separate, deliberately maintained summary range. The extra separation gives flexibility but also means the chart’s range and any formulas need to be maintained.

Neither option fixes poor source data or answers the business question automatically. A PivotChart makes summarization easier to explore; the person building the report still needs to choose sensible categories, measures, filters, and labels.

Frequently Asked Questions

What is the difference between a PivotTable and a PivotChart?
A PivotTable summarizes and arranges records in rows and columns. A PivotChart visualizes that summary and is connected to the PivotTable’s fields and filters. They are complementary views of the analysis, not two unrelated copies of the data.

Can I make a PivotChart without a PivotTable?
You can start from the source data by choosing Insert > PivotChart. Excel creates the PivotChart report with a supporting PivotTable structure, even if you did not build a PivotTable first.

Why does my PivotChart show Count instead of Sum?
Excel may treat the value field as nonnumeric if the source column includes text, numbers stored as text, or other inconsistent entries. Correct the source, refresh the report, and check Value Field Settings to select Sum when the values are numeric.

How do I update a PivotChart after adding rows?
Refresh its PivotTable with PivotTable Analyze > Refresh or use Data > Refresh All. If the new rows are outside a fixed source range, expand or replace that source; using an Excel table as the source helps include added rows after refresh.

Can one slicer control multiple PivotCharts?
Sometimes. The reports generally need compatible PivotTables and a shared data connection or cache. Select the slicer and use its Report Connections control to see which reports it can filter; separate or incompatible PivotTables may not be selectable.

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.