Moving Average in Excel — Formula, Weighted & Exponential (2026)

To calculate a moving average in Excel, put your values in time order in column B and enter =AVERAGE(B2:B4) in the third row (C4), then fill down: each cell averages the latest three values and the window slides one row at a time. Change the size of the range for a different window, add a chart’s Moving Average trendline for a visual-only line, or use SUMPRODUCT for a weighted moving average.

Try it on your own file → · Updated 5 October 2026

Raw data is noisy — sales jump on payday, traffic dips at weekends, prices wobble daily. A moving average strips out that noise so you can see the trend underneath. In 35 minutes you’ll learn every way to calculate a moving average in Excel: the simple moving average with AVERAGE, a one-click chart trendline, weighted and exponential moving averages, and how to turn any of them into a next-period forecast. Works in Excel 2010 through Microsoft 365.

The DataHub Pro Team · Analytics for SMEs
LinkedIn · About

What is a Moving Average?

A moving average replaces each data point with the average of itself and a fixed number of neighbouring points, then “moves” that window one step at a time across the whole series. The result is a smoother line that follows the underlying direction of the data while filtering out short-term noise. It is the most widely used smoothing technique in business analytics precisely because it is intuitive: an average of recent values is something everyone understands.

Imagine daily sales that bounce between 80 and 120 units with no obvious pattern. Each day’s number tells you almost nothing — was today good, or just a normal wobble? A 7-day moving average answers that by collapsing each week of noise into a single representative value. Suddenly a gentle upward drift, invisible in the raw figures, becomes obvious. That is the entire point of a moving average: separate signal (the trend) from noise (the random day-to-day variation).

There are three flavours you will meet. The simple moving average (SMA) treats every period in the window equally. The weighted moving average (WMA) gives more recent periods a bigger say. The exponential moving average (EMA) goes further, letting influence decay smoothly so the newest data matters most but nothing is ever fully forgotten. They share the same goal and differ only in how quickly they react to change — a trade-off this guide will make concrete.

Moving averages also have a second job: forecasting. Because the average of the last few periods is often a decent estimate of the next, a moving average doubles as a simple prediction. It has a known weakness — it lags, so it always under-shoots a rising series and over-shoots a falling one — but as a transparent baseline it is hard to beat, and it is the foundation that more advanced methods like exponential smoothing build upon.

The Moving Average Formula in Excel — Worked Example

Here is every moving average formula in Excel on one small sheet, so you can check your own numbers against it. Six months of sales sit in B2:B7 with month names in column A. Column C is a 3-month simple moving average, column D a 3-month weighted moving average (weights 1, 2, 3), and column E an exponential moving average with α in cell H1 set to =2/(3+1), which is 0.5.

Simple moving average — in C4, then fill down to C7:

Weighted moving average — in D4, then fill down. The newest month gets weight 3 and the divisor is the sum of the weights, 6:

Exponential moving average — seed E2 with =B2, then in E3 enter the formula below and fill down. It blends each new value with the previous EMA:

The same EMA in one formula (Microsoft 365) — enter in E2 with E3:E7 empty and it spills down the column. SCAN starts from B2, so the first result equals the first value, exactly like the seed above:

Next-month forecast — the 3-month moving average of the last three actuals, placed in the July row (155.7 here):

Notice how the three methods disagree in June: sales dipped from 162 to 155, the weighted average (156.5) still leans on May’s high, the simple average (155.7) treats all three months alike, and the EMA (152.7) carries the whole history, including the low start. Which one is “right” depends on how much you want recent months to dominate — Steps 6 and 7 below explain the trade-off.

Three ways to do a moving average using Excel

Before You Start

A moving average has only two requirements, but both matter.

Data must be in time order

Each row must represent one period — one day, week, or month — sorted oldest to newest. A moving average is meaningless on unsorted data, because the “window” would mix unrelated periods. Sort by your date column ascending before you begin.

No gaps in the series

Missing periods distort the window. If a day has no sales, record a zero rather than leaving the row out — otherwise your 7-period window silently spans more than 7 calendar days. For genuinely missing data, decide deliberately whether to interpolate, carry forward, or treat as zero; do not let blanks slip in by accident.

