Problem
You'd like to smooth a set of time series data by computing moving averages.
Solution
You can compute moving averages in several ways in Excel. You can use the builtin Moving Average trendline charting feature, the Analysis ToolPak Moving Average feature, or spreadsheet functions to compute moving averages.
Discussion
Excel allows you to compute moving averages in several ways. I'll show you three ways: using chart trendlines, using the Analysis ToolPak, and using spreadsheet formulas.
Moving Average trendline
As mentioned in Recipe 6.2, you can add a trendline to a chart by selecting the data series and then rightclicking to reveal a popup menu. You can then select Add Trendline to open the Add Trendline dialog box shown in Figure 64.
Figure 64. Add Trendline dialog box
You can select from six different types of trendlines. In this case, select the Moving Average type. To the right of the Moving Average icon, you should see a Period spinner control (as shown in the figure). Use the spinner arrows to select the period over which averages will be computed. You can also type in a number. In this example, I entered 3 so as to create a threeyear moving average trendline.
On the Options tab you can type in a name for the new trendline. Figure 65 shows the Options tab.
Figure 65. Add Trendline: Options tab
Moving Average trendlines have available only the "Trendline name" options. The other options apply to the other trendline types shown in Figure 64. (See Chapter 8 for examples using the other trendline types.)
Pressing OK closes the Add Trendline dialog box and adds the new trendline to your chart. Figure 66 shows the time series from Figure 62 and Figure 63 with a threeyear moving average trendline.
Figure 66. Threeyear moving average trendline
If you decide to modify the resulting moving average trendline, perhaps changing the averaging period, simply select the trendline on the chart and then rightclick to reveal a popup menu. Next, select Format Trendline from the popup menu to open the Format Trendline dialog box.

The Format Trendline dialog box includes the same the Add Trendline dialog box, where you can change the trendline type and any of its options. Further, the Format Trendline dialog box has an additional tab called Patterns that allows you to format the actual line drawn for the trendline. For example, you can change the line weight, color, and style.
Chart trendlines offer an easy way of creating moving average trendlines for time series data. However, this method does not allow you to extract the actual numerical values representing the moving average trendline. Also, this method requires that you first create a chart of your data. If you need numerical values for the trendline or you don't want to create a chart, then you can use one of the two following approaches.
Analysis ToolPak Moving Average
The Analysis ToolPak includes a Moving Average feature that allows you to compute moving averages in a manner similar to that of the Excel chart Moving Average trendline. The benefit of using the Analysis ToolPak is that you get the actual numerical data for the moving average series and not just a chart of the data.
To compute moving averages for a data series, select Tools Data Analysis... from the main menu bar to open the Data Analysis dialog box. Figure 67 shows the Data Analysis dialog box, where youre presented with a list of analysis tools to choose from.
Figure 67. Data Analysis dialog box
Select Moving Average from this list and press OK to open the Moving Average dialog box shown in Figure 68.
Figure 68. Moving Average dialog box
In the Input Range field, enter (or select directly on your spreadsheet) the cell range containing the y values of the data series for which you'd like to compute moving averages. For my example, cells O3 to O72 contained the relevant data.
Next, enter the Interval over which you'd like to compute averages. This is the same as the Period field used in the Trendline dialog box, as discussed earlier.
Finally, enter the topmost cell in the range where you'd like the output created. In this case I selected cell P3, to place the moving average data adjacent to the original time series data.
You may also elect to have a chart created automatically or have standard errors computed. Press OK to close the Moving Average dialog box and create the data. Your spreadsheet will now contain the new moving average data. Figure 69 shows a portion of the average annual temperature data series shown in Figure 66, along with the computed moving averages.
Figure 69. Moving Average results
Notice that the first two entries in the moving average series are #N/A. This is because the Moving Average tool computes moving averages based on the previous data points, and there are not enough points to compute the threeyear average at the beginning of the series.
In Figure 69, I intentionally selected one of the computed moving averages to show you that these results are not static numerical values. Instead, the Moving Average tool entered formulas to compute the moving average. The formula used is the standard AVERAGE spreadsheet formula, as shown in the formula bar in Figure 69. Using the spreadsheet formula in this way means that if your original data changes, the moving average will update accordingly.
At this point, you can perform other spreadsheet operations on the moving average data or plot the data as you wish. This approach gives you greater flexibility, as opposed to the trendline moving average method discussed earlier. However, what if you want to compute the moving averages over a period centered about each data point in the original series? You can use spreadsheet formulas to accomplish this, as discussed in the next section.
Spreadsheet formulas
Aside from the two previous approaches, you can always use simple spreadsheet formulas to compute moving averages. Specifically, you can use the AVERAGE formula, as shown in Figure 610.
Figure 610. Centered moving averages
Figure 610 shows the same original data and threeyear moving average data computed using the Analysis Toolpak from the previous section. Further, Figure 610 shows a new column of threeyear moving averages computed using the AVERAGE formula manually. To generate the new column, I entered the AVERAGE formula in Q4 with the cell range O3:O5 passed as the argument. The formula is =AVERAGE(O3:O5). This formula computes the average of the three original series data points centered over the second data point. Once I had that formula entered, I simply copied and pasted the same formula in all the remaining cells in column Q, corresponding to the original data. Using relative cell references meant that Excel automatically adjusted the cell range argument passed into the AVERAGE formula. For example, Figure 610 shows the resulting formula contained in cell Q6, along with the corresponding cell range argument.
The result of these simple steps yields a new data series containing threeyear centered moving averages. This approach is direct in that you don't need to use charts first if you don't want to, nor do you need to use the Analysis ToolPak. Further, this approach is flexible. For example, you can change the cell range passed into the AVERAGE formula to compute averages over any period of your choosing.
See Also
You can also compute weighted moving averages in Excel. See Recipe 6.4 to learn how.
Using Excel
Getting Acquainted with Visual Basic for Applications
Collecting and Cleaning Up Data
Charting
Statistical Analysis
Time Series Analysis
Mathematical Functions
Curve Fitting and Regression
Solving Equations
Numerical Integration and Differentiation
Solving Ordinary Differential Equations
Solving Partial Differential Equations
Performing Optimization Analyses in Excel
Introduction to Financial Calculations
Index