Pivot Chart Line Graphs in Excel: How They Work

Yes. In Excel, a PivotChart line graph displays values from a PivotTable as a line chart, making it useful for showing how a measure changes over time or across an ordered sequence. To create one in Microsoft Excel for Microsoft 365 on Windows, select a PivotTable, choose PivotTable Analyze > PivotChart, and select a Line chart type.

What a PivotChart Line Graph Shows

A PivotChart is a chart connected to a PivotTable. The PivotTable summarizes the source records, while the chart presents that summary visually. In a line chart, the horizontal axis usually shows dates or another ordered category, and each line represents a series, such as a region, product, or department. The vertical axis shows a numeric measure, such as revenue, units sold, or average response time.

For example, a sales table might contain Date, Region, Product, and Sales columns. A PivotChart can plot monthly sales on the horizontal axis, sales amounts on the vertical axis, and separate lines for North, South, and West. The chart is not a picture of the original rows: it reflects the grouping and summarization set up in the PivotTable.

Because the chart is linked to the PivotTable, changing a field, filter, or value calculation in the PivotTable changes the chart too. This is the key difference from a regular chart, whose series and categories are normally tied directly to a worksheet range.

When a Line Chart Is the Right Choice

Choose a line graph when the order of the horizontal categories matters and the reader should see a trend, direction, or pattern of change. Dates are the most familiar example, but ordered stages such as week number, production step, or age band can also work. A line connects neighboring points, so it suggests continuity or progression between them.

A line chart is often a good fit for questions such as whether monthly revenue is rising, which region has seasonal demand, or whether average delivery time changed after a process update. It can also help compare several related series, provided the chart remains readable.

Use a standard Line chart to compare trends across periods.
Use a Line with Markers chart when individual observations or a small number of periods should be easy to locate.
Consider a column chart when the main task is comparing a few discrete categories rather than following a continuous trend.
Avoid plotting many lines at once. Too many series can overlap and make the pattern difficult to interpret.

A line graph is usually a poor choice for unordered categories, such as unrelated product names, because connecting them implies a sequence that may not exist. It can also be misleading when the underlying values are sparse or when missing periods are treated as if they were observed values.

Prepare the Source Data

Start with a rectangular list in which each column has one clear header and each row represents one record. For example, a transaction list should have fields such as Date, Region, and Sales, with one transaction per row. Avoid blank header cells, merged cells, and completely blank rows or columns inside the list; these can interfere with PivotTable creation or make the source range harder to identify.

Use actual Excel dates in the Date column, not text that merely looks like a date. Likewise, store Sales as numeric values rather than numbers typed with extra text. If a date is imported as text, Excel may not group it into months or quarters. Check a few cells by changing their number format or using the formula bar before building the chart.

For a source that will grow over time, format the list as an Excel Table using Insert > Table or Home > Format as Table. A PivotTable based on a Table can include appended rows after the PivotTable is refreshed. If the PivotTable uses a fixed cell range instead, newly added rows outside that range will not automatically become part of its source.

Create the PivotTable and Chart

Click a cell in the source list or Excel Table.
Choose Insert > PivotTable. Confirm the table or range, choose where to place the PivotTable, and select OK.
In the PivotTable Fields pane, place the date field in Rows and a numeric field such as Sales in Values. Add a category such as Region to Columns if you want separate series, or to Filters if you want to select one category at a time.
Select a cell in the PivotTable, then choose PivotTable Analyze > PivotChart.
In the chart selection window, choose Line, select a suitable subtype, and confirm. Excel inserts a chart tied to the PivotTable.

Depending on the field arrangement and Excel build, Excel may place fields into chart areas named Axis (Categories), Legend (Series), Values, and Filters. These correspond to the categories, lines, numeric measurements, and report filters. You can change the layout in the PivotChart Fields pane by dragging fields between areas. If the desired chart is not created, select the chart and use Chart Design > Change Chart Type to choose a Line option.

The ribbon path above describes Excel for Microsoft 365 on Windows. Excel for Mac has PivotTable and PivotChart features, but ribbon layout and some chart controls can differ. Excel for the web may also present a different set of controls, so use the desktop Windows steps as the reference when a command is not in the same place.

Arrange Fields and Choose a Useful Summary

Field placement determines what the graph means. For a monthly trend by region, put Date on the category axis, Sales in Values, and Region in the legend or series area. If Region is placed in Filters instead, the chart will generally show the selected region rather than a separate line for every region.

Excel commonly summarizes numeric fields by Sum, but a sum is not always the right statistic. For transaction-level sales, total sales by month may be appropriate. For a field such as delivery time, an average may be more informative than a total. Open the menu for the value field in the PivotTable Fields pane and choose Value Field Settings to select an available summary, such as Sum, Count, or Average. The calculation should match the question and the meaning of the data.