Pick a window that matches your rhythm

The window size is the single most important choice. The rule of thumb: match the window to the cycle you want to remove. Daily data with a weekly rhythm → window of 7. Monthly data with annual seasonality → window of 12. There is no universally “correct” window — it is a deliberate trade-off between a smooth line and a responsive one, which the interactive chart below lets you feel directly.

1.Step 1 — Arrange Your Data

Put your period labels (dates) in column A and your values in column B, starting in row 2 with headers in row 1. Sort ascending by date. That is all the setup a moving average needs — one column of periods, one column of values, no gaps.

For the examples below, assume your values live in B2:B100. Add a header in C1 like “3-Period MA” for the column you are about to build.

2.Step 2 — The Simple Moving Average

The simple moving average is just AVERAGE over a sliding window. For a 3-period SMA, go to cell C4 (the third data row — the first row that has three values behind it) and enter:

Then grab the fill handle and drag down. Because the references are relative, C5 becomes =AVERAGE(B3:B5), C6 becomes =AVERAGE(B4:B6), and so on — the window slides down one row at a time exactly as it should. That is the entire mechanism of a moving average in one formula.

Changing the window size

The window size is simply how many cells the AVERAGE spans. For a 7-period moving average, start in C8 with =AVERAGE(B2:B8); for a 12-period, start in C13 with =AVERAGE(B2:B13). Larger windows give smoother, slower lines; smaller windows track the data more closely.

A window you can change without rewriting formulas

Put the window size in a cell (say $F$1) and use OFFSET so you can change the period in one place:

This averages the $F$1 cells ending at the current row. Type 5 in F1 and the whole column becomes a 5-period MA; type 10 and it instantly becomes a 10-period MA. OFFSET is volatile (it recalculates often), so prefer the plain AVERAGE approach on very large sheets.

3.Step 3 — Handle the Start of the Series

An N-period moving average cannot exist for the first N−1 rows — there simply are not enough prior values. If you drag =AVERAGE(B2:B4) up into C2 and C3, Excel will happily average a partial window and show a misleading early figure. The clean fix is to suppress those cells:

This returns an empty string until a full window of three real numbers exists, leaving the first two rows blank. On a chart, blank cells create a gap, so your smoothed line correctly starts at period 3 rather than dangling from a fake early value.

4.Step 4 — Add a Moving Average Trendline to a Chart

If you only want the smoothed line on a chart — not the values in cells — Excel can do it without a single formula. Select your data and insert a Line or Scatter chart. Click the data series to select it, right-click, and choose Add Trendline. In the Format Trendline pane, pick Moving Average and set the Period to your window size.

Excel overlays a smoothed trendline on your raw data instantly, and you can change the period in the pane to experiment. This is the fastest way to see a trend.

5.Step 5 — Trailing vs Centered

There are two ways to align a moving average to its window, and they answer different questions.

A trailing moving average — the kind from Step 2 — places each average at the latest period in its window (=AVERAGE(B2:B4) sits in row 4). You can compute it the moment the newest value arrives, which makes it the right choice for forecasting, dashboards, and anything real-time. Its downside is that it lags: it is always a little behind the true trend.

A centered moving average places each average at the middle of its window. For a 3-period centered MA, =AVERAGE(B2:B4) sits in row 3, not row 4. Centering removes the lag and gives the truest picture of the underlying trend and seasonality — but it needs future values, so you can never compute it for the most recent periods. Use centered for historical analysis, trailing for prediction.

For an even-numbered window, a true centered MA requires a “double” moving average (the 2×12 used in seasonal decomposition) because the centre falls between two periods.

6.Step 6 — The Weighted Moving Average (WMA)

A simple average treats a value from three days ago exactly like today’s — but in many series the most recent data is more relevant. A weighted moving average fixes this by multiplying each period by a weight and dividing by the total weight. With weights of 1, 2, 3 (oldest to newest) for a 3-period window:

