Moving Average in Excel

In this tutorial we will learn how to compute Moving Average in Excel. Moving Average can be defined as the Mean of time series data from several consecutive periods. It is called ‘moving’ because it is continually recomputed as new data becomes available, it progresses by dropping the earliest value and adding the latest value.

Data:

The monthly sales data that we use for computing moving average in Excel is shown below

Moving Average in Excel 1

 

Step 1: First, let’s plot our time series, which is shown below.

Moving Average in Excel 2

 

Step 2: On the top right corner of the data tab click data analysis.

Moving Average in Excel 3

Note: if you can’t able to find the Data Analysis button? Click here to load the Analysis ToolPak add-in.

 

Step 3: Select the Moving Average and click ok.

Moving Average in Excel 4

 

Step 4: select the input range, interval=2 and output range as shown below. Interval value is the interval at which the moving average is calculated.

Moving Average in Excel 5

So the output will be

Moving Average in Excel 5b

 

Step 5: Calculate the moving average for interval =4 and interval=6 as shown in step 4. So the resultant and table and chart will be.

Moving Average in Excel 6

As you can clearly see when the interval for moving average increases the line gets smoother. The smaller the interval, the moving average is more close to the data points which is less smooth.

previous small moving average in Excel                                                                                                                next small moving average in Excel