Excel Pivot Chart Tutorial: Create and Customize Charts

To create a PivotChart in Excel for Microsoft 365 on Windows, select a clean data range or Excel table, choose Insert > PivotChart, select where to place it, and use the PivotChart Fields pane to arrange categories and values. A PivotChart is linked to a PivotTable, so you can summarize, filter, and explore the same data without rebuilding the chart for every view.

What a PivotChart Does

A PivotChart is a chart connected to a PivotTable. The PivotTable groups and summarizes records, while the chart displays those results visually. For example, a sales list with one row per transaction can become a column chart of total sales by product, with regions shown as separate series. Move Region to Filters or add a slicer, and the chart can focus on one region while the underlying summary updates.

This link is useful for interactive analysis, but it also creates an important distinction from a regular chart. A standard chart usually points directly at a cell range; a PivotChart gets its plotted categories and values from PivotTable fields. Changing the PivotChart layout changes the associated PivotTable layout as well. You generally cannot edit a PivotChart as if it were an independent chart with arbitrary cell references.

Prepare the Source Data

Good source data makes the chart easier to build and less likely to break. Arrange records in a rectangular table: one header row, one record per row, and one type of information per column. A sales dataset might have Order Date, Region, Product, Units, and Sales columns. Each transaction should occupy its own row, rather than putting monthly totals or subtotals between transaction rows.

Give every column a distinct, nonblank header, such as Sales or Order Date.
Remove completely blank rows and columns inside the data area, and avoid merged cells in the source.
Keep a column consistent: dates should be dates, amounts should be numbers, and category labels should be text.
Do not add manual subtotal rows to the source; let the PivotTable summarize the detail.

For data that will grow, convert the range to an Excel table first. Select a cell in the data and use Insert > Table, confirm that the table has headers, and select OK. On Windows, Ctrl+T also opens the Create Table dialog; on Mac, Control+T is commonly used, though keyboard behavior can depend on the keyboard and Excel settings. A PivotTable based on an Excel table can include newly appended table rows after a refresh. A PivotTable based on a fixed cell range will not automatically extend its source range when rows are added outside it.

Create a PivotChart Step by Step

Select a cell in the source table or data range. If the source is an Excel table, selecting any cell in it is enough.
On the ribbon, choose Insert > PivotChart. In the Create PivotChart dialog, check the table or range and choose whether to put the chart and its PivotTable on a New Worksheet or an Existing Worksheet. Select OK.
In the PivotChart Fields pane, drag fields into the areas at the bottom. Put a category such as Product in Axis (Categories), a measure such as Sales in Values, and an optional grouping such as Region in Legend (Series). A field placed in Filters filters the whole report.
Choose a chart type that suits the comparison. A clustered column chart is useful for comparing categories; a line chart can show a time trend; a bar chart can make long category names easier to read.
Use the field buttons on the chart or the PivotChart Fields pane to filter and rearrange the view. If the pane is hidden, select the chart and use PivotChart Analyze > Field List.

Excel may place numeric fields in Values and text fields in Axis automatically, but check the result rather than assuming the layout is right. If Sales appears as a list of labels instead of a total, move it to Values. If a field is counted rather than summed, right-click one of its values in the PivotTable, choose Summarize Values By, and select Sum. Alternatively, open Value Field Settings from the field menu. A field containing numbers stored as text or blanks can lead Excel to choose Count instead of Sum.

Choose Fields and Summaries That Answer a Question

Begin with a specific question, then assign fields according to their role. For example, suppose a store wants to compare sales by product and region. Put Product in Axis (Categories), Sales in Values, and Region in Legend (Series). To compare only one region at a time, put Region in Filters instead. These are different views: a legend creates multiple plotted series, while a report filter narrows the whole chart to the selected region.

Values can be summarized in several ways. Sum is appropriate for additive measures such as revenue or units. Count answers how many nonblank entries are present, and Average can help compare typical values. Right-clicking a value in the associated PivotTable and choosing Show Values As also exposes calculations such as percentage of a total or difference from a prior item. These display calculations do not change the source records; they change how the summarized values are presented.

Dates can often be grouped into months, quarters, or years. In the PivotTable, right-click a date item, choose Group, and select the periods needed. If Group is unavailable or Excel gives an error, inspect the source date column for text dates, blank cells, or invalid entries. Grouping is most reliable when every entry is a real Excel date.

Filter with Slicers and Timelines

For filters people can see and click, add a slicer. Select the PivotChart or its PivotTable, then choose PivotChart Analyze > Insert Slicer, select fields such as Region or Product, and select OK. Clicking a slicer button filters the connected report; use the clear-filter icon on the slicer to remove that selection. Slicers are particularly helpful on a dashboard because they make the current filter state more visible than a small drop-down.

