Excel Line Chart: Plotting Total Values Over Time

To make an Excel line chart that uses total values, put each period or category in one column and its total in the next, select both columns, then choose Insert > Insert Line or Area Chart > 2-D Line. If your source contains several records per period, first sum them with a PivotTable or a total formula; use a running total only when you specifically want a cumulative trend.

What a line chart of total values shows

A line chart connects values in a meaningful order, most often dates or time periods. For example, a chart with months on the horizontal axis and total sales on the vertical axis shows how sales change from month to month. The total is the plotted value for each month, not necessarily the sum of every month before it.

That distinction matters because “total” can describe two different measures:

Period total: The amount recorded within each day, month, quarter, or other interval. Monthly sales of $200, $260, and $230 appear as three separate points.
Running total: The cumulative amount through each interval. Those same monthly figures become $200, $460, and $690.

For a standard trend chart, period totals are usually the right choice. A running total answers a different question: how much has accumulated so far? Label the chart and vertical axis clearly so readers do not mistake one measure for the other.

Arrange the data before inserting the chart

Excel needs a label for each point and a corresponding numeric value. Place those labels in the first column and the totals in the next column, with a header in each column. For example, a small monthly summary could have Month in A1 and Total sales in B1, followed by January and 200, February and 260, and March and 230. Select the headers and all three rows of values before inserting the chart.

For a total made from multiple components, add a total column to the source table. With product revenue in B2 and service revenue in C2, and the dates or periods in column A, put this formula in D2:

With product revenue in B2 and service revenue in C2, the formula in D2 adds those two values; for example, 120 plus 80 returns 200.

=SUM(B2:C2)

Copy the formula down for the remaining records, then chart the period column and the total column. If the two components are not in adjacent columns, use a SUM formula that refers to the relevant cells, or arrange the source data so the components are together. Check that every value included in the total belongs to the same period and uses the same unit.

To make a source range easier to maintain, select the data and choose Insert > Table. Confirm that My table has headers is selected when appropriate. Charts based on an Excel Table can include new rows added to that table, which is helpful for regularly updated summaries.

Create the line chart in Excel for Microsoft 365

Arrange the period labels and totals in adjacent columns, with headers and no completely blank rows inside the data.
Select the full range, including the headers. If Excel does not interpret the data correctly, select the chart and use Chart Design > Select Data to check the chart data range.
On the Windows ribbon, choose Insert > Insert Line or Area Chart > 2-D Line. A basic Line chart is a sensible starting point for totals over time.
Use the chart elements button beside the chart, or Chart Design > Add Chart Element, to add a chart title and axis titles when they would help explain the data.
Replace the default title with a precise description, such as Monthly Sales Total, and label the vertical axis with the unit, such as Dollars.

Excel normally treats the first selected column as the horizontal category labels and the numeric column as the series to plot. If the line shows months as separate series instead, select Chart Design > Switch Row/Column. You can also correct the arrangement through Chart Design > Select Data, where you can edit the series and horizontal axis labels.

On Excel for Mac, the chart is also created from the Insert tab, though the layout and wording of chart menus can vary slightly by release. Excel for the web offers line charts from its Insert tab as well, but some formatting and data-selection controls may be arranged differently from the Windows desktop ribbon.

Use a PivotTable when records need to be summed

If the source is a transaction list rather than a pre-summarized table, a PivotTable is often a clearer way to calculate totals. Imagine a list with one row per sale, a date in one column, and an amount in another. A line chart of those raw rows would plot individual sales, not one total per month. A PivotTable can group and sum the amounts first.

Select a cell in the transaction list, then choose Insert > PivotTable and confirm the source range and destination.
In the PivotTable Fields pane, drag the date field to Rows and the amount field to Values.
Check that the Values field summarizes by Sum. If Excel displays Count instead, open the field’s value settings and choose Sum; a field stored as text may need to be converted to numbers first.
For daily records that should be summarized monthly, right-click a date in the PivotTable and choose Group, then select the appropriate time units, such as Months and Years.
Select a cell in the PivotTable and choose PivotTable Analyze > PivotChart. Select a 2-D Line chart.

Grouping dates by both month and year avoids combining every January from different years into one category. If dates cannot be grouped, check for blanks, text that only looks like a date, or mixed data types in the source column. A PivotChart is connected to its PivotTable, so refresh the PivotTable after changing the underlying records; choose PivotTable Analyze > Refresh, or use Data > Refresh All when you need to update multiple connections.

