Excel Pivot Chart Multiple Lines: How to Create Them

Yes. To show multiple lines in an Excel PivotChart, create a PivotChart from your PivotTable, choose a line chart, put the time or other category field in Axis (Categories), the field that separates the lines in Legend (Series), and the numeric field in Values. For example, place Month on the axis, Region in the legend, and Sales in Values to draw one sales line per region.

What multiple lines mean in a PivotChart

A line chart displays values across an ordered set of categories, commonly dates or months. Each series is drawn as a separate line. In a PivotChart, the fields in the PivotTable determine those categories and series: a field in Axis (Categories) supplies the horizontal positions, while a field in Legend (Series) divides the values into separate lines. Values supplies the numbers plotted on the vertical axis.

The distinction matters because a PivotChart does not infer which field should become a line. If you place Region on the axis and Month in the legend, Excel will make a line for each month across regions, which is usually the reverse of what someone tracking regional trends intends. You can drag fields between areas in the PivotTable Fields pane to change the chart’s structure without rebuilding the source data.

Multiple lines are useful when the series share the same units and can be compared on the same scale—for example, monthly revenue by store, support tickets by department, or temperatures by sensor. If one series is in dollars and another is a percentage, plotting both on one vertical scale can be misleading; consider separate charts or a more suitable chart design instead.

Prepare the source data first

PivotCharts summarize a source dataset through a PivotTable, so the source should be a clean rectangular list. Use one header row with a distinct name for every column, and put one record in each row. Avoid merged cells, blank header names, and subtotal rows inside the source range. A useful layout for regional sales is:

Date: a real Excel date for the transaction or reporting period.
Region: a consistent label such as North, South, East, or West.
Sales: a numeric amount, not a number stored as text.

It is fine for a region and month to appear on many rows. The PivotTable can combine those records, usually by summing Sales. If your data is already summarized to one row per month and region, that works too, provided the column headings and values are consistent. Convert a growing data range to an Excel Table with Insert > Table before creating the PivotTable. A table expands when new rows are added, making it easier for a later refresh to include them.

Create a PivotChart in Excel for Microsoft 365

In Excel for Microsoft 365 on Windows, first click inside the source data or its Excel Table. Choose Insert > PivotTable, confirm the table or range, choose where the PivotTable should go, and select OK. After the PivotTable is created, click inside it and choose PivotTable Analyze > PivotChart. In the chart dialog, select Line, choose a line subtype, and confirm.

In the PivotTable Fields pane, drag your date or category field to Axis (Categories).
Drag the field that should distinguish the lines—such as Region or Product—to Legend (Series).
Drag the numeric measure, such as Sales or Units, to Values.
Check the chart title, axis labels, and legend to verify that the intended series appear.

You can also create the PivotTable and PivotChart together from the source using the PivotChart command when it is available on your Insert ribbon. The exact ribbon arrangement can vary with window size and Excel updates. On Excel for Mac, PivotTables and PivotCharts are supported, but ribbon placement and some dialog details can differ. Excel for the web may not expose every desktop layout or chart control; use the desktop app if a needed field or formatting command is missing.

Choose the right fields for each line

Suppose the source contains Date, Region, and Sales. Put Date in Axis (Categories), Region in Legend (Series), and Sales in Values. Excel groups the records for each region at each date and plots the resulting totals. If you want a line per product instead, replace Region in the legend with Product. If you want only selected regions, filter the Region field or use a slicer.

A common confusion is expecting every field to become a line. Fields in the legend create the separate series; fields in the axis create positions shared by those series. A field in Filters limits which records contribute but does not create another line. You can also place a second category field in the axis area, but multiple axis fields can create a hierarchy that is harder to read. For a simple trend chart, keep the horizontal axis to one clear time or category field.

Check the calculation shown under Values. Excel may choose Sum for numeric data, but it can choose Count when the source contains numbers stored as text or mixed data. Open the Values field menu, choose Value Field Settings, and select an appropriate summary such as Sum or Average. Use Average for measures where adding individual records would not make sense, and confirm the choice against the question your chart is meant to answer.

Example: monthly sales by region

Imagine a table with thousands of sales transactions, each carrying a transaction date, region, and sale amount. A PivotChart can reduce those rows to a small set of monthly totals. With Date on the axis, Region in the legend, and Sale Amount in Values summarized by Sum, the chart displays one line per region and one plotted value for each month.

If the axis shows individual transaction dates rather than months, right-click a date in the PivotTable and choose Group, then select the periods you want, such as Months. Grouping is available when Excel recognizes the entries as dates. If the dates are text, or the column contains blanks or invalid dates, grouping may fail; correct the source values first. If the data spans multiple years, include Years as well as Months when grouping so that January from different years is not treated as a single period.

The same design works for other questions: weekly orders by channel, quarterly costs by department, or daily website visits by campaign. The line represents the comparison group, not necessarily an individual record. A chart with dozens of products may become crowded, so filter to a few important products or use small separate charts rather than forcing every category into one legend.

Make the chart easier to read

