Multi-Series Pivot Chart in Excel: Setup and Tips

A multi-series PivotChart displays two or more related data series in one chart, usually by placing a category field such as Region or Product in the Legend (Series) area of the PivotChart Fields pane. In Excel for Microsoft 365 on Windows, select a cell in your source data, choose Insert > PivotChart, then arrange your category, series, and numeric fields in the chart fields areas.

What a multi-series PivotChart shows

A chart series is a set of values plotted together, such as monthly sales for one region. A multi-series chart puts several such sets on the same axes so you can compare them. For example, a sales table with Date, Region, Product, and Sales columns can produce a chart with months along the horizontal axis, sales on the vertical axis, and a separate series for each region.

PivotCharts are linked to PivotTable-style summaries. Excel groups and aggregates the source records according to the fields you place in the chart layout. If the source contains hundreds of individual transactions, the chart can show summarized sales by month and region rather than plotting each transaction as its own point. You can change the grouping, aggregation, and filters without rebuilding the source data.

The phrase multi-series describes the chart’s structure, not a special Excel chart command. You can make a multi-series PivotChart with several categories in one field, or with more than one numeric measure. Those are different ways to create series, and the best choice depends on the comparison you want readers to make.

Prepare the source data

Good source data makes a PivotChart easier to build and maintain. Arrange records in a simple rectangular list: one header row, one record per row, and one kind of information per column. Avoid merged cells, blank header names, and subtotal rows mixed into the records. For the sales example, each row might represent one product sale on one date in one region, with a numeric sales amount.

Before inserting the chart, check these details:

Use distinct, descriptive column headers, such as Date, Region, and Sales. Duplicate or empty headers can make the field list confusing.
Store dates as real Excel dates and amounts as numbers. Text-formatted dates or amounts may not group or summarize as expected.
Keep each column’s data type consistent. For example, do not place notes such as N/A in the same numeric column as sales figures unless you have a deliberate way to handle them.
Consider converting the list to an Excel table with Insert > Table. A table can expand as rows are added, and a PivotTable or PivotChart based on it can use the expanded source after a refresh.

Decide what one plotted point should represent before building the chart. If the goal is monthly sales by region, the point is the total sales for one region in one month. If you instead want average order value, the Values field must summarize sales using an average and the source must contain the appropriate transaction-level values.

Create the chart and assign its fields

On Windows, click a cell in the source list and select Insert > PivotChart. Choose the data range and where to place the PivotChart and its associated PivotTable if Excel asks, then confirm. Excel inserts the chart and opens the PivotChart Fields pane. If a PivotTable already exists, select a cell in it and use PivotTable Analyze > PivotChart to create a chart from that summary.

For monthly sales by region, arrange the fields like this:

Drag Date to Axis (Categories), which supplies the horizontal-axis categories.
Drag Region to Legend (Series), which creates a separate series for each region in the current selection.
Drag Sales to Values, which supplies the numbers to plot. Excel usually summarizes a numeric field by Sum; check the field’s summary if the intended measure is different.
Optionally drag Product to Filters if you want to select a product without making every product a separate series.

Excel may label the areas Axis (Categories), Legend (Series), Values, and Filters. Dragging a field to a different area changes the question the chart answers: putting Region on the axis makes regions the categories, while placing it in the legend makes regions the series. With several numeric fields, Excel can plot each measure as another series; the exact arrangement can be adjusted by moving fields, including the Values field, between the available areas.

To group daily dates into months, use the associated PivotTable: right-click a date in the table, choose Group, and select Months. If the data covers more than one year, include Years as well, so January in one year is not combined with January in another. Date grouping can fail when the source date column contains text, blanks, or invalid dates; correct those source entries and refresh.

Choose a chart type that supports the comparison

For a small number of series, a clustered column chart is a straightforward way to compare values across categories. Each region’s column sits beside the others for the same month. A line chart is often more readable when the categories are ordered over time and the main question is how each region’s trend changes. Use lines only when connecting the points communicates a meaningful sequence, such as months or dates.

A stacked column chart shows how series contribute to a combined total in each category. It is useful for composition, but the middle series do not share a common baseline, so comparing their exact values across categories is harder. A 100% stacked chart compares proportions rather than totals. It should not be used when the absolute size of each category is the main message.

Chart type availability and behavior can vary with PivotChart features and Excel builds. If the chart type you need is unavailable for a PivotChart, or you need a specialized combination such as independent scales or chart-specific data editing, create a regular chart from a separate summary range instead. Do not choose a chart merely because it is available: a chart with many series can become unreadable even if Excel can draw it.

Keep series readable and meaningful

Every distinct value in the Legend (Series) field can add another series. If Region has three values, the chart may have three series; if Product has 30 values, putting Product in the series area can produce a crowded legend and too many colors. Use a filter to narrow the products, or summarize them into a smaller set of meaningful groups before charting.

