Moving Average

Excel Formulas › Average

All versionsAVERAGERolling window

A moving average smooths out noise by averaging each point with the few before it — a 3-month rolling average of sales, a 7-day average of traffic. The core is just AVERAGE over a window that slides down with the formula.

Quick formula: for a 3-period moving average, in the 3rd row and down:
=AVERAGE(B2:B4)
Copying it down makes the window slide: B3:B5, B4:B6, … Each result is the mean of that point and the two before it.

Functions used (tap for the full reference guide):

AVERAGEOFFSET

The example

Monthly sales with a 3-month moving average in column C (starts at the 3rd month).

ABC
1MonthSales3-mo avg
2Jan100
3Feb140
4Mar120120
5Apr180147
6May150150

The formula

The moving average in C4, filled down:

=AVERAGE(B2:B4) // (100+140+120)/3 = 120

How it works

The window is a relative range, so it slides:

  1. In C4 the window is B2:B4 — the current month and the two before it.
  2. Copy down to C5 and the relative reference shifts to B3:B5; C6 becomes B4:B6.
  3. Each cell is therefore the average of its own 3-month window — the “moving” part.
  4. The first two months have no full window, so leave them blank (or they’d average fewer points).

Dynamic window size? Use OFFSET to make the period a variable: =AVERAGE(OFFSET(B4, 0, 0, -E1, 1)) averages the last E1 rows ending at B4.

Try it: interactive demo

Live demo

Pick a window size; see the moving average of the last point.

Latest moving average (May):

Variations

Weighted moving average

Give recent points more pull with SUMPRODUCT:

=SUMPRODUCT(B2:B4, {1;2;3}) / 6

Dynamic window with OFFSET

Let a cell control the period:

=AVERAGE(OFFSET(B4, 0, 0, -E1, 1))

Centered moving average

Average around the point, not just behind it:

=AVERAGE(B3:B5)

Pitfalls & errors

The first rows have no full window. Leave them blank rather than averaging 1–2 points, which would distort the trend.

Lock the window only if you must. A moving average needs a relative range so it slides — don’t anchor it with $.

Blanks pull the average. AVERAGE ignores truly empty cells, but a 0 entered for “no data” is counted — use blank, not 0, for missing periods.

Practice workbook

📊
Download the free Moving Average practice workbook
The sales series with a live 3-month moving average, the weighted and centered variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I calculate a moving average in Excel?
Use AVERAGE over a sliding window, e.g. =AVERAGE(B2:B4) for a 3-period average, and fill it down so the relative range moves with each row.
How do I make the moving-average period adjustable?
Use OFFSET to size the window from a cell: =AVERAGE(OFFSET(B4,0,0,-E1,1)) averages the last E1 rows ending at B4.
Why are the first rows of my moving average blank?
There aren't enough prior periods to fill the window. Leaving them blank avoids averaging only one or two points, which would distort the early trend.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Average by group · Weighted average · Running total

Function references: AVERAGE · OFFSET