To draw a monthly trendline in Excel, first summarize your data into one value per month, create a chart from those monthly results, then select the data series and choose Chart Design > Add Chart Element > Trendline. Excel adds a fitted line to the chart; it does not automatically turn daily or transaction-level records into monthly totals.
What a monthly trendline shows
A trendline summarizes the overall direction of a series, such as monthly sales, website visits, or expenses. It is a mathematical fit to the values on a chart, not a line that connects every month’s actual result. A rising trendline suggests an upward overall pattern, while a falling one suggests a decline. The individual monthly values can still fluctuate above and below it.
The order matters: aggregate the underlying records into months before adding the line. If a table contains hundreds of daily transactions, charting those rows and adding a trendline describes the daily observations, not monthly totals. Monthly aggregation also makes the chart easier to read and gives each month a clear place on the horizontal axis.
A trendline is not the same as a moving average. A trendline fits a selected mathematical model to the whole series; a moving average smooths local variation by averaging a rolling group of observations. Both can be useful, but they answer different questions.
Prepare dates and monthly values
Keep the detailed records in one table, with real Excel dates in one column and the values to analyze in another. A date that merely looks like a date but is stored as text can break grouping, sorting, or formulas. Format the date cells as dates, and check that they sort chronologically rather than alphabetically.
For a simple monthly summary, make a second table with one row per month. Put the first day of each month in a date column, then calculate a total, average, or other measure for that month. Choose the measure that matches the question: sum sales to see monthly revenue, but average daily temperature if the goal is typical monthly temperature.
For example, suppose transaction dates are in A2:A100 and amounts are in B2:B100. Put 1-Jan-2025 in D2 as a genuine date, and put the next month’s start date in D3. With those dates in D2:D3 and the source data in A2:B100, enter this in E2 to total the month beginning in D2:
=SUMIFS($B$2:$B$100,$A$2:$A$100,">="&D2,$A$2:$A$100,"<"&EDATE(D2,1))
The formula includes dates on or after the first of the month and before the first of the next month. If January’s transactions are 120 and 80, E2 returns 200. Copy the formula down alongside later month-start dates. Using an exclusive end date avoids problems with timestamps that include a time of day.
If you prefer to generate the next month’s start date by formula, with the current month start in D2, enter this in D3:
=EDATE(D2,1)
For a D2 value of 1-Jan-2025, the result is 1-Feb-2025. Format the dates as “mmm yyyy” if you want labels such as Jan 2025. The underlying cells remain dates, which is more useful than typing month names as text.
Create the chart from the monthly summary
Select the month and result columns in the summary table, including their headers. On Windows in Excel for Microsoft 365, choose Insert > Insert Line or Area Chart and select a 2-D Line chart. A line chart is a straightforward choice for evenly spaced monthly results. Excel uses the months as category labels and draws the values in sequence.
If the monthly intervals are not evenly spaced, or if you want the horizontal axis to represent actual elapsed time, use an XY scatter chart instead. Choose Insert > Insert Scatter (X, Y) or Bubble Chart and select a scatter option. Set the date column as the X values and the monthly results as the Y values. Scatter charts use numeric date values, so gaps between dates can be reflected in the spacing.
Check the horizontal axis before adding a trendline. It should show the months in chronological order, not alphabetically. If Excel displays serial numbers or unhelpful labels, format the axis or the source date cells as dates. A chart that uses text labels such as “Jan,” “Feb,” and “Mar” can also be ambiguous across multiple years; use actual dates formatted to show both month and year.
Add a linear trendline in Excel
- Click the chart, then click the monthly data series. Confirm that the series, rather than the chart area or an individual point, is selected.
- Choose Chart Design > Add Chart Element > Trendline > Linear. In some layouts, you can also right-click the selected series and choose Add Trendline.
- To change the model or its appearance, choose More Trendline Options or select the trendline and open the Format Trendline pane.
- Optionally, select Display Equation on chart or Display R-squared value on chart in the Format Trendline pane.
The menu labels can vary slightly with window size and Excel updates. On Mac, select the chart and use the Chart Design tab’s Add Chart Element controls, or use the chart’s contextual controls to add a trendline; the available options are similar. Excel for the web has chart editing features, but controls can be more limited or arranged differently than in the Windows desktop app.
Choose a trendline type that fits the question
Linear is a useful starting point when the series changes by roughly the same amount per month. It estimates a straight-line direction through the observations. It does not mean that each month follows a straight path, and it should not be treated as proof that the pattern will continue.
- Linear: Useful for a steady increase or decrease in absolute units, such as roughly 500 more visits per month.
- Exponential: Can describe growth or decline at a roughly constant percentage rate. It is not suitable for every data set; Excel requires positive values for this model.
- Moving Average: Smooths short-term variation using a chosen number of periods. It helps reveal local direction, but it is not a fitted straight-line forecast.
- Polynomial: Can represent curves, but a high order may follow noise and create a misleading impression. Use the simplest order that is defensible.
- Logarithmic or Power: These can fit particular curved relationships, but have data restrictions and should be chosen for a reason, not simply because they look closer.
Seasonality is a frequent issue with monthly data. Retail sales may rise every December and fall in January, for example. A single linear trendline does not separate seasonal effects from long-term movement. Consider plotting several years of monthly values, comparing the same month across years, or using a suitable seasonal analysis if that distinction matters.
Read the equation and R-squared carefully
Excel can display the trendline equation and R-squared value on the chart. R-squared describes how closely the selected model fits the plotted observations; a value nearer 1 indicates a closer fit for that model and data. It does not establish that the model is appropriate, explain why the values changed, or guarantee accurate future values. A high value can still come from an unsuitable model or a small sample.
On a line chart, the horizontal positions are usually the category sequence: first month, second month, and so on. Therefore, the displayed equation’s x-value may represent a position in the series rather than a calendar date such as March 2025. That is fine for describing equally spaced months, but it matters if you interpret the equation or use it outside the chart. An XY scatter chart with real dates as X values makes the time scale explicit, though its equation still uses Excel’s numeric date values.
Use Excel’s forecast controls with restraint. A chart trendline can be extended forward or backward through the Format Trendline options, but the extension is a model-based projection, not a promise. It may be especially unreliable when the data has seasonality, a sudden business change, missing months, or too few observations.
Use a PivotTable for larger transaction lists
A PivotTable can summarize a long list without building a separate SUMIFS formula for every month. Select the source table and choose Insert > PivotTable. Place the date field in Rows and the numeric field in Values, then confirm that the value calculation is appropriate, such as Sum or Average.
If Excel recognizes the source as dates, right-click a date in the PivotTable and choose Group. Select Months and Years when the data spans more than one year. Grouping only by Months can combine every January from every year into one category, which is usually wrong for a month-by-month timeline. If grouping is unavailable, inspect the source for blank cells, text dates, or invalid date values.
For a trendline, use the PivotTable summary as the source for a regular chart if the PivotChart controls do not offer the trendline option. Keep the grouped month and year labels in chronological order, and refresh the PivotTable when the source data changes.
Common problems and practical checks
- The chart shows one point per transaction: The chart uses raw records. Build a monthly summary first, or group the dates in a PivotTable.
- Months appear out of order: The labels may be text. Use real dates, sort them chronologically, and format them to display month and year.
- Some months are missing: Add a row for every month in the period. Decide whether a missing month means zero, no data, or an unknown value before charting; these meanings are not interchangeable.
- The trendline option is unavailable: Check that the data series is selected and that the chart type supports trendlines. A regular chart made from a PivotTable summary may be an alternative.
- The line looks unexpectedly steep or flat: Check the vertical-axis scale, the chart type, and whether values are totals or averages. A truncated axis can make small changes look dramatic.
- The trendline changes after editing data: Review the chart’s data range and the selected trendline type. A change in included months can alter the fitted line.
Frequently Asked Questions
Can Excel automatically draw a monthly trendline from daily data?
No. A chart trendline fits the points in the chart; it does not first aggregate daily records into monthly values. Summarize the records by month with a formula or PivotTable, then chart that summary and add the trendline.
Should I use a line chart or a scatter chart for monthly data?
Use a line chart for regularly spaced months when the main purpose is to show the sequence. Use an XY scatter chart when actual elapsed time matters, such as when months are missing or the intervals are irregular.
Can I add a trendline to a PivotChart?
Trendline availability can depend on the chart and Excel’s controls, and PivotCharts may not offer the same options as regular charts. If the trendline command is unavailable, chart the PivotTable’s summarized results with a regular chart.
Which trendline is best for monthly sales?
There is no universally best type. Start with linear if the question concerns a steady change in sales amount, but check for seasonal patterns and consider whether a percentage-growth model is justified. Compare the fit with the underlying data rather than choosing a model solely by its R-squared value.
Does a trendline predict next month’s result?
A fitted line describes the pattern in the data and can be extended as a simple projection in chart options. It does not account automatically for seasonality, new conditions, or uncertainty, so treat an extension as an estimate rather than a guaranteed forecast.
Related
- Excel Data Validation Default Value Explained
- Excel Data Validation Based on Another Cell: How It Works
- Excel Data Validation: Make Lists Dynamic
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Chart Axis in Millions: Format Values ClearlyTo show an Excel chart axis in millions, select the axis, open Format Axis, and either choose Millions…
- Access Power Pivot in Excel: Enable and Troubleshoot ItTo access Power Pivot in Excel for Microsoft 365 on Windows, select File > Options > Add-ins. At…
- Excel 3-Line Chart: Create and Format Three SeriesTo create a three-line chart in Excel for Microsoft 365 on Windows, put the category labels in the…
- Excel Line Chart with Multiple Lines and a Secondary AxisTo make an Excel line chart with multiple lines, arrange each series in its own column, select the…
- 2-D Clustered Column Chart in Excel: How to Make OneTo make a 2-D clustered column chart in Excel for Microsoft 365 on Windows, select your category labels…
- Excel Trendline Values: How to Display and Calculate ThemTo get trendline values in Excel, select the chart’s data series, add a trendline, and tick Display Equation…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.