A moving average smooths out the ups and downs in a series of numbers so that you can see the trend. Instead of looking at each month on its own, you average the last few periods and move the window forward one step at a time. It is used for sales, website visits, stock prices, temperatures and inventory, and it is one of the simplest forecasting tools. In Excel you can build a moving average with a plain AVERAGE formula, with the SUMPRODUCT function for a weighted version, with a recursive formula for an exponential version, or with a chart trendline. This guide explains each method with a small data set that you can verify by hand, and it ends with tips for choosing the window, common mistakes and practice problems.
Updated November 2026
In this guide
- What is a moving average?
- The example data
- Method 1: simple moving average with AVERAGE
- Choose the window size
- Method 2: weighted moving average with SUMPRODUCT
- Method 3: exponential moving average
- Method 4: add a trendline to a chart
- Method 5: Data Analysis ToolPak
- Moving average in a rolling formula with OFFSET
- Use it to forecast
- Real-world examples
- Simple versus exponential: which one to use
- Handle missing data
- Common mistakes
- Practice problems
- Quick recap
What is a moving average?
A moving average is the average of a fixed number of the most recent values, recalculated for each new period. With a window of three, the value for month 3 is the average of months 1, 2 and 3, the value for month 4 is the average of months 2, 3 and 4, and so on. A larger window gives a smoother line but reacts more slowly to change. A smaller window follows the data more closely but keeps more noise.