Choose period totals or a cumulative line

Use a period-total line when the question is how much happened in each interval: monthly expenses, weekly orders, or daily visitors. Peaks and dips then represent changes in activity during those periods. Use a running total when the question is how a quantity accumulates, such as total units shipped so far this year or cumulative project spending.

For a PivotTable running total, open the value field’s settings and use the Show Values As tab, then choose Running Total In and select the date or period field as the base field. The PivotTable will display cumulative values in sequence. Verify the ordering and reset the accumulation at the intended boundary, such as the start of each year. If the chart is meant to show monthly activity, do not select this option: it changes the measure rather than merely changing the chart’s appearance.

Pick the right line style and scale

A basic 2-D Line chart is generally appropriate when the horizontal axis contains ordered dates or categories and you want to compare values across them. A Line with Markers can make individual monthly or quarterly points easier to identify, especially when there are only a few. With many points, markers can crowd the chart; a plain line is often easier to read.

A stacked line chart is for comparing a total and its component series as they build on one another. It is not the same as plotting one already-calculated total. If you chart both components and their sum as ordinary lines, the total line will overlap or sit apart from the components according to their values; that may be useful, but it can also make the chart busy. Decide whether the reader needs the overall total, the breakdown, or both.

Check the vertical axis before interpreting small changes. Excel may choose a scale that starts above zero, which can make differences look larger than they are. A line chart’s vertical axis does not always need to begin at zero, but for totals where the size of the change could be overstated, a zero baseline can aid comparison. Right-click the vertical axis, choose Format Axis, and review the Bounds settings. Do not change the scale without considering how it affects the message.

Fix common chart problems

The chart plots one point per transaction: The source is still at transaction level. Summarize it by period with a PivotTable or a separate summary table before charting.
The line uses row numbers instead of dates: Include the date or category column in the selected range, then check the horizontal axis labels under Chart Design > Select Data.
Months appear in the wrong order: Sort the source chronologically. If month names are plain text, Excel may sort them alphabetically; use real dates formatted to display as month names, or otherwise provide a correctly ordered category list.
The chart shows Count instead of Sum: In a PivotTable, change the value summary to Sum and check that the source values are numeric rather than text.
There are gaps in the line: Inspect the source for blank cells or missing periods. Select the chart and use Chart Design > Select Data > Hidden and Empty Cells to review how empty cells are displayed. Do not connect gaps if doing so would suggest values were recorded when they were not.
New rows do not appear: If the source is a PivotTable, refresh it. If it is a regular chart, confirm that the new data is included in the chart range; using an Excel Table as the source can make ongoing additions easier to manage.

When a different chart is a better fit

A line chart is strongest when order matters, particularly for continuous time. For a small number of unrelated categories, a column chart may make individual totals easier to compare. If the goal is to show how each category contributes to a whole, a stacked column chart may be more suitable, although readers should still be able to compare the totals accurately. For dates with irregular gaps where the spacing between observations matters, consider an XY (Scatter) chart: a line chart generally spaces categories evenly, while a scatter chart uses numeric x-values and their actual spacing.

Do not choose a chart type just because the word total appears in the data. First decide what each point represents, whether the order and spacing matter, and whether the reader needs a trend, a category comparison, or a part-to-whole view.

Frequently Asked Questions

How do I show the total on each point of an Excel line chart?
Select the chart, then choose Chart Design > Add Chart Element > Data Labels and pick a position. Labels can overlap when the chart has many points, so consider showing them only for a small series or a few important points.

How do I make Excel add multiple rows into one monthly total?
Build a PivotTable with dates in Rows and the numeric field in Values, summarized by Sum. Group the dates by month and year if needed, then create a PivotChart from the PivotTable.

Is a total line the same as a running-total line?
No. A period total is the amount for that period alone. A running total adds each period to the previous accumulated amount, so it normally stays level or rises when values are nonnegative.

Can I chart a total alongside its component values?
Yes. Include the total and component columns as separate series if that comparison is useful. Check whether the lines will be understandable together; if the components add up to the total, a stacked chart or a separate chart may communicate the relationship more clearly.

Why does my PivotChart not change after I edit the source?
Refresh the PivotTable that feeds it. Choose PivotTable Analyze > Refresh, or Data > Refresh All for multiple connections, and confirm that the changed records fall inside the PivotTable’s source 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.