In Excel, a trendline is not necessarily an average: most trendlines model the overall direction of data, while the chart’s Moving Average trendline smooths short-term fluctuations by averaging a chosen number of consecutive data points. To add one in Excel for Microsoft 365 on Windows, select a supported chart, then choose Chart Design > Add Chart Element > Trendline > More Trendline Options and select Moving Average.
What an Excel trendline represents
A trendline is a visual model added to a chart to make a pattern easier to see. Depending on the type selected, it may estimate a straight-line relationship, a curved relationship, or a rolling average. It is separate from the source data: adding a trendline normally does not insert new values into the worksheet cells.
People sometimes say “trendline average” when they mean one of two different things. They might mean the Moving Average option available in Excel charts, or they might mean a line that summarizes the general direction of their data. Those are not interchangeable. A moving average calculates local averages in sequence; a linear trendline fits one straight line across the data, usually by minimizing the squared differences between observed and predicted values.
For example, monthly sales could rise overall while jumping up and down from month to month. A linear trendline emphasizes the broad upward direction. A moving average can soften the month-to-month jumps, making the changing pattern easier to follow, but it may lag behind a sudden change because it includes earlier observations.
How to add a Moving Average trendline
First, organize the values in the order you want Excel to analyze them. For monthly data, put each month in one column and its measurement in another, then create a line chart or an XY (scatter) chart as appropriate. Select the chart and use this path in Excel for Microsoft 365 on Windows:
- Choose Chart Design > Add Chart Element > Trendline > More Trendline Options.
- In the Format Trendline pane, select Moving Average under Trendline Options.
- Enter a Period, which is the number of consecutive data points included in each average.
- Close the pane and inspect the line against the original series. Adjust the period if the line is too jagged or hides meaningful changes.
The default period is commonly two points, so check it rather than assuming Excel has chosen a useful smoothing interval. A period of 3 averages three consecutive observations at a time; a period of 12 on monthly data averages a year of observations at a time. The right choice depends on the question and the frequency of the data, not on a universal rule.
On Mac, select the chart and use the Chart Design tab to add a trendline; chart-formatting controls may be arranged differently from Windows. In Excel for the web, available chart controls can differ. If the Moving Average option or its period setting is not available in the interface you are using, open the workbook in the desktop app.
What the period changes
The period controls how many neighboring data points contribute to each moving average. A smaller period follows the source series closely and preserves more short-term variation. A larger period smooths more aggressively and is better suited to showing a broad pattern, but it can hide short-lived peaks or turning points.
Consider monthly visitor counts that move between 90 and 130 but trend upward over several years. A three-month moving average reduces some month-to-month noise while still reacting fairly quickly. A twelve-month period suppresses seasonal and monthly variation more strongly, which can help reveal the longer-term level. It also takes longer to reflect a change that begins near the end of the series.
Excel calculates the moving average from data points in sequence; it does not determine a calendar window based on elapsed time. If observations are unevenly spaced, a period of three means three records, not necessarily three months. A missing month or an irregular measurement schedule can therefore make the visual result misleading unless the data is arranged and interpreted carefully.
Moving average versus other trendlines
Excel offers several trendline models, and they answer different questions. Choosing one because it looks attractive is not a reliable analysis method. Consider the shape of the data and what you are trying to describe.
- Linear: Fits a straight line and is useful for a roughly steady increase or decrease. Its slope represents the modeled change in the vertical value for each step in the horizontal variable.
- Exponential: Models a curve whose rate of change is proportional to its current level. It requires positive values and is not suitable for data containing zero or negative values.
- Logarithmic: Models a pattern that changes quickly and then levels off, or the reverse, and requires positive horizontal values.
- Polynomial: Fits a curved line with a selected degree. Higher degrees can create more bends, but may fit random fluctuations rather than a useful underlying pattern.
- Power: Models a power relationship between variables and requires positive values on both axes.
- Moving Average: Smooths a sequence by averaging neighboring points. Unlike the other listed models, it does not fit one equation across the full series.
For a steadily increasing measurement, a linear line may communicate the overall direction more clearly than a moving average. For strongly fluctuating readings, a moving average may help show the local level. If the data follows a seasonal cycle, a single linear trendline may conceal that cycle; the chart should show enough context to distinguish seasonality from long-term movement.
Using a worksheet formula instead
A chart trendline is convenient for visual analysis, but it may not provide a reusable set of smoothed values in cells. If you need to chart, report, or compare those averages elsewhere, calculate them in a helper column. With five sample values of 10, 12, 14, 8, and 16 in cells B2:B6, enter this formula in C6 to calculate the first five-point average:
=AVERAGE(B2:B6)
The result is 12, because the five values total 60 and 60 divided by 5 is 12. Label column C clearly, then copy the formula down one row at a time so each result uses the next five consecutive source values. The first result appears beside the fifth observation because five data points are needed to make a complete five-point window. This worksheet method makes each calculated number visible and available to other formulas.
A worksheet formula and a chart Moving Average trendline can serve similar purposes, but their presentation differs. The formula creates cell values that can be inspected or reused. The chart option adds a smoothed line to the plotted series without filling a helper column. If you need an auditable calculation or want to export the averages, use formulas; if you mainly need a quick visual overlay, the chart option may be simpler.
Reading a linear trendline equation and R-squared
Excel can display the equation for many fitted trendline types and can show the R-squared value. To add these labels, select the trendline, open the Format Trendline pane, and use the options to display the equation on the chart or display the R-squared value. For a linear trendline, the equation is commonly written as y = mx + b: m is the slope and b is the intercept. The chart’s horizontal and vertical variables determine what those quantities mean.
R-squared describes how closely a fitted model corresponds to the data used to fit it. It is not a measure of whether the model is appropriate, whether a relationship is causal, or whether future values will be accurate. A complicated polynomial can produce a high R-squared on historical points and still perform poorly outside them. Keep the equation’s displayed precision in mind too: the label may round coefficients, so a prediction recalculated from the rounded text may not exactly match Excel’s internal fit.
A Moving Average trendline is different: it is a sequence of local averages rather than a single fitted equation, so do not expect its equation or R-squared interpretation to work like a linear regression. If the purpose is forecasting, a smooth-looking line alone is not evidence that the future will follow it.
Common problems and how to avoid them
- The trendline option is unavailable: Trendlines are not supported for every chart type. Try a compatible two-dimensional chart, such as a line or XY scatter chart, and avoid relying on a three-dimensional or stacked chart for this feature.
- The line does not match the intended time intervals: A line chart can space category labels evenly, even when dates are not evenly spaced. For measurements with numeric or genuinely irregular x-values, an XY scatter chart can represent the horizontal values as a numeric scale.
- The moving average begins later than the source series: A complete window needs the selected number of observations. The first plotted average therefore cannot be calculated until enough data points are available.
- The line seems to miss a sudden shift: A longer period includes more earlier values, so it responds more slowly to a new level. Test a shorter period and compare it with the original data rather than treating the smoother line as the data itself.
- The line is misleading around gaps or outliers: Check for missing observations, unusual values, and changes in how measurements were collected. A moving average can spread the influence of an outlier across several neighboring averages.
Choosing a useful approach
Start with the question. If you want to communicate the broad direction over time, compare a fitted linear line with the raw observations. If you want to reduce short-term variation while keeping local changes visible, try a moving average and state its period. For example, on daily readings, a seven-point period summarizes seven records; it represents a week only if there is one observation for each day and no missing days.
Keep the raw series visible when possible. A trendline can make a chart easier to interpret, but hiding the observations may make noise, outliers, or abrupt changes disappear from view. Name the series and axes, indicate the period when readers need it to interpret the smoothing, and avoid extending a fitted line far beyond the observed range without a clear reason. A trendline describes a pattern in the data; it does not establish why the pattern occurred.
Frequently Asked Questions
Is an Excel trendline the same as an average? No. A linear or other fitted trendline models a pattern across the data. The chart’s Moving Average option averages a selected number of consecutive points to smooth the series.
What period should I use for a Moving Average trendline? Choose a period based on the data frequency and the pattern you want to reveal. A short period preserves more detail; a longer one smooths more. Compare the result with the original data rather than assuming one period is always best.
Can Excel show a moving average in worksheet cells? Yes. Use the AVERAGE function in a helper column for each consecutive window. Unlike a chart trendline, those results are cell values that can be referenced, checked, and reused.
Why does a Moving Average line start later than my data? Excel needs a full window of observations before it can calculate the first average. With a period of 12, for example, the first complete average requires 12 points.
Can I use a trendline to predict future values? A fitted line can be extended on a chart, but that is an extrapolation, not a guarantee. Check whether the model makes sense for the data and avoid treating a high R-squared or a smooth line as proof of future accuracy.
Related
- Excel Spreadsheet Grouping Rows: How to Create and Use It
- Excel Split Cell Into Multiple Cells: Methods That Work
- Excel Split Names in One Cell Into Two Cells
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- How to Make a Large Org Chart in PowerPointTo make a large org chart in PowerPoint, first organize the reporting structure, then use Insert > SmartArt…
- Spiderweb Graph in Excel: Create and Format a Radar ChartIn Excel for Microsoft 365 on Windows, a spiderweb graph is called a Radar chart. Select your category…
- Funnel Chart with Multiple Measures in Excel?Excel’s built-in funnel chart is designed to show one measure across a sequence of stages, so it does…
- Clear an Excel Pivot Table: Filters, Data, or Table?To clear a PivotTable in Excel for Microsoft 365 on Windows, click inside it and choose PivotTable Analyze…
- Doughnut Chart in Excel: Show PercentagesYes. In Microsoft Excel for Microsoft 365 on Windows, select your category names and values, choose Insert >…
- Pie Chart in Excel: How to Create, Format, and Label ItTo create a pie chart in Excel for Microsoft 365 on Windows, select a column of category names…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.