For a date field, use PivotChart Analyze > Insert Timeline when that command is available. A timeline provides a date-range control that can be viewed by years, quarters, months, or days. If a slicer or timeline needs to control more than one PivotTable, select it and open Report Connections, also called PivotTable Connections in some contexts. The reports generally need to share a compatible PivotTable cache or data source. If a second report is not listed, its source or cache may differ, so the control cannot connect to it as-is.

Format the Chart for Clear Reading

Once the fields answer the question, improve the chart’s readability. Select the chart and use the Chart Design and Format tabs to change its appearance. Chart Design includes chart styles and chart elements; use Add Chart Element to add or adjust items such as a chart title, axis titles, data labels, and legend. A clear title such as Sales by Product and Region tells readers what is being compared, while axis titles can clarify units such as dollars or units sold.

Choose formatting that supports comparison rather than decoration. Keep related series visually consistent, use readable labels, and avoid showing so many categories that the chart becomes crowded. If category names are long, a horizontal bar chart may be clearer than narrow columns. If there are many dates, show a sensible time grouping instead of every individual day. Check the chart after filtering: a title or axis scale that worked for the full dataset may not communicate the filtered view as clearly.

PivotCharts have some chart-type restrictions compared with regular charts. Certain types, including XY (Scatter), stock, and bubble charts, are not available as PivotCharts in Excel. Some specialized combinations and formatting options are also more limited. If the analysis requires an unsupported chart type, build a regular chart from a suitable summary range or copy the PivotTable results to a separate range and chart those values.

Refresh and Keep the Chart Current

A PivotChart reflects the PivotTable’s cached summary, so edits to source records do not always appear immediately. Right-click the PivotTable and choose Refresh, or select the PivotTable and use PivotTable Analyze > Refresh. To refresh all PivotTables in the workbook, use Data > Refresh All. The chart updates with its associated PivotTable when that report refreshes.

Refreshing and expanding the source are separate issues. If new records were added within an Excel table, refresh the PivotTable to include them. If the source was a fixed range and the new records sit below that range, refresh alone may not include them. Change the source through PivotTable Analyze > Change Data Source, or create the report from an Excel table next time. When sharing a workbook, explain whether readers should refresh it and whether the source data is included, connected externally, or subject to access permissions.

PivotChart vs. Regular Chart

Choose a PivotChart when readers need to rearrange fields, filter categories interactively, or explore a large dataset through a PivotTable summary. It keeps the chart and summary connected, which makes a field change quick. Choose a regular chart when you need precise control over a fixed set of cells, specialized chart types, or a design that should not change when someone rearranges PivotTable fields.

A practical alternative is to create a PivotTable for analysis, then make a regular chart from a separate summary range when the presentation needs chart behavior PivotCharts do not support. That approach may require maintaining the summary range and chart separately, but it gives more flexibility. For a small, stable dataset with no need for interactive grouping, a normal chart may be simpler and easier to maintain.

Common Problems and Fixes

The chart shows Count instead of Sum: Check Value Field Settings and the source column’s data types. Numeric-looking text or nonnumeric entries can make a field count instead of sum.
New rows are missing: Refresh the report, then check whether the source is an Excel table or a fixed range. Expand or change a fixed source range if needed.
The date field will not group: Look for blanks, text values, or invalid dates in the source column. Correct them, refresh, and try grouping again.
A slicer does not filter another report: Open Report Connections and check whether the other PivotTable is listed. Reports built from different sources may not share a compatible cache.
Filters or field buttons clutter the chart: Use the field list or PivotChart Analyze controls to hide chart field buttons when they are not needed. Keep slicers only for filters readers should actively use.

Frequently Asked Questions

How do I create a PivotChart in Excel?

Select a cell in the source data, choose Insert > PivotChart, choose the destination in the dialog, and select OK. Then drag fields into Axis (Categories), Legend (Series), Values, or Filters in the PivotChart Fields pane.

Can I create a PivotChart without a PivotTable?

A PivotChart is tied to a PivotTable, even if the PivotTable is placed on another worksheet or its details are not prominent. If you need a chart with no PivotTable-based field layout, use a regular chart instead.

Why is my PivotChart not updating?

Refresh the associated PivotTable using PivotTable Analyze > Refresh or Data > Refresh All. If newly added rows are still absent, check whether the PivotTable source range includes them; a fixed range may need to be expanded.

Can one slicer control multiple PivotCharts?

Often, yes, when the PivotTables behind the charts share a compatible source or cache. Select the slicer and use Report Connections or PivotTable Connections to choose the reports it should filter.

Can I use a PivotChart in Excel for Mac or Excel for the web?

Excel for Mac includes PivotTable and PivotChart features, though ribbon labels or available controls can differ from Windows. Excel for the web supports many workbook and PivotTable tasks, but its editing options are not identical to desktop Excel; use the desktop app if a needed PivotChart command is unavailable.

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.