How to calculate a moving average in Excel

The simple moving average (SMA)

A simple moving average weights every value in the window equally. The window length k is your choice: 3 months reacts quickly, 6 or 12 months smooths more. The first result appears only once a full window is available, so a 3-month average starts in the third row; leave the first two cells empty rather than averaging fewer values, otherwise the early points are not comparable.

With sales in B2:B13, type =AVERAGE(B2:B4) in C4 and drag down to C13. Do not use absolute references here: the whole point is that both ends of the range move. A trailing average like this one belongs to the last period of its window. A centred moving average, which places the 3-month average on the middle month, is better for describing a past trend but cannot be used for forecasting, because it needs one future value.

To make the window length adjustable, put k in a cell, say H1, and use =AVERAGE(OFFSET(B13,1-$H$1,0,$H$1,1)) for the latest average. OFFSET starts at the current row, goes k − 1 rows up and takes k rows. It is flexible but volatile, so for large models the plain fill-down AVERAGE is faster and easier to audit.

The weighted moving average (WMA)

A weighted moving average gives recent periods more influence. With weights 1, 2 and 3 for the oldest, middle and newest month, the formula in D4 is =(B2*1+B3*2+B4*3)/6, where 6 is the sum of the weights. For December this gives (512 × 1 + 548 × 2 + 571 × 3) ÷ 6 = 553.5, about ten units above the simple average, because the strong last month counts most.

With the weights in a separate range, for example H2:H4, the general form is =SUMPRODUCT(B2:B4,$H$2:$H$4)/SUM($H$2:$H$4). The weights can be any positive numbers; they do not have to add up to 1 because the formula divides by their sum. Make sure the order matches: the newest value must line up with the largest weight.

Exponential smoothing (EMA)

Exponential smoothing keeps all past values in the average, with weights that shrink by the factor (1 − α) each period. Each new smoothed value is α × the latest actual + (1 − α) × the previous smoothed value. In the example the first smoothed value is simply the first actual, =B2, and from E3 on the formula is =$E$16*B3+(1-$E$16)*E2, with α = 0.3 in E16. A high α (0.5 or more) follows the data closely; a low α (0.1 to 0.2) gives a very smooth line.

In finance the same idea is called an exponential moving average and often uses α = 2 ÷ (k + 1), so a "10-day EMA" has α ≈ 0.18. Note how the smoothed value for December, 529.0, is below both the simple and the weighted average. In a series with a steady upward trend, every averaging method lags behind, and a low α lags most.

Worked example: three moving averages of monthly unit sales

Twelve months of unit sales (yellow). Column C is the 3-month simple moving average, D the weighted version with weights 1-2-3, E exponential smoothing with the α in E16. The lower block turns each into a forecast for January and compares their past accuracy.

Worked example: three moving averages of monthly unit sales

	A	B	C	D	E
1	Month	Units sold	3-month SMA	3-month WMA (1-2-3)	Exponential (α in E16)
2	Jan	412			412.0
3	Feb	389			405.1
4	Mar	455	418.7	425.8	420.1
5	Apr	430	424.7	431.5	423.0
6	May	478	454.3	458.2	439.5
7	Jun	462	456.7	462.0	446.3
8	Jul	501	480.3	484.2	462.7
9	Aug	487	483.3	487.5	470.0
10	Sep	530	506.0	510.8	488.0
11	Oct	512	509.7	513.8	495.2
12	Nov	548	530.0	533.0	511.0
13	Dec	571	543.7	553.5	529.0
14
15	Forecast for January next year				Value
16	Smoothing factor α (0 to 1)				0.30
17	SMA forecast = last 3-month average				543.7
18	WMA forecast = last weighted average				553.5
19	Exponential forecast = last smoothed value				529.0
20	Same SMA with a fixed window: AVERAGE(B11:B13)				543.7
21	Mean absolute error of the SMA as a forecast (Apr–Dec)				28.4
22	Mean absolute error of the WMA as a forecast (Apr–Dec)				23.6

Illustrative data. The error rows treat each moving average as the forecast for the month after its window.

Using a moving average as a forecast

The simplest forecast for next period is the latest moving average: 543.7 units for January with the SMA. To judge whether that is any good, look back. Treat each 3-month average as the forecast for the following month and measure the errors: the mean absolute error of the SMA from April to December is 28.4 units, the weighted version 23.6. On this rising series the weighted average is more accurate because it lags less.

That points to the limit of all moving averages: they forecast a flat line at the current level. When the data have a clear trend, a trendline forecast fits better, see the FORECAST.LINEAR guide. When the data are seasonal, a 12-month moving average removes the seasonal pattern, which makes it a good way to see the underlying trend but a poor forecast for a single month. Excel charts can also draw a moving average directly: add a trendline, choose Moving Average and set the period. That trendline is for viewing only; the cell formulas are what you need for forecasts and error checks.

Common mistakes

• Locking the range with $. =AVERAGE($B$2:$B$4) does not move when filled down, so every row shows the same number. The references must stay relative.
• Averaging incomplete windows. Putting =AVERAGE(B2:B2) and =AVERAGE(B2:B3) in the first rows mixes 1-, 2- and 3-period averages. Start where the first full window ends.
• Aligning the average with the wrong period. A trailing 3-month average ending in March is a forecast for April, not for March. Comparing it with March sales flatters the accuracy.
• Weights in the wrong order. In a weighted moving average the largest weight must go to the newest value. Reversed weights make the line lag even more.
• Expecting a moving average to follow a trend. In rising or falling data every moving average lags behind. Use a trend method when the direction matters.
• Gaps and zeros in the data. AVERAGE ignores empty cells but counts zeros, so a month recorded as 0 instead of left blank pulls three averages down.