The example data
We use six months of sales: 10, 12, 14, 13, 15 and 17.
| Month | Sales |
|---|---|
| 1 | 10 |
| 2 | 12 |
| 3 | 14 |
| 4 | 13 |
| 5 | 15 |
| 6 | 17 |
Method 1: simple moving average with AVERAGE
Put the sales in B2:B7. In D4, next to month 3, type =AVERAGE(B2:B4), and copy the formula down. The first value needs three data points, so the first two rows stay empty. The results are shown in the table below.
| Month | Sales | 3-month average |
|---|---|---|
| 3 | 14 | 12 |
| 4 | 13 | 13 |
| 5 | 15 | 14 |
| 6 | 17 | 15 |
Check the first value by hand: 10 plus 12 plus 14 is 36, and 36 divided by 3 is 12. The last value is 13 plus 15 plus 17, which is 45, and 45 divided by 3 is 15. The average rises by one each month, which shows a steady upward trend, even though the raw sales go up and down.
Choose the window size
The window should match the rhythm of your data. For weekly data with a weekly pattern use 7. For monthly data with seasons use 12 or 3. For noisy daily data, 5 or 10 days is common. With a window of 5 on our data, the first value is 10 plus 12 plus 14 plus 13 plus 15, which is 64, divided by 5 is 12.8, and the second is 12 plus 14 plus 13 plus 15 plus 17, which is 71, divided by 5 is 14.2. Compare it with the 3 month values to see how a longer window smooths more but starts later.
Method 2: weighted moving average with SUMPRODUCT
A weighted moving average gives more importance to recent values. Choose weights that add up to 1 or divide by their sum. For three periods with weights 1, 2 and 3, put the weights in F1:F3. The value for month 6 is 13 times 1, plus 15 times 2, plus 17 times 3, which is 13 plus 30 plus 51, so 94, divided by the sum of weights 6, which is 15.67. In Excel, use =SUMPRODUCT(B5:B7,$F$1:$F$3)/SUM($F$1:$F$3). This is higher than the simple average of 15 because the largest value gets the largest weight. If you want to learn more about this idea, see our weighted average guide.
Method 3: exponential moving average
An exponential moving average gives weights that fall off smoothly into the past. Choose a smoothing factor alpha between 0 and 1. The first value is the first data point. Each next value equals alpha times the new data plus 1 minus alpha times the previous average. With alpha equal to 0.5, the values are 10, then 0.5 times 12 plus 0.5 times 10, which is 11, then 12.5, then 12.75, then 13.875 and finally 15.4375 for month 6.
| Month | Sales | EMA (alpha 0.5) |
|---|---|---|
| 1 | 10 | 10 |
| 2 | 12 | 11 |
| 3 | 14 | 12.5 |
| 4 | 13 | 12.75 |
| 5 | 15 | 13.875 |
| 6 | 17 | 15.4375 |
In Excel, put 10 in C2, and in C3 type =0.5*B3+0.5*C2, then copy it down. A higher alpha reacts faster, and a lower alpha is smoother.
Method 4: add a trendline to a chart
If you only need the visual, make a line chart of the sales. Click the chart, choose Chart Elements, Trendline, More Options, and select Moving Average with the period of 3. Excel draws the smoothed line without any formula. This is quick, but the values are not in cells, so use the formulas when you need numbers.
Method 5: Data Analysis ToolPak
The ToolPak includes a Moving Average tool. Enable it in the Add-ins, then go to Data, Data Analysis, Moving Average, select the input range, enter the interval, choose an output range and tick Chart Output. It aligns each average with the last period of its window, and it fills the first rows with the error value because there is not enough data yet. It is handy for a one time job, but formulas update automatically when the data changes.
Moving average in a rolling formula with OFFSET
If you want the window size to be changeable from a cell, use OFFSET. With the window in F1 and the current row in B7, =AVERAGE(OFFSET(B7,1-$F$1,0,$F$1,1)) averages the last F1 values. Change F1 to 5 and the same formula gives a 5 period average. OFFSET is volatile, which means it recalculates often, so for huge files use fixed ranges.
Use it to forecast
The latest moving average is a simple forecast for the next period. With the 3 month average of 15 for month 6, the forecast for month 7 is 15. It does not capture a strong trend, since the data was rising and the real value will probably be higher, so it is best for stable series. For data with a clear trend, a forecast that uses a line, as in our correlation guide, can be better.
Real-world examples
A shop owner uses a 7 day average to smooth daily sales that are low on weekdays and high on weekends. A website owner uses a 30 day average of visits to see whether a change in content worked. A freelancer uses a 3 month average of income to plan expenses. A warehouse uses a 4 week average of demand to set the reorder level. In each case, the smoothed line answers the question of where the series is going. You can also find the percentage move between two averages with our percent change guide.
Simple versus exponential: which one to use
The simple average treats every value in the window the same and drops old values suddenly, which can make the line jump when a big value leaves the window. The exponential version fades old values gradually, so it is smoother and reacts faster to recent change. For reports that must be easy to explain to a client, the simple average is usually the best choice, because anyone can verify it. For fast moving data such as prices, the exponential version is more popular. Whatever you choose, state the window or the alpha in the report so that the reader can reproduce your numbers.
Handle missing data
If a month is missing, do not enter zero, because that pulls the average down. Leave the cell empty, since AVERAGE ignores blank cells, but remember that the window will then hold fewer values. Better still, fill the gap with an estimate and mark it clearly. For long gaps, calculate the average only over the periods that have data and note the break in a comment beside the table.
Common mistakes
The first mistake is to include too few or too many points in the window. The second is to put the average next to the first value of the window instead of the last, which shifts the line. The third is to use absolute references so the window does not move. The fourth is to forget that the first values cannot be calculated. The fifth is to use a long window on short data. The sixth is to treat the moving average as a prediction of a turning point, since it always lags behind the data.
Practice problems
Try these. Problem 1: find the 3 period averages of 4, 6, 8, 10. The results are 6 and 8. Problem 2: find the weighted average of 20, 30 and 40 with weights 1, 2 and 3. It is 20 plus 60 plus 120, which is 200, divided by 6, so 33.33. Problem 3: with alpha equal to 0.5, previous average 20 and new value 30, the new average is 25. Problem 4: build the 3 month and 5 month averages for your own data and plot them.
Quick recap
A moving average smooths a series by averaging the most recent values. Use AVERAGE for a simple one, SUMPRODUCT for a weighted one, and a recursive formula for an exponential one. Add a chart trendline for a quick picture, choose a window that fits your data, and remember that the result lags behind and is only a rough forecast.
Frequently asked questions
How do I calculate a moving average in Excel?
Use =AVERAGE over the last few cells and copy the formula down.
What is a weighted moving average?
It gives more weight to recent values.
How do I add a moving average to a chart?
Add a trendline and choose Moving Average.
What window size should I use?
Match it to the pattern of your data, such as 7 for daily data with a weekly cycle.
Does a moving average predict the future?
It gives a simple forecast, but it lags behind trends.


