Excel Pivot Chart Example: Build a Sales Dashboard

An Excel PivotChart example is a chart that summarizes sales by month and region, then lets you rearrange or filter the summary without rebuilding the chart. In Excel for Microsoft 365 on Windows, select a clean data table, choose Insert > PivotChart, and place Date on the axis, Region in the legend, and Revenue in Values.

A small sales example

Suppose a worksheet contains one record per sale, with columns for Date, Region, Product, and Revenue. The sample records below are deliberately small so you can check the totals by hand; a real workbook might contain thousands of rows.

Date	Region	Product	Revenue
Jan 5	East	Desk	$700
Jan 18	East	Chair	$500
Jan 9	West	Desk	$300
Jan 22	West	Chair	$500
Feb 3	East	Desk	$900
Feb 16	East	Chair	$600
Feb 8	West	Desk	$700
Feb 20	West	Chair	$400

A PivotChart built from these records can show clustered columns for each month, with separate series for East and West. January totals are $1,200 for East and $800 for West; February totals are $1,500 and $1,100. Across both months, the data adds up to $4,600. The chart therefore makes it easy to compare regions and spot the increase from January to February, while the associated PivotTable provides the exact figures.

What a PivotChart does

A PivotChart is a chart connected to a PivotTable. The PivotTable groups and summarizes source records, and the chart visualizes that summary. Instead of selecting a new range and editing chart series whenever you want a different view, you can move fields between the chart’s categories, series, values, and filters. The chart updates to reflect the new arrangement.

For the example, Date supplies the horizontal categories, Region separates the two colored series, and Revenue is the measure being added together. Product could be used as a filter or as another grouping field. This is different from a regular chart, which generally plots a fixed range of cells: changing the underlying summary may require adjusting the chart’s source range yourself.

The PivotChart is not a separate copy of the sales data. It depends on the source table and its PivotTable. That connection is what enables interactive filtering and rearrangement, but it also means that the source data must be structured consistently and refreshed when records change.

Prepare the source data first

Make the source range a simple rectangular list. Use one descriptive header in each column, keep each record on a single row, and avoid blank rows or merged cells inside the list. The example should have one header row followed by eight sales rows. Do not add a manually calculated total row inside the source records; a PivotTable calculates totals when it summarizes the data.

Check that Revenue contains numbers rather than text that merely looks like currency, and that Date contains real Excel dates. A date imported as text may not group into months. Similarly, consistent labels matter: East, east, and East with a trailing space can be treated as separate categories. If a source column has mixed types or missing values, clean those issues before interpreting the chart.

For an ongoing dataset, convert the range to an Excel table with Insert > Table, leaving My table has headers selected. A table normally expands when you add records directly below it, which makes it a convenient source for a PivotChart. You will still need to refresh the PivotTable after adding rows so the summary and chart include them.

Create the PivotChart step by step

Click a cell in the source data or in the Excel table. Confirm that the headers are present and that the selected source covers all the records you want to analyze.
Choose Insert > PivotChart. In the Create PivotChart dialog, confirm the table or range, choose New Worksheet for a separate report sheet if appropriate, and select OK. Depending on the Excel build and ribbon layout, PivotChart may appear in the PivotTable menu on the Insert tab.
In the PivotChart Fields pane, drag Date to Axis (Categories), Region to Legend (Series), and Revenue to Values. Excel will usually summarize numeric Revenue by Sum. If it shows Count instead, check whether the source values are stored as numbers.
To summarize by month, right-click a date label in the PivotTable and select Group. Choose Months; if the source covers more than one year, also select Years so January from different years does not get combined. Click OK.
Choose a chart type suited to the comparison. Clustered Column is a clear starting point for comparing East and West side by side in each month. The chart and its PivotTable are linked, so changes to the field layout update the visual.

Excel may choose a default chart type when the PivotChart is created. You can change it using Chart Design > Change Chart Type. The PivotChart Fields pane can be reopened if it is hidden by selecting the chart and using PivotChart Analyze > Field List.

Choose fields and a useful chart type

Think about the question before adding more fields. To compare total revenue over time, use Date on the axis and Revenue in Values. To compare regions, add Region as the legend series. To compare product mix within each region, Product can be added to the axis or used as a filter, but too many categories can make a chart difficult to read.

Clustered columns: Useful for comparing a small number of groups across months or other categories.
Lines: Useful for showing a trend across many ordered time periods. A line chart is less helpful when the categories are unordered labels such as product names.
Stacked columns: Useful for seeing both a total and its parts, though comparing the size of a middle segment across columns is harder.
Bar charts: Useful when category names are long or there are many categories.

