How to Calculate a Moving Average in Excel
By ExcelReady TeamUpdated 6 min read
The short answer
To calculate a 3-period moving average in Excel, put =AVERAGE(B2:B4) next to the third value and fill it down. Because the references are relative, each row averages its own value and the two before it, so the window moves one row at a time. Don't add $ signs, or the window stops moving and becomes a cumulative average.
Key takeaways
- A moving average is the average of the last few values, recalculated for every row.
- Start the formula on the row where the first full window ends, such as =AVERAGE(B2:B4) in row 4.
- Use relative references with no $ signs so the window slides down as you fill.
- For an average of the latest 3 values that updates as you add data, use OFFSET or INDEX with COUNT.
- Charts can draw a moving average for you with a trendline set to Moving Average.
On this page+
A moving average in Excel is the average of the last few values, worked out again for every row. You type one AVERAGE formula, such as =AVERAGE(B2:B4), and fill it down, and the three-cell window slides along the data. This guide is for anyone smoothing out noisy monthly sales, daily visits or weekly costs, and it covers the basic formula, the "latest 3 months" version and the chart trendline.
What is a moving average?#
A moving average (also called a rolling average) replaces each value with the average of a fixed number of recent values. A 3-month moving average for March is the average of January, February and March. For April it drops January and adds April. This smooths out one-off spikes, so the trend is easier to see.
All examples use this sales table in A1:B8:
| A | B | C | |
|---|---|---|---|
| 1 | Month | Sales | 3-month avg |
| 2 | Jan | 100 | |
| 3 | Feb | 120 | |
| 4 | Mar | 110 | 110 |
| 5 | Apr | 130 | 120 |
| 6 | May | 150 | 130 |
| 7 | Jun | 140 | 140 |
| 8 | Jul | 160 | 150 |
The raw sales go up and down from month to month. The moving average in column C rises steadily from 110 to 150, which shows the real direction.
How to calculate a moving average in Excel#
Start on the first row that has a full window of data, then fill down. For a 3-month window, that is row 4:
- Click C4.
- Type
=AVERAGE(B2:B4)and press Enter. The result is 110: (100 + 120 + 110) / 3. - Select C4:C8 and fill down (Windows: Ctrl+D; Mac: ⌘D). Or drag the fill handle at the bottom-right of C4.
=AVERAGE(B2:B4)Excel copies the formula and moves the range one row each time: C5 becomes =AVERAGE(B3:B5) (120), C6 becomes =AVERAGE(B4:B6) (130), and so on to C8, which is =AVERAGE(B6:B8) = 150. Leave C2 and C3 empty, because there aren't three months of data yet. The AVERAGE function guide covers the function itself, and AVERAGE, MIN and MAX explained shows it alongside its neighbours.
For a different window, change the size of the range. A 7-day moving average on daily data starts in row 8 with =AVERAGE(B2:B8). A 12-month moving average starts in row 13 with =AVERAGE(B2:B13).
Why you must not use $ signs in a moving average#
The moving window depends on relative references: cell addresses that shift when you copy the formula. If you lock the range with $, it stops moving.
| Formula in C4, filled to C8 | What happens | Result in C8 |
|---|---|---|
=AVERAGE(B2:B4) | Window moves; moving average | 150 |
=AVERAGE($B$2:B4) | Start locked, end moves; cumulative average | 130 |
=AVERAGE($B$2:$B$4) | Nothing moves; same answer in every row | 110 |
The middle one is useful in its own right: it is a cumulative (running) average of everything so far, like a running total in Excel but divided by the count. Just don't confuse it with a moving average. The guide to the dollar sign in Excel formulas explains the difference between $B$2, $B2 and B2. You can switch between them by pressing F4 on Windows or ⌘T on a Mac.
How to average the latest 3 months automatically#
Sometimes you want one cell that always shows the average of the last 3 values, even as you add new months. OFFSET can build a range that starts a set number of rows down:
=AVERAGE(OFFSET(B2,COUNT(B2:B13)-3,0,3,1))COUNT(B2:B13)counts the months filled in so far: 7.OFFSET(B2,7-3,0,3,1)starts 4 rows below B2 and is 3 rows tall: B6:B8.- AVERAGE of B6:B8 is 150.
Add August in B9 and the formula moves to B7:B9 by itself. Leave room for future months in the range (here up to B13) and keep the data in one block with no gaps, because COUNT only counts numbers.
OFFSET is volatile, which means it recalculates whenever anything in the workbook changes. That is fine in small files. In a large workbook, INDEX gives the same result without the extra work:
=AVERAGE(INDEX(B2:B13,COUNT(B2:B13)-2):INDEX(B2:B13,COUNT(B2:B13)))The first INDEX returns the 5th cell (B6) and the second returns the 7th (B8), and the colon joins them into the range B6:B8. The result is again 150.
Centred and weighted moving averages#
A trailing moving average, like the ones above, uses the current value and the ones before it. It is the usual choice for business reports because it only uses data you already have.
A centred moving average puts the result in the middle of the window. In C3, =AVERAGE(B2:B4) gives February the average of January, February and March. This lines up better with the original data, but you can't calculate the latest month until next month's data arrives.
A weighted moving average gives recent values more importance. For weights 1, 2 and 3 (the most recent month counts three times), use:
=SUMPRODUCT(B2:B4,{1,2,3})/6In row 4, that is (100×1 + 120×2 + 110×3) / 6 = 111.67. The 6 is the sum of the weights. See how to calculate a weighted average in Excel for more on SUMPRODUCT weights.
How to add a moving average to a chart#
If you only need to see the trend, a chart can draw the moving average for you:
- Make a line or column chart of the sales.
- Click the sales series, then click the + button next to the chart (Windows) or go to Chart Design > Add Chart Element > Trendline (Windows and Mac).
- Choose More Options (or More Trendline Options) and select Moving Average.
- Set Period to 3.
The trendline starts at the third point, just like the formula. It doesn't put the numbers in cells, so use the formula when you need the values for a report.
The Analysis ToolPak add-in also has a Moving Average tool under Data > Data Analysis. On Windows, turn it on in File > Options > Add-ins; on a Mac, use Tools > Excel Add-ins. It fills a column for you, but the formula approach is easier to check and update.
Common moving average mistakes#
- Starting in the wrong row.
=AVERAGE(B2:B4)belongs in row 4. Typing it in row 2 shifts every result two months early. - Locking the range with $. The window stops moving. Use plain relative references.
- Blank cells in the data. AVERAGE ignores empty cells, so a window with a missing month averages only two values. Fill gaps or mark them clearly.
- Zeros for missing months. A 0 does count, which drags the average down. The guide to averaging without zeros shows how to skip them with AVERAGEIF.
Practise it#
Try the exercise on this page: you finish a 3-month moving average by writing its last formula. Then practise AVERAGE, MIN and MAX in a live spreadsheet, and lock ranges correctly in the absolute references lesson. You type each formula and it checks it instantly.
Try it yourself
This is a live spreadsheet. Type the formula in the green cell and press Enter.
Column C holds a 3-month moving average. Finish it: in C8, average the sales for May, June and July (B6:B8).
| A | B | C | D | |
|---|---|---|---|---|
| 1 | 3-month avg | |||
| 2 | ||||
| 3 | ||||
| 4 | ||||
| 5 | ||||
| 6 | ||||
| 7 | ||||
| 8 | ||||
| 9 | ||||
| 10 | ||||
| 11 |
Tip: Follow the pattern in C7: each row averages its own month and the two above it.
Practise this in a lesson
Frequently asked questions
What is the formula for a moving average in Excel?
For a 3-period moving average with values in column B, type =AVERAGE(B2:B4) in the row of the third value and fill it down. Each row then averages its own value and the two above it.
How do I calculate a 7-day moving average in Excel?
Use a 7-cell window. With daily values starting in B2, type =AVERAGE(B2:B8) in row 8 and fill it down. Rows 2 to 7 stay empty because there aren't 7 days of data yet.
What is the difference between a moving average and a running average?
A moving average uses a fixed number of recent values, such as the last 3. A running (cumulative) average uses every value from the start, like =AVERAGE($B$2:B4), so it changes more slowly over time.
How do I add a moving average to an Excel chart?
Click the data series in a line or column chart, choose Add Trendline, select Moving Average and set the Period, for example 3. Excel draws the smoothed line on the chart.
Why does my moving average not change when I fill it down?
The range probably has $ signs, such as $B$2:$B$4, which lock it in place. Remove them so the range moves down one row with each cell.