Use consistent units and scales. Plotting revenue and units sold together on one ordinary value axis can make one measure look nearly flat because their magnitudes differ. A secondary axis can sometimes help, but it can also make comparisons misleading. A clearer option is often two charts, or a normalized comparison such as an index, provided the transformation is explained.

Give the chart an informative title and label the units. Select the chart and use Chart Design > Add Chart Element to add or adjust elements such as axis titles and a legend. Check that series names are recognizable: Excel typically derives them from field items or value-field names, and unclear source labels will carry through. Avoid relying on color alone when the chart will be read by people with color-vision differences; labels, patterns where available, and clear legends can help.

Filter, refresh, and interact with the chart

PivotCharts can be filtered using the associated field layout, chart field buttons when displayed, and slicers. A slicer provides clickable buttons for fields such as Region or Product. Select the PivotChart or its associated PivotTable and use PivotTable Analyze > Insert Slicer, select the desired field, and choose OK. A slicer can make a report easier to explore, but it does not change the underlying source records.

When the source data changes, refresh the PivotTable or PivotChart so the summary reflects those changes. Use PivotTable Analyze > Refresh for the selected PivotTable, or Data > Refresh All to refresh workbook connections and PivotTables. If new records fall outside a fixed source range, a refresh alone may not include them. Using an Excel table as the source helps the range expand; verify the PivotTable data source if newly added rows do not appear.

Filtering the associated PivotTable affects the linked PivotChart because the chart reflects that summary. If you copy or create multiple PivotCharts from PivotTables that share a PivotCache, changing one report’s filters may affect another report as well, depending on how the reports are connected. For dashboards with independent filtering, use separate PivotTables or configure slicer connections carefully using the available Report Connections options.

Common problems and practical fixes

The chart has only one series. Check that a field with multiple items is in Legend (Series), not only in Axis (Categories). Confirm that filters have not reduced the field to one item.
The chart has too many series. Move a high-cardinality field out of Legend (Series), apply a filter or slicer, or group the source categories into a smaller set. A long legend is a sign that the chart may not be the right comparison.
Values are counted instead of summed. In the PivotTable, open the drop-down for the value field, choose Value Field Settings, and select Sum if the source values are numeric and a total is intended. Count may be used when Excel treats the entries as text or when the field’s current summary is set to Count.
Months appear in the wrong order. Use real dates and group them in the PivotTable. Text labels such as Apr, Aug, and Dec may sort alphabetically rather than chronologically.
New source records are missing. Check the source range and refresh. If the PivotTable uses a fixed range, update its source or base it on an Excel table that includes the new rows.
Formatting or categories change after refresh. Refreshing updates the PivotTable summary and can expose new items or remove old ones. Review the axis, legend, and filters after refresh, especially when the source has changing categories.

A useful diagnostic is to inspect the PivotTable behind the chart. If its rows, columns, values, or filters do not represent the intended summary, changing chart colors will not fix the underlying issue. Correct the field arrangement first, then format the chart.

PivotChart versus a regular chart

A PivotChart is best when readers need to reorganize, filter, or refresh a summarized view interactively. Its series and categories are controlled by PivotTable fields, and its chart updates as that layout changes. This connection is also a constraint: you generally do not edit a PivotChart’s plotted data by changing a normal chart data range, because its data comes from the PivotTable structure.

A regular chart is often preferable when the plotted range must be precisely controlled, when you want a custom helper table, or when the chart needs features not supported by the PivotChart workflow. One approach is to build a PivotTable for reliable aggregation, copy or link the desired summary into a separate range, and create a regular chart from that range. The separate chart will not automatically behave like a PivotChart; you must ensure its source range and formulas include new categories and values.

In short, use a PivotChart for an interactive analysis that follows the PivotTable fields. Use a regular chart when the final visual needs more control than the PivotChart structure allows. Both can be useful in the same workbook: the PivotTable can support exploration, while a regular chart can present a carefully selected, stable view.

Frequently Asked Questions

How do I add multiple series to a PivotChart?
Place a field with several items, such as Region, in Legend (Series), and place the numeric measure in Values. Excel creates a separate series for each included item.

Can one PivotChart show two different measures?
Yes. Add both numeric fields to Values. Excel can plot them as separate series, but check their units and scales; measures with very different magnitudes may be clearer in separate charts.

Can I make a PivotChart with multiple series from dates?
Yes. Put a date field on Axis (Categories), group it by a useful interval such as months, and put the comparison field, such as Region, in Legend (Series). Include Years when the data spans multiple years.

Why does my PivotChart show Count instead of Sum?
Excel may be treating the source values as text, or the value field may be set to Count. Check the source data type and use Value Field Settings in the associated PivotTable to choose Sum when a total is appropriate.

Can I change the data range of a PivotChart directly?
Not in the same way as a regular chart. A PivotChart is driven by its associated PivotTable and field layout. Change the source or PivotTable structure, or create a regular chart from a separate summary range when direct range control is needed.

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.