Use Sum of Revenue when the question concerns total sales. For an average sale value, change the value calculation to Average through Value Field Settings. Averages answer a different question from totals, so label the chart accordingly. Formatting Revenue as currency improves readability, but number formatting does not alter the values or the way Excel calculates them.

Make the chart interactive

PivotChart filters let a reader focus on a subset of the source records. You can drag Product to Filters in the field pane, then use the filter control on the chart or PivotTable to select a product. For a more visible control, select the PivotChart and choose PivotChart Analyze > Insert Slicer, then select a field such as Region or Product. A slicer displays buttons that make the active selection apparent.

For date-based analysis, a timeline can provide a visual date filter when Excel recognizes the source column as dates. Select the PivotChart, then choose PivotChart Analyze > Insert Timeline and select Date. A slicer or timeline is especially useful in a dashboard where a reader needs to change the view without editing the field layout.

Keep controls purposeful. A slicer for every field can crowd the sheet and make it unclear which selections matter. If one chart is intended to show all regions, avoid leaving a hidden filter that excludes one. Check the visible filter state before sharing the workbook so readers can understand what the chart includes.

Formatting and reading the result

Give the chart a title that states the measure and grouping, such as Monthly Revenue by Region. Add axis titles if the units or time scale are not obvious. Use consistent colors for the same region across related charts, and avoid relying on color alone to communicate meaning. If the chart is crowded, remove unnecessary gridlines, reduce the number of series, or show a shorter time range.

In the sample, the columns for February are higher than January for both regions. That indicates a month-to-month increase in the summarized records, not necessarily a cause for the change or a prediction of future sales. A PivotChart describes the data selected for its source and filters. It does not establish why a pattern occurred, and it cannot correct omissions or errors in the underlying records.

Use the PivotTable when readers need exact values, and the chart when they need to see comparisons or trends quickly. Keeping both visible, or placing the chart beside a compact PivotTable, helps prevent readers from estimating precise values from bar height alone.

Refresh and troubleshoot common problems

A PivotChart does not always update immediately when source records are edited or appended. Right-click the associated PivotTable and choose Refresh, or use Data > Refresh All to update workbook connections and PivotTables. If new rows are missing, confirm that they fall inside the source table or range. For a fixed range, change the PivotTable source using PivotTable Analyze > Change Data Source, then refresh.

If the chart shows Count of Revenue rather than Sum of Revenue, inspect the Revenue column for text, blanks, or mixed data types. If dates appear as individual days instead of months, right-click a date label in the PivotTable and use Group. If Group is unavailable, check for text dates, blank cells, or invalid date entries in the source. When multiple years are present, group by both Years and Months to avoid combining the same month across years.

Other common surprises include categories appearing in an unexpected order, duplicate-looking labels caused by inconsistent spelling, and totals that differ from a separate calculation because a filter is active. Check the field filters and slicer selections, then compare the PivotTable totals against a known sample. Refreshing will not fix incorrectly entered source data; correct the source and refresh again.

PivotChart versus a regular chart

Choose a PivotChart when the report needs interactive grouping, filtering, or quick changes to which fields are compared. It is particularly useful for recurring reports where users ask questions such as whether revenue is higher by region, product, or month. The same PivotTable can support several useful views without maintaining a separate manually prepared summary for each one.

Choose a regular chart when the data is already arranged exactly as needed and the chart should remain a simple, fixed presentation. Regular charts can be easier to format in highly customized ways and are not tied to PivotTable field buttons or refresh behavior. A PivotChart may restrict or handle some chart features differently from a standard chart, so if a required design detail is unavailable, a regular chart based on a carefully prepared summary may be a better fit.

Frequently Asked Questions

What is a simple Excel PivotChart example?
A monthly revenue chart with months on the axis, regions as separate series, and revenue summed in Values is a straightforward example. Its PivotTable shows the exact totals while the chart makes the comparison visual.

How do I make a PivotChart from an existing PivotTable?
Click inside the PivotTable and choose PivotTable Analyze > PivotChart. Select a chart type and confirm. Excel creates a chart connected to that PivotTable, so its fields and filters stay linked.

Why does my PivotChart show Count instead of Sum?
Excel may count values when the source column contains text, blanks, or mixed data types. Check that every intended Revenue entry is numeric, correct the source if needed, and refresh the PivotTable.

How do I group PivotChart dates by month?
Right-click a date label in the associated PivotTable, choose Group, select Months, and confirm. If more than one year is included, select Years as well so dates from separate years remain distinct.

Does a PivotChart update when I add new rows?
It updates after its PivotTable is refreshed, provided the new records are included in the source. An Excel table usually expands as records are added beneath it; a fixed source range may need to be changed before refreshing.

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.