Microsoft Excel · Analysis and charts · Intermediate
Create and format a PivotChart report
Build a region-by-channel PivotChart, format its message, and verify filters and refreshed totals against a small sales fixture.
Official documentation reviewed; synthetic fixtures checked independently; not executed in Excel · Editorial standards
Quick answer: Select a cell in your source table, choose Insert > PivotChart, and arrange the fields so the chart answers one specific question. Verify its numbers in the accompanying PivotTable before choosing colors or a layout.
Before you begin
Use English Excel for Microsoft 365 desktop on Windows and a new practice workbook. These are Windows instructions: Mac and Excel for the web require a PivotTable first, followed by a supported chart. A PivotChart is not interchangeable with an ordinary scatter or combination chart.
Prepare a checkable source
On a sheet named Sales, enter this block at A1:C7. Each amount must be numeric.
Region Channel Amount
North Online 120
North Retail 80
South Online 90
South Retail 140
North Online 60
South Online 30
Select A1:C7, choose Insert > Table, and confirm that the table has headers. This gives the report a defined source rather than an accidental selection of part of the data. The six amounts sum to 520. If you need help with source tables and refresh, use the PivotTable source guide.
Build the report
- Select a cell in the table and choose Insert > PivotChart. Place the report on a new worksheet and select OK.
- In the field list, put Region in the category/axis area, Channel in the legend/series area, and Amount in Values. The associated PivotTable should show regions as rows and channels as columns.
- Check that the value field summarizes Amount by Sum, not Count. If the chart displays record counts, repair numeric source values and the value-field calculation before formatting.
- Use a clustered column chart to compare the two channels within each region. Select the chart and use its chart design controls to choose a readable style.
- Replace the generic title with Sales by region and channel. Label the vertical axis Amount, synthetic units; retain the Channel legend. Use clearly distinguishable series colors, but do not rely on color alone: keep the nearby PivotTable available as the precise numerical view.
The expected bars are North Online 180, North Retail 80, South Online 120, and South Retail 140. North and South each total 260. The chart does not show six independent transactions; it shows four aggregated combinations.
Prove the chart is connected
Filter the Region field to North. Both the report and chart should now represent 260, with only the North categories visible. Clear that filter before comparing the full report again. Moving Channel out of the series area removes the channel split; it does not remove the underlying records.
Change Sales!C2 from 120 to 150. Refresh the PivotTable/report, then inspect the chart again. North Online becomes 210, North total becomes 290, South remains 260, and the overall total becomes 550. A chart still showing 180 for North Online is not evidence that the edit failed: check refresh and the source table first.
Save and troubleshoot
Save the workbook as XLSX, close it, and reopen it. Check the title, legend, filter state, and 550 total. These are checks for you to perform, not a claim that Excel was run for this example.
If one series disappears, inspect the field list and filters. If the wrong regions appear, inspect the source range. If a desired chart type is unavailable, do not assume every ordinary chart type supports PivotTables. Keep the report in a supported column chart rather than turning this task into a different visualization.