Pivot Charts in Excel: Create, Customize, and Refresh

To make a PivotChart in Excel for Microsoft 365 on Windows, select a cell in a clean data table, choose Insert > PivotChart, confirm the data range and where to place the chart, then use the PivotTable Fields pane to arrange fields. Excel creates a PivotTable with the chart, so you can summarize and filter the same data interactively.

What a PivotChart Does

A PivotChart is a chart linked to a PivotTable. Rather than plotting every source row separately, it visualizes the PivotTable’s grouped and summarized results. For example, a sales list with thousands of transactions can become a chart of total sales by month, product, or region. Dragging a field to a different area changes the summary and updates the chart.

The link is useful when a chart needs to answer more than one question. A regular chart is usually tied to a fixed range of cells; a PivotChart is designed to work with PivotTable fields, filters, and grouping. You can switch the displayed categories, compare measures, or filter out selected items without rebuilding the chart from scratch.

A PivotChart is not a separate kind of source data or a replacement for a PivotTable. It is the visual companion to a PivotTable, and both depend on the same source records and PivotTable cache. Understanding that connection helps explain why changing a field affects both the chart and its underlying summary.

Prepare the Source Data

Good source data makes the PivotChart easier to create and maintain. Arrange records in a rectangular list: one header row followed by one record per row, with each column representing a single field. For instance, a sales list might have Date, Region, Product, Units, and Revenue columns. Each transaction should occupy one row.

Give every column a distinct, nonblank header, such as Region or Revenue.
Avoid merged cells, blank rows or columns within the list, and subtotals inserted between records.
Keep each column consistent. Dates should be dates, quantities should be numbers, and categories should be text.
Check for accidental spaces or spelling variations in category names, such as North and North . Excel may treat these as separate items.

For a source that will grow, convert the list to an Excel Table first. Select a cell in the data and choose Insert > Table, confirm that My table has headers is selected, and choose OK. A table can expand when new rows are added, which is generally more dependable than a fixed source range. You still need to refresh the PivotTable after adding records so the chart includes them.

Create the PivotChart Step by Step

Click any cell in the source table or data list. If the records are not an Excel Table, check that Excel has selected the complete range, including headers.
On the ribbon, choose Insert > PivotChart. In the Create PivotChart dialog, check the Table/Range value and choose whether to place the PivotTable and chart on a New Worksheet or an Existing Worksheet.
Choose OK. Excel creates the chart and its associated PivotTable, then displays the PivotTable Fields pane. If the pane is hidden, select the PivotTable or chart and use the field-list command on the PivotTable Analyze or PivotChart Analyze tab.
Drag fields into the areas in the pane. Put a category such as Product in Axis (Categories), and a numeric field such as Revenue in Values. Add Region to Legend (Series) to compare regions, or place a field in Filters to filter the full report.
Review the chart title, axes, and totals. Choose a chart type that fits the comparison, then resize or move the chart as needed.

Excel may choose a default chart type and summarize numeric values by Sum. If the Values area shows Count instead, the source column may contain text, blanks, or numbers stored as text. Check the source data type, correct it if appropriate, and refresh the PivotTable. You can also open the field’s value settings to choose a supported summary such as Sum, Count, or Average.

Arrange Fields to Answer a Question

The four field areas determine what the chart says. Axis (Categories) supplies the labels along the chart’s category axis. Legend (Series) divides the data into colored series. Values supplies the measurements, and Filters restricts which records appear. The pane labels can differ slightly depending on the chart and Excel version, but the basic roles remain the same.

Imagine a transaction table with Date, Region, Product, and Revenue. To compare monthly revenue across regions, put Date on the category axis, Revenue in Values, and Region in Legend. Excel may group dates into years, quarters, or months; inspect the labels to make sure the time scale matches the question. To compare product totals instead, move Product to the category axis. There is no need to rebuild the chart.

Field placement can also change the level of detail. Putting Product on the axis may create many categories, while putting it in a filter lets a reader focus on one product at a time. If a chart looks crowded, reduce the number of categories or use a filter rather than shrinking the labels until they become unreadable.

Choose a Chart Type and Improve Readability

Choose the visual form according to the comparison you want readers to make. Clustered columns or bars work well for comparing categories. A line chart is often suitable for a sequence of dates, especially when the goal is to see a trend. A stacked chart shows how series contribute to a total, but can make precise comparisons between middle segments difficult. Pie charts are most readable when there are only a few categories that make up one whole.

Use the chart’s contextual tabs to make adjustments. Select the chart and use Chart Design to change the chart type, layout, or style; use Format to adjust elements and appearance. The plus-shaped Chart Elements button beside a selected chart can add or remove items such as a title, axis titles, data labels, and a legend. Give the chart a specific title, for example Monthly Revenue by Region, rather than leaving a generic title.