The newest value (B4) carries three times the influence of the oldest (B2), so the line reacts faster to recent changes while still smoothing noise. The divisor 6 is the sum of the weights (1+2+3). To make the weights editable, put them in cells and use =SUMPRODUCT(B2:B4,$G$2:$G$4)/SUM($G$2:$G$4).

7.Step 7 — The Exponential Moving Average (EMA)

The exponential moving average is the most responsive of the three. Instead of a fixed window, it blends each new value with the previous EMA using a smoothing factor α (alpha) between 0 and 1. Every past period still contributes, but its influence decays exponentially the further back it is.

First choose α. A common convention links it to an equivalent window N: α = 2/(N+1), so a “10-period” EMA uses α ≈ 0.18. Then seed the first EMA cell with the actual first value (or the SMA of the first few). From the second period on:

Here $F$1 holds α, B3 is the current value, and D2 is the previous period’s EMA. Fill down and each cell builds on the one above it. A higher α tracks the data more tightly (less smoothing); a lower α produces a calmer line. EMAs are the workhorse of financial charts and demand sensing for exactly this reason — they react quickly without the abrupt “drop-off” that an SMA shows when an old spike leaves the window.

An EMA is the same calculation as single exponential smoothing. If your series also trends or repeats every season, the next step up is double or triple smoothing — see exponential smoothing in Excel for both, built cell by cell.

8.Step 8 — Forecasting with a Moving Average

To forecast the next period, place the average of the last N actual values in the next (empty) row. If your data ends at B100, the period-101 forecast is:

That is a 3-period moving-average forecast. It is transparent and surprisingly hard to beat on stable data, but be aware of its built-in limitation: a moving average always lags. On a rising series it forecasts low; on a falling series it forecasts high; and it cannot project a trend forward, only echo the recent level.

When your data has a genuine trend or seasonality, a moving average is the wrong forecasting tool. Reach instead for exponential smoothing with trend and seasonal components — the Holt-Winters method — or Excel’s built-in FORECAST.ETS. We cover that end to end in the Holt-Winters forecasting in Excel tutorial.

Try It: Live Smoothing Chart

This is the moving-average trade-off made visible. The faint line is 24 periods of noisy data; the bright line is its moving average. Drag the slider to change the window size and switch between Simple and Exponential to feel the difference: a small window hugs the noise, a large window glides smoothly but lags, and the EMA reacts faster than the SMA at the same setting — exactly what your Excel formulas produce.

Advanced Moving Average Tips

Date-range moving average with AVERAGEIFS

If your periods are real dates and you want a true rolling 30-day average (not a fixed row count), use =AVERAGEIFS(B:B,A:A,">"&A2-30,A:A,"<="&A2). This averages every value whose date falls in the 30 days up to the current row — robust even if some calendar days are missing.

Dynamic spilled MA in Microsoft 365

With dynamic arrays you can compute an entire moving-average column in one formula using SCAN or a BYROW/AVERAGE combination, so there is nothing to drag and nothing to break when data grows.

Compare two windows to judge momentum

Plotting a short MA and a long MA together is a classic technique: when the short crosses above the long, recent momentum is rising; when it crosses below, it is falling. It is the basis of the moving-average crossover used throughout finance and demand planning.

From smoothing to a live dashboard

Once your moving averages drive decisions, you will want them refreshed and shareable. Upload your spreadsheet to DataHub Pro and it builds an interactive, always-current chart with smoothing and forecasting built in — no formulas to maintain, a link to share instead of a screenshot.

Common Errors & Fixes

The moving average line is offset from the data

You have mixed trailing and centered alignment. Decide which you want and place the formula in the correct row consistently — trailing in the last row of the window, centered in the middle.

#DIV/0! in the moving average

The window contains only blank cells, or AVERAGE is pointed at text. Ensure the range holds numbers and use the Step 3 IF(COUNT(...)) guard for the start of the series.

The smoothed line looks identical to the raw line

Your window is too small (a 2-period MA barely smooths) or your data is not actually noisy. Increase the window and confirm you are charting the MA column, not the raw column twice.

EMA values look wrong from the first row

The seed is missing. The first EMA cell must be a plain actual value (or an SMA), not the recursive formula — the recursive formula has nothing to reference above it and will error or chain from a blank.