How to Calculate a Moving Average in Excel: Simple, Weighted and Exponential

shape
shape
shape
shape
shape
shape
shape
shape
How to Calculate a Moving Average in Excel: Simple, Weighted and Exponential - featured image

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

  1. What is a moving average?
  2. The example data
  3. Method 1: simple moving average with AVERAGE
  4. Choose the window size
  5. Method 2: weighted moving average with SUMPRODUCT
  6. Method 3: exponential moving average
  7. Method 4: add a trendline to a chart
  8. Method 5: Data Analysis ToolPak
  9. Moving average in a rolling formula with OFFSET
  10. Use it to forecast
  11. Real-world examples
  12. Simple versus exponential: which one to use
  13. Handle missing data
  14. Common mistakes
  15. Practice problems
  16. 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.

Average Calculator screenshot used in the guide: How to Calculate a Moving Average in Excel
Check your answer with our free Average Calculator.

The example data

We use six months of sales: 10, 12, 14, 13, 15 and 17.

MonthSales
110
212
314
413
515
617

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.

MonthSales3-month average
31412
41313
51514
61715

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.

MonthSalesEMA (alpha 0.5)
11010
21211
31412.5
41312.75
51513.875
61715.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.

Related guides and free tools

Leave a Reply

Your email address will not be published. Required fields are marked *