Some chart types are unavailable for PivotCharts or do not work like their regular-chart counterparts. If a desired chart type is missing or an advanced layout is blocked, create a normal chart from a separate summary range instead. Also avoid adding too many series: a chart with numerous colors may be technically correct but difficult to interpret.

Filter Interactively with Buttons and Slicers

PivotChart field buttons let viewers filter or rearrange fields directly on the chart. To reduce visual clutter, select the chart and choose PivotChart Analyze > Field Buttons > Hide All. Hiding buttons changes the chart display, not the underlying PivotTable fields. You can restore them from the same menu when direct chart controls are useful.

For more visible filtering, select the PivotTable or PivotChart and choose PivotTable Analyze > Insert Slicer, then select fields such as Region or Product. A slicer presents clickable filter buttons. For date fields, choose Insert Timeline when that command is available and the source field contains valid dates. Slicers and timelines are especially helpful on a dashboard, where a reader may not know how to use field dropdowns.

If one slicer should control more than one PivotTable, the PivotTables generally need to share a compatible source or cache. Select the slicer and use the Report Connections command to choose connected PivotTables. If a report is absent from that list, check how its PivotTable was created; independently created reports may not share a cache.

Refresh, Update, and Share the Chart

A PivotChart does not automatically recalculate its source records every time you edit the source list. To update it, select its PivotTable and choose PivotTable Analyze > Refresh, or right-click within the PivotTable and select Refresh. Alt+F5 refreshes the selected PivotTable in Windows; Ctrl+Alt+F5 refreshes all connections and PivotTables in the workbook. On a Mac, use the Data tab’s refresh commands if the Windows shortcuts are unavailable or different.

If new rows are not included, check the source range. An Excel Table usually expands when records are appended directly below it, but the PivotTable still needs a refresh. For a fixed-range source, use PivotTable Analyze > Change Data Source and select the updated range, or convert the source to a Table for future additions.

Save the workbook as an Excel workbook if it needs to retain PivotTable functionality. When sharing, consider whether recipients need to interact with the filters, whether the source data should remain in the file, and whether the chart needs a concise explanation. Excel for the web can work with many PivotTable and chart features, but some creation and formatting options may differ from desktop Excel; use the Windows desktop app for the steps and full ribbon controls described here. Excel for Mac has similar PivotTable and chart workflows, although tab names and available commands can vary by release.

Common Problems and Practical Workarounds

The chart is blank or shows unexpected totals: Verify that the intended field is in Values and that its source entries are valid numbers. A text-formatted number may be counted rather than summed.
New data does not appear: Refresh the PivotTable and confirm that the source range includes the added records. Tables help the source range grow, but do not remove the need to refresh.
Categories appear more than once: Look for inconsistent labels, hidden spaces, or different data types. Correct the source values and refresh.
Dates are not grouped as expected: Confirm that every entry is a real Excel date and that the date column contains no text or blank entries that prevent grouping. Right-click a date in the PivotTable to inspect grouping options.
The chart will not accept a preferred chart type: PivotCharts have chart-type limitations. Build a small, separate summary table and use a regular chart if the required type or customization is unsupported.

A useful workaround for a presentation-ready chart is to preserve the PivotTable for analysis, then create a normal chart from a carefully chosen summary range when you need a chart type or layout that PivotCharts cannot provide. The trade-off is that the ordinary chart may not respond to PivotChart field controls in the same way, so plan how its summary cells will update.

Frequently Asked Questions

What is the difference between a PivotTable and a PivotChart?
A PivotTable summarizes data in rows and columns. A PivotChart visualizes that summary and shares its fields and filters. They are linked, so changing the PivotTable’s arrangement can change the chart.

Can I make a PivotChart without a PivotTable?
Excel creates an associated PivotTable when you create a PivotChart. You can place the PivotTable on another worksheet or hide the worksheet, but the chart still relies on the PivotTable structure.

How do I add a slicer to a PivotChart?
Select the PivotTable or PivotChart, choose PivotTable Analyze > Insert Slicer, select one or more fields, and choose OK. Click slicer buttons to filter the connected report.

Why does my PivotChart show Count instead of Sum?
Excel commonly uses Count when a Values field contains text or other entries it cannot treat consistently as numbers. Check the source column, convert valid numeric entries to numbers, refresh, and then review the field’s value settings.

Can I use a PivotChart in Excel for the web or on a Mac?
Many PivotTable and chart tasks are available in Excel for the web and Excel for Mac, but commands and editing options can vary by version. If a ribbon command described here is missing, open the workbook in desktop Excel for Microsoft 365 on Windows and use the corresponding PivotTable Analyze or PivotChart Analyze controls.

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.