Give the chart a title that states both the measure and grouping, such as “Monthly Sales by Region.” Use a visible legend when the lines are not directly labeled, and keep axis labels concise. To change the chart type or presentation, select the chart and use Chart Design > Change Chart Type or the chart design commands for titles, legends, and other elements. Ribbon commands can vary slightly with the selected chart and Excel build.

Choose line markers when individual monthly values matter or the series has only a few points. For long daily or hourly histories, markers at every point can clutter the view, so a plain line may be clearer. Keep the vertical axis consistent when comparing series; automatically changing the scale can make small differences look larger than they are. If the lines overlap, filtering to a smaller group, changing colors, or using separate charts can improve readability without changing the underlying summary.

Also consider whether a line chart is appropriate for the category order. Dates and ordered stages usually suit lines. Unordered categories such as unrelated product names may be easier to compare with a bar chart. A line connects neighboring points, so it can imply continuity; do not use it to suggest a continuous trend where the categories have no meaningful order.

Filter, refresh, and keep the chart current

A PivotChart is connected to its PivotTable. Filtering a field in the PivotTable or chart changes which records are summarized, and the chart updates to reflect the current view. For an interactive report, select the PivotTable and use PivotTable Analyze > Insert Slicer to add buttons for a field such as Region or Product. A date field can also be controlled with a timeline when that command is available. A slicer connected to the PivotTable filters its associated PivotChart as well.

When you add or edit source records, refresh the PivotTable to recalculate the summary. Click inside it and choose PivotTable Analyze > Refresh, or use Data > Refresh All to refresh workbook connections and PivotTables. If new rows are outside a fixed source range, a refresh alone will not include them. Using an Excel Table as the source helps because the table expands as records are appended; then refresh the PivotTable.

For a report shared with others, leave a brief note explaining the measure, date range, and any active filters. A chart that shows only a subset of regions can otherwise be mistaken for a complete comparison. If the chart is intended for printing or a presentation, check the slicer selections and filters before exporting or copying it.

Common problems and practical fixes

Only one line appears: Confirm that the intended grouping field is in Legend (Series), not only in Filters or Values. Check that the source actually contains more than one distinct group.
The chart has one line per month: The fields are likely reversed. Put Month in Axis (Categories) and Region or Product in Legend (Series).
The line shows counts instead of totals: Open Value Field Settings and choose the correct summary. If Sum is unavailable or produces unexpected results, check for numbers stored as text or nonnumeric entries in the source.
Months appear in an odd order: Use genuine date values and group the date field. Text labels such as “Apr” and “Feb” may sort alphabetically rather than chronologically; a real date field avoids that ambiguity.
Some months are missing: The source may have no records for those periods. A PivotTable generally summarizes existing records; it does not automatically invent missing observations. Decide whether a missing period should mean no activity, zero, or unavailable data before filling gaps or interpreting the line.
New records do not show: Check the PivotTable source range, make sure appended rows are included, and refresh. A source Excel Table can reduce range-maintenance problems.
The chart looks crowded: Reduce the number of series with a filter, use a wider chart, or split the comparison into multiple charts. Too many similarly colored lines make values difficult to distinguish.

PivotChart or regular chart?

A PivotChart is a good choice when the data needs interactive grouping, filtering, and summarization. It stays associated with a PivotTable, so changing fields can alter the categories, series, or measure without manually rebuilding a chart range. This is especially useful for recurring reports where someone may want to switch from Region to Product or narrow the view to selected dates.

A regular chart can be preferable when the plotted values come from a custom calculation, a carefully arranged helper table, or a layout that should not change when someone filters a PivotTable. It offers a more direct relationship to a cell range, although you may need to maintain that range or create the summary yourself. PivotCharts also have some differences in available chart controls compared with ordinary charts. If Excel does not offer the precise chart type or formatting behavior you need, build a regular chart from a separate summary table instead.

Neither option is automatically more accurate. The important checks are whether the source data is complete, whether the aggregation is right, and whether the axis and series answer the intended question. A polished chart can still mislead if it sums a measure that should be averaged or omits filtered-out categories without making that clear.

Frequently Asked Questions

Can an Excel PivotChart have more than two lines?
Yes. Each distinct item in the field placed in Legend (Series) can become a separate line, subject to the data and filters. Many series may make the chart difficult to read, so consider limiting the categories shown.

How do I add another line to an existing PivotChart?
Click the chart to show the PivotTable Fields pane, then drag the grouping field you want to compare into Legend (Series). If the field is already there, check whether its items are filtered out.

Can I make one line for each year?
Yes. Put a Year field in Legend (Series) and a month or date field on the axis. If your source has a full date column, group it by Years and Months or add suitable year and month fields to the source before creating or refreshing the PivotTable.

Why does Excel show Count instead of Sum?
Excel may detect the Values field as text or mixed data. Check the source column for text-formatted numbers, blanks, or labels, correct the data where appropriate, refresh, and choose Sum in Value Field Settings.

Will a PivotChart update when I add rows?
It updates after the PivotTable is refreshed, provided the new rows are part of its source. An Excel Table is a practical source because it expands as rows are added, but you still need to refresh the PivotTable to recalculate its results.

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.