A moving average is a technique that can be used to smooth out time series data to reduce the “noise” in the data and more easily identify patterns and trends.
The whole idea behind a moving average is to take the average of a certain number of previous periods to come up with an “average” for a given period.
In this tutorial, we show how to find moving averages for time series data using a built-in function in Excel.
Example: Moving Averages in Excel
Suppose we have the following dataset that shows the sales for a certain company during 10 periods:
Perform the following steps to find moving averages for this time series data.
Step 1: Click on the “Data Analysis” button.
Go to the “Data” tab along the top ribbon and click the “Data Analysis” button. If you don’t see this button, you need to first load the Excel Analysis ToolPak, which is completely free to use.
Step 2: Choose the “Moving Average” option and click OK.
Step 3: Fill in the necessary values.
- Fill in the data values for Input Range.
- Choose a value for Interval. This determines how many periods will be used to compute an average. In this example we chose to use 3.
- Select the Output Range where you’d like the moving average values to appear. It’s a good idea to choose this output range right next to your actual data values so you can easily compare the actual values and the moving average values side by side.
- If you would like to see a chart displayed with the actual values and the moving average values, select the box that says Chart Output.
Then, click OK.
A list of moving average values and a chart will automatically appear:
Note that the first two time periods have a value of #N/A because there weren’t enough previous time periods that could be used to compute the moving average.
The moving average value for each period is calculated as the average of the sales for the current period along with the previous two periods (for a total of three periods used).
For example, the moving average value for period 3 is calculated as (50+55+36) / 3 = 47.
Similarly, the moving average period 4 is calculated as (55+36+49) / 3 = 46.67.
The moving average for each period is calculated in a similar manner.
Experimenting with Different Values for the Interval
You can experiment with different values for the interval and see how it impacts the moving average values. You’ll notice that the larger the interval, the more smoothed out the moving average line will be:
For more tutorials in Excel, be sure to check out our complete list of Excel Guides.