A moving average shows the trend hidden behind data that jumps up and down. You average the last few values, then slide that window down one row at a time. In Excel, you only need the AVERAGE function.
Try it right now
👉 In cell D4, write the formula that calculates the 3-month moving average for January to March. This is a real spreadsheet: type in it as you would in Excel.
Why use a moving average?
Here are the daily temperatures (in Celsius) in New York in 2020, and the line chart built from them.
We can see the seasons: colder in winter, warmer in summer. But the curve has many spikes. Between January 4 and January 9, the temperature dropped by 12 °C. Then, between January 9 and January 12, it rose by 17 °C.
With so many ups and downs, this chart is hard to read: you can’t see a clear trend. Is it getting warmer or colder this week? Impossible to say.
This is exactly where the moving average is useful. It smooths out daily spikes and shows the data’s real direction 😮
Write the moving average formula
A moving average is simply an average. For a 10-day moving average, average the first 10 values. In cell C11 (the row of the 10th day), write:
=AVERAGE(B2:B11)
No $ in the formula: this is what makes it “move”
Now copy the formula down to the last row. This is the most important part of the technique: write the reference B2:B11 without any $ sign.
Without $, the reference is relative. Each time you copy the formula one row down, Excel moves the range one row down too:
| Cell | Formula | Days averaged |
|---|---|---|
| C11 | =AVERAGE(B2:B11) | days 1 to 10 |
| C12 | =AVERAGE(B3:B12) | days 2 to 11 |
| C13 | =AVERAGE(B4:B13) | days 3 to 12 |
Each row drops the oldest day and adds a new one. The window of 10 days “moves” along the year: that is why it is called a moving average.
⚠️ If you write =AVERAGE($B$2:$B$11), the range is locked. Every copy calculates the average of the same 10 days, and you get the same value on every row: a flat line, not a moving average. To understand the difference, read why there is a $ in Excel formulas.
The trend over 10 days
Then add column C to your chart (the quickest way is a copy-paste: see How to Add a New Line to an Excel Chart with Copy-Paste). With the 10-day moving average, the trend is much easier to read than with the daily values.
And with a 20-day moving average
If you increase the moving average to 20 days, the spikes are even smoother. They become less pronounced, and the trend is easier to see. For example, from September to December, the curve shows a steady drop in temperature.
Going further: the weighted and exponential moving averages
The simple moving average has one weakness: every day of the window counts the same. The temperature of 10 days ago weighs as much as today’s, so the curve reacts late when the trend changes.
Two other formulas improve the principle by giving more weight to the most recent days:
- the weighted moving average (WMA), calculated with SUMPRODUCT;
- the exponential moving average (EMA), the one used in finance and trading.
The difference is easy to see on a chart. On January 11, the temperature jumps from 5 to 14 °C. The simple moving average only reaches 5.7, while the weighted and exponential averages reach 8.3 and 8.5: they follow the change at once.
To build them step by step in Excel, and to know which one to use, read Weighted and Exponential Moving Average in Excel.
Related Articles
- Bollinger Bands with Excel
- Understand the Standard Deviation
- TOP 10 Most Useful Statistical Functions for Data Analysis
- Tutorial for the Excel Functions AVERAGE, MIN, and MAX
- Training with SUM, MIN, MAX and AVERAGE functions
- How to Add a New Line to an Excel Chart with Copy-Paste
- Weighted and Exponential Moving Average in Excel
