Calculate Moving Average in Excel (Easy Guide)

Excel makes it super easy to calculate a moving average of values, also known as a rolling average. In this simple step by step guide we will run through the process taken in order to calculate a moving average in Excel – both manually and using a built in function.

How to Calculate Moving Average With a Formula

The formula itself to calculate a moving average is pretty straight forward since all we are really doing is selecting a series of values and performing a simple division – the key though is to make sure we select the right values.

Here we have an example of 12 months worth of sales that we will use as our sample dataset:

Let’s say we wanted to see what the moving average month on month was with an interval of 3 – meaning the last 3 months worth including the current month. What we would need to do here is start at March, and selecting Jan-Mar sum up the values and divide by 3:

Then we just drag down this formula so April calculates Feb-April, May calculates Mar-May and so on:

If we were interested in seeing moving averages over longer periods of time, we simply change the value of 3 to something higher. Let’s say we wanted to look longer term and see 6 month averages. The first 5 rows we can’t do anything with since there won’t be enough data for an average, but once we get to June we use the exact same formula, which will end up looking like the below:

Note that if you were to use the =AVERAGE formula above we would see the exact same results.

Enabling the Data Analysis ToolPak

Before we go any further and run through how to use the build in function to calculate Moving Average in Excel we will need to make sure that the Data Analysis ToolPak is enabled. If you already have this set up feel free to skip ahead, otherwise click here for our quick and easy guide on how to enable this. Once you are finished you should see the option appearing as below:

Using Excel’s built in Moving Average Function

The above manual formulas work nicely when using smaller datasets and only needing a couple of quick changes here and there, but to make life even easier we can use a built in function.

To use the Moving Average Function all we need to do is click on Data Analysis under the data ribbon, and then in the list of options that come up select Moving Average.

In the box that pops up we have 3 fields we will need to fill out:

Input Range: This is the overall dataset we are going to work with – in this case all 12 months of sales in cells C2 down to C14

Interval: The length of the moving average that we want to see. In this case we will start with 3 again like above.

Output Range: The cells that the calculations output will appear. This is where things differ slightly to the manual formula as we don’t just start down on the 3rd or 6th cell, we need to start at the top as well, and the interval selection will do the rest for us.

Completing this twice with intervals of 3 and 6, and the outputs of each in columns D and E, we are left with the below automatic output:

What makes this Excel function quite cool is that it isn’t simply pasting hard values into the cells. It actually enters what the manual formula would have been! You can copy and paste back over as values if for any reason you don’t need this, but having the actual formula that we used is super handy as it provides a bit more context on what we were calculating.

Visualizing our Moving Averages

Finally, let’s quickly look at how this would look when we make a visualization.

Line charts will be the best approach here, with our initial monthly sales values looking like the below:

With the seemingly random looking spikes it can be hard to see any trends in terms of growth or loss of sales. Let’s add two more lines now by re-creating the chart with all 3 sales columns included:

As we can see, the orange line for 3 months is smoother than the blue line, with the grey 6 month line being even smoother still. Being just one year of sales it’s not enough yet to see any longer term trends, but this kind of approach can be really beneficial when visualizing long term data.

This sums up our simple guide on how to calculate Moving Average in Excel. For more handy guides on working with Excel, be sure to check out our Excel Tips page.