Dates may initially appear as individual days. To summarize them by month or quarter, right-click a date label in the PivotTable and choose Group, then select the desired time units. Grouping changes the PivotTable categories and therefore the chart axis. If Group is unavailable or produces an error, check that the source column contains valid dates throughout and does not include blanks or text values that break the date field.

For a long time series, consider whether the chart needs daily, weekly, monthly, or quarterly detail. A chart with hundreds of daily points may be cluttered, while a yearly summary may hide meaningful short-term variation. Choose a period that matches the decision or question the chart is meant to support.

Filter, Refresh, and Interact with the Chart

PivotChart filters let readers focus on selected dates or categories without rebuilding the chart. Use the field filter controls associated with the PivotChart or PivotTable, or add a slicer from PivotTable Analyze > Insert Slicer for a category such as Region. A slicer provides visible buttons for selecting one or more items. A date field can also be controlled with a Timeline when that option is available for the PivotTable.

Filtering is useful, but the chart should make the current selection clear. A line that disappears because its category was filtered out is different from a line with missing data. Keep the relevant filter controls visible or label the chart so readers can tell whether they are viewing all regions or only a subset.

When source records change, refresh the PivotTable so the chart reflects the updated summary. Select the PivotTable and choose PivotTable Analyze > Refresh, or use Data > Refresh All to refresh workbook connections and PivotTables. If the data source is an Excel Table, appended records can be included on refresh. If it is a fixed range, update the source through PivotTable Analyze > Change Data Source before refreshing.

Common Problems and How to Fix Them

The horizontal axis shows days instead of months: Group valid date labels into months or quarters. Check that the source values are real dates and that the field contains no incompatible blanks or text.
The line is flat or the totals look too large: Review the field in Values and its Value Field Settings. Sum, Count, and Average answer different questions, and a default sum may not suit every measure.
There is only one line: Check that the comparison field is in the legend or series area, not only in Filters. Also confirm that the field has multiple distinct items in the filtered records.
New rows do not appear: Refresh the PivotTable and verify its source. If the source is a fixed range, extend or change it; using an Excel Table can make future additions easier to include.
The graph is crowded: Reduce the number of series, filter to relevant categories, or summarize at a broader time interval. Direct labels or a clear legend can also help readers follow the lines.
There are gaps or unexpected points: Inspect missing dates and values in the source. A PivotChart cannot infer what an unrecorded value should have been, and connecting across a gap may not communicate the data accurately.

Formatting choices matter too. Keep the vertical axis scale honest and consistent when comparing related charts. Use distinct line colors with adequate contrast, and avoid relying on color alone when readers need to distinguish series. A descriptive chart title should name both the measure and the time scale, such as Monthly Sales by Region.

PivotChart Versus a Regular Line Chart

A PivotChart is convenient when users need to rearrange fields, filter categories, group dates, or change the summary interactively. Its chart remains tied to the PivotTable, so edits to the PivotTable layout are reflected in the visualization. This makes it well suited to exploratory analysis and recurring reports built from structured records.

A regular line chart can be preferable when the plotted series are already arranged in a simple worksheet range and the chart needs to be customized independently. Regular charts generally allow direct editing of the chart’s data range, whereas PivotCharts are controlled through their associated PivotTable fields and filters. Some chart options and formatting behaviors can also differ for PivotCharts.

In practice, choose a PivotChart for flexible summaries and a regular chart for a fixed presentation where the data layout and series are deliberately controlled. If a PivotChart’s field-driven behavior is useful but its appearance needs refinement, first try Chart Design and Format options; do not assume that every regular-chart operation is available for a PivotChart.

Frequently Asked Questions

Can a PivotChart be a line graph?
Yes. Select a PivotTable, choose PivotTable Analyze > PivotChart, and choose a Line chart subtype. The chart will display the PivotTable’s summarized values.

How do I make months appear on the line chart axis?
Put a valid date field in the category area, then group the date labels by Months in the PivotTable. If the Group command fails, check for text dates, blanks, or inconsistent values in the source column.

Why does my PivotChart show only one line?
The field intended to separate the lines may be in the Filters area instead of the Legend (Series) area, or the current filter may leave only one category. Move the field to the series area and review active filters.

Does a PivotChart update automatically when I add data?
It updates when its PivotTable is refreshed, not simply because source cells changed. A source formatted as an Excel Table can expand to include appended rows, but the PivotTable still needs a refresh.

Can I change a PivotChart into a regular chart?
Excel provides ways to copy chart results or create a separate chart from worksheet values, but a PivotChart is designed to remain connected to its PivotTable. If you need independent series editing, build a regular line chart from a suitable summary range.

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.