A trendline will draw a straight line through anything you give it, so the skill is knowing when the line means something. This tutorial uses 36 months of fictitious sales to add a linear trendline and read its equation, build a moving average and choose its window, forecast six months with FORECAST.LINEAR, and see the mistake that makes a trendline meaningless: drawing it across categories instead of across time. Every number can be checked in a free workbook.
Start with time on the horizontal axis
A trend is a change over time. Put the months on the horizontal axis and the sales on the vertical axis, with a line chart. Select the Month and Sales columns, then Insert, Line. If the horizontal axis is a list of companies, products or regions, a trendline has nothing to measure; the last section shows why.
A linear trendline, and the same numbers by formula
Click the chart, then the + button (Chart Elements), Trendline, Linear. Open More Options and tick Display Equation on chart and Display R-squared value on chart.
The equation Excel prints is the one the SLOPE and INTERCEPT functions give you:
=SLOPE(tblTrend[Sales], tblTrend[Period])
=INTERCEPT(tblTrend[Sales], tblTrend[Period])
=RSQ(tblTrend[Sales], tblTrend[Period])
In the workbook they return a slope of 1,697.92, an intercept of 102,013.84 and an R² of 0.7806. Read them in words: the line adds about 1,698 of sales per month, and it accounts for about 78% of the variation in these 36 months. That 78% describes how well the line fits the months you already have; it says nothing about the next ones, and nothing about why sales rose.
A moving average, and how long the window should be
A moving average replaces each month with the average of the last few, so the ups and downs of each month cancel out. There are two ways to get it. In the chart, Trendline, Moving Average, with a period. Or in a column, so you can see and audit the numbers:
=IF(B11<Trend!$B$8, NA(), AVERAGE(OFFSET(C11, 1-Trend!$B$8, 0, Trend!$B$8, 1)))
The window, in months, sits in cell B8 of the Trend sheet; B11 and C11 are the current row's Period and Sales on the Data sheet. The first months show #N/A because there are not enough previous months yet. OFFSET is volatile, which is fine for 36 rows; for a fixed window you can write the plain range, for example =AVERAGE(C9:C11).
The window matters more than the method. These sales have a 12-month seasonal cycle. Measured as the average change from one month to the next over the last 12 months, the raw sales move 4,543.35 a month, the 3-month moving average 4,217.88 and the 12-month moving average 1,847.57. A 3-month window barely smooths a yearly cycle; a 12-month window removes it and leaves the trend, whose slope is 1,697.92. The price of a long window is that you lose the first 11 months and that the average reacts late, because it is built only from months that already happened.
Forecast the next six months
FORECAST.LINEAR fits the same line and reads it at a future period:
=FORECAST.LINEAR(37, tblTrend[Sales], tblTrend[Period])
It equals the intercept plus the slope times the period, 102,013.84 + 1,697.92 × 37. FORECAST, the older function, gives the same result and works in versions before Excel 2016.
| Month | Period | Forecast |
|---|---|---|
| Jan 2027 | 37 | 164,836.72 |
| Feb 2027 | 38 | 166,534.63 |
| Mar 2027 | 39 | 168,232.55 |
| Apr 2027 | 40 | 169,930.46 |
| May 2027 | 41 | 171,628.38 |
| Jun 2027 | 42 | 173,326.30 |
Read the forecast with its flaw in mind: a straight line ignores the seasonal cycle. In this data, January has been about 5,200 below the line on average over the three years, so a January forecast of 164,836.72 is probably too high. Excel has FORECAST.ETS for data with seasonality; I did not test it for this post.
The mistake: a trendline across categories
The workbook has eight companies with their revenue. If you draw a bar chart and add a trendline, Excel gives you a line, and the line depends on the order of the bars, not on the data:
| Order of the bars | Slope | R² |
|---|---|---|
| Alphabetical | -2,298.81 | 0.0429 |
| From highest to lowest revenue | -11,082.14 | 0.9981 |
The same eight figures give a line that explains 4% of the variation in one order and 99.8% in the other. Sorting the bars in descending order can make a set of numbers look like a clean downward trend. A trendline needs an axis where the order means something, and time is the usual one.
What I check before I trust a trendline
| Question | Why |
|---|---|
| Is the horizontal axis time, or another ordered number? | Otherwise the slope is the order of the bars |
| Do I have at least two full cycles? | Three years show a seasonal pattern; six months do not |
| Does the window match the cycle? | A 3-month average does not smooth a yearly cycle |
| Am I forecasting a few periods or many? | The farther from the data, the less the line can be trusted |
| Is R² being read as the reason? | It measures fit, not cause |
Limits
- The data is fictitious. The workbook shows how each tool behaves; your data will have its own trend, noise and cycle.
- I checked the formulas, not the chart screens. The numbers come from a Python replica and a recalculation in LibreOffice; the chart steps are the ones Excel for Microsoft 365 shows, and you should confirm them in your version.
- R² here is in-sample. It says how well the line fits the 36 months, not how well it predicts.
- A straight line is a simple model. If your series is curved or seasonal, try another trendline type or FORECAST.ETS and compare.
- FORECAST.LINEAR needs Excel 2016 or later. In earlier versions use FORECAST.
Workbook
Download the trends workbook, with fictitious data, no sign-up and no email. It has the trendline formulas, the moving average with a window you can change, the six-month forecast, the comparison of windows, the categories mistake, two charts and seven guided tests with the expected result.
If you want a trend or a forecast reviewed in your own workbook, let's talk.