Moving average in Excel: formulas, rolling 3- and 12-month, and the chart

A moving average in Excel is an =AVERAGE over the last n cells, filled down so the window slides one row at a time. With monthly sales in column B from row 2, =AVERAGE(B2:B4) in C4 gives the 3-month moving average for March, and filling it down gives every month after. It smooths out month-to-month noise so the level of the series is easier to read.

The moving average formula in Excel

n-period moving average for month t = (value in t + value in t − 1 + ... + value in t − n + 1) / n

That is the single moving average in the NIST/SEMATECH e-Handbook of Statistical Methods: the mean of the latest N observations, recomputed as each new one arrives. In Excel, for a 3-month window, in C4:

=AVERAGE(B2:B4)

Fill down to the last month. For a trailing 12-month average, start in C13 with =AVERAGE(B2:B13).

The rows you need

One row per period, in date order, with no gaps:

A missing month must still be a row. If April has no sales, it needs a row with 0, or a deliberate decision to leave it blank. Microsoft's AVERAGE reference is explicit about the difference: empty cells and text in a range are ignored, but cells containing zero are included. A blank April turns a 3-month window into a 2-month average without any warning; a missing April row makes the window span four months of time.

Worked example: 12 months of sales

Monthly sales for 2025, with the 3-month trailing moving average (MA3) in column C from row 4.

The July dip from 1,010 to 870 barely moves the average (943.33 to 940.00), and the climb from September onward shows up as a steady rise from 896.67 to 1,136.67. That is the point of smoothing: one bad month is visible in column B and muted in column C.

The check

Recompute one window by hand. March: (820 + 760 + 905) / 3 = 2,485 / 3 = 828.33, matching C4. December: (1,050 + 1,120 + 1,240) / 3 = 3,410 / 3 = 1,136.67, matching C13.

A second check catches a fill that went wrong: click any cell in column C and confirm the range ends on its own row. C9 should read =AVERAGE(B7:B9). If it reads =AVERAGE(B2:B9), the first cell was typed with a locked start (F4) and the window is growing instead of moving.

A window size you can change

To switch between 3, 6 and 12 months without rewriting formulas, put the window size in a cell and name it N (Formulas > Define Name). Then, in C2, filled down:

=IF(ROW()-1<N,"",AVERAGE(OFFSET(B2,1-N,0,N,1)))

ROW()-1 is how many months exist so far, so the cell stays blank until a full window does. OFFSET returns a range N rows tall ending on the current row. With N = 3 it returns exactly the MA3 column above.

OFFSET is volatile: Microsoft's Excel recalculation guide lists it among the functions recalculated on every change anywhere in the workbook. On a few hundred rows that does not matter; on tens of thousands it slows the sheet. INDEX does the same job without being volatile:

=IF(ROW()-1<N,"",AVERAGE(INDEX(B:B,ROW()-N+1):B2))

Both formulas assume the data starts in row 2. Tested in Excel for Microsoft 365, both return 828.33 for March and 1,136.67 for December with N = 3.

Weighted moving average

A weighted moving average gives the latest months more say. With weights 1, 2 and 3, the newest month heaviest, in C4 filled down:

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

The divisor is the sum of the weights, 1 + 2 + 3 = 6. For December:

7,010 / 6 = 1,168.33, against 1,136.67 for the simple average. The weighted figure is higher because it leans on December, the strongest month, so it reacts faster to the year-end climb.

Rolling 12-month total vs average

Finance teams usually track a rolling 12-month total, also called the moving annual total, rather than an average. In row 13, filled down as new months arrive:

=SUM(B2:B13)

For December 2025 it is 11,415, the full calendar year; the 12-month average is 11,415 / 12 = 951.25. Next January the total drops January 2025 and adds January 2026, so every figure covers one of each month. That is why a 12-month window removes seasonality: a strong December and a weak February are both always inside it. To measure the seasonal pattern itself rather than remove it, see how to calculate a sales seasonality index in Excel.

The chart trendline and the Analysis ToolPak

Chart trendline. Select the sales series in a line chart, then Add Chart Element > Trendline > More Trendline Options > Moving Average, and set the period to 3. Excel draws the same line as column C, but the values are not written to cells, so you cannot reference or check them.

Analysis ToolPak. Data > Data Analysis > Moving Average asks for an input range, an interval and an output range. Run on the table above with interval 3, it writes =AVERAGE formulas into the output range, #N/A in the first two cells, and the same 828.33 to 1,136.67 series. It is a one-off run: new months need a rerun. Installing and using the add-in is covered in the Data Analysis ToolPak in Excel.

Where it goes wrong

• Averaging before a full window exists. =AVERAGE(B2:B3) in February is a 2-month average shown next to 3-month ones. Leave the first n − 1 rows blank.
• Missing months. If a month with no sales has no row, the 3-row window spans four months of time.
• Centered vs trailing. A centered average uses the months on both sides, including future ones, so it cannot be used as this month's reading.
• OFFSET on very large sheets. It is volatile and recalculates on every edit; use the INDEX form.
• Reading a turn as a trend change. A trailing average lags the data by (n − 1) / 2 periods: one month for a 3-month average, 5.5 months for a 12-month one. By the time the line turns, the series turned earlier. Level and trend in sales measures separates the two.

Rolling averages per customer and region

One workbook handles one series well. A sales file with 400 customers in 12 regions needs the same calculation thousands of times, with the gaps handled the same way each time. Covirage computes rolling averages and totals per customer, product or region from your uploaded ledger, handling missing months explicitly; the external AI model explains which series turned, never the arithmetic. See rolling averages, trend and seasonality for sales teams, and for projecting the series forward rather than smoothing it, the Excel FORECAST function. For the annual projection, see sales projection and the sales forecast template; for month-on-month change, percentage change in Excel.