Excel Moving Average
By Richard Rost

5 years ago

Calculate Moving Average in Microsoft Excel

S M L XL FS | Slo Reg Fast 2x | Bookmark

In this video, I'll show you how to calculate a standard moving average (also called a rolling average, moving mean, or rolling mean) in Microsoft Excel.

Learn More!

Questions?

Please feel free to post your questions or comments below. Thanks.

Keywords

#msexcel, #microsoftexcel, #microsoft excel, #excel, #exceltips, moving average, moving mean, rolling average, rolling mean, simple moving average, data analysis, analyze data, analysis toolpak

Subscribe to Excel Moving Average
Get notifications when this page is updated

Intro

In this video, I will show you how to calculate a moving average, also known as a rolling average, in Microsoft Excel. We will look at how to use the AVERAGE function with relative references for a simple moving average, and I will demonstrate how to apply this formula using the autofill handle. I will also talk about how to use the Analysis ToolPak add-in to create moving averages and include a chart for visualizing your data. This is a quick tip video to help you smooth out your data using moving averages in Microsoft Excel.

Transcript

Welcome to another fast tip video brought to you by Excel LearningZone.com. I am your instructor Richard Rost. In this video, I'm going to show you how to calculate a moving average, also called a rolling average, in Microsoft Excel.

Here I am in Excel and I have a list of dates along with values for those dates. These could be stock prices. These could be order amounts, whatever you want to track.

A moving average, or a rolling average, is simply taking the average over a specific number of intervals. You can do a rolling average over five days, seven days, thirty days, a hundred days, whatever.

Let's say you want to do a moving average over the last five days. The first day you could do an average for would be right here because you have five data points. So it would be the average of those five. Then the average for the sixth would be averaging those five - the previous five values. By the time you get down to here, your moving average is now the average of these. That's how a moving average works.

This is a simple moving average. There is also something called an exponential moving average. We will cover that in a different video.

To calculate the moving average, we just use the average function and make sure you have a relative reference to the cells that you want to average. Here, for example, in cell C5, enter =AVERAGE( and then just select the previous five days, including that one of course. Press enter. There is the moving average over these five days. If you select those again, you can see down here average, 15.

How do you bring this down for the rest of them? Just use the autofill handle. Look at that. There's your moving average. We are going to ignore those errors. It's just that this formula omits an adjacent cell.

There you go. That's the easiest way to calculate a moving average - the average of those, the average of those, and so on. That's your fast tip.

I want to go into a little bit more detail. There's a thing called the Analysis ToolPak that you can install. It comes with Excel, but it's not installed by default.

If you go to Data, it gives you a button right over here under Data Analysis. You might not see this button. If you don't see that button, go to File, come down to Options, go to Add-ins, and then you'll see the Analysis ToolPak. It will be down here under Inactive. Click on it and then click Go down here. Then hit OK, and you'll see this button is added right there.

It's not the same as that. That confuses a lot of people. Under Analysis on the Home tab, you have Analyze Data. Totally different. I don't know why they named these so similar. It's under Data, and then Data Analysis.

Let me get rid of this. Let me show you what this button does. Click Data Analysis. You get this window. There are all kinds of different options in here - regression, sampling, t-test. I don't even know what some of this stuff is. But there's moving average right there. Hit OK.

Your input range, Excel does its best to try and guess for you. It would be B1 to B16 right there. How many intervals do you want? You could do five, six, ten, whatever. Where do you want the output range to go? I want it to go right there. Chart output is going to give you a nifty little chart to go with it. It saves you a step from having to make a chart.

Hit OK. There you go. Now, it can't calculate a moving average because there are not enough data points up there. We'll just delete that. Again, we'll ignore these errors. Ignore. There you go. There's your handy little moving average, and you get a nifty little chart here. You can see how the moving average kind of smooths out the data points. You don't have these big jumps and peaks and valleys here. It kind of averages out. There you go. There's your moving average.

Want to learn more Excel? Come visit my website for many more free videos, including an hour-long Excel beginner course, absolutely free. Make sure you let me know. Email me and say, I want more Excel. Most of my videos are for Microsoft Access - that's what I mostly do. But I also know Excel. If you want to learn more Excel, email me and let me know. I want more Excel. See you soon.

Quiz

Q1. What is a moving average in Excel?
A. An average calculated over a specific number of intervals to smooth data
B. The sum of all values in a column
C. A calculation of only the highest and lowest values
D. The total of all numbers divided by two

Q2. What is another name for a moving average?
A. Simple sum
B. Rolling average
C. Data analysis average
D. Peak average

Q3. When can you first calculate a five-day moving average in a data list?
A. On the first data point
B. After you have five data points
C. After all data has been entered
D. Immediately after entering the second data point

Q4. How do you create a moving average formula in Excel manually?
A. Use the MAX function
B. Use the SUM function only
C. Use the AVERAGE function with a range of previous intervals
D. Use the MIN function on adjacent cells

Q5. What allows you to quickly duplicate the moving average formula for the rest of your data?
A. Copy and paste
B. The autofill handle
C. Manual entry of the formula each time
D. The cut and insert tool

Q6. What should you do about the error messages that appear at the top of the moving average column?
A. Fix them immediately
B. Ignore them if not enough data points are available
C. Change the formula to SUM
D. Delete the entire column

Q7. What is the Excel add-in that provides built-in moving average analysis?
A. Data Sorter
B. Pivot Table Generator
C. Analysis ToolPak
D. Chart Wizard

Q8. How can you enable the Analysis ToolPak if it is not already installed?
A. Go to the Home tab and click Sort
B. Go to File, then Options, Add-ins, and activate Analysis ToolPak
C. Download it separately from Excel.com
D. Restart Excel twice

Q9. Where is the Data Analysis button found after enabling Analysis ToolPak?
A. On the Home tab under Analyze Data
B. Under the Insert tab in Charts
C. On the Data tab under Data Analysis
D. Under Page Layout

Q10. What is a benefit of using the moving average tool in the Analysis ToolPak, besides the calculation?
A. It creates a backup of your data
B. It generates a chart of your moving average automatically
C. It formats your data as a table
D. It highlights errors in red

Q11. What is NOT true about the moving average as discussed in the video?
A. It smooths out sharp peaks and valleys in your data
B. It only works on text data
C. It is useful for tracking trends
D. It reduces the impact of random fluctuations

Answers: 1-A; 2-B; 3-B; 4-C; 5-B; 6-B; 7-C; 8-B; 9-C; 10-B; 11-B

DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.

Summary

Today's video from Excel Learning Zone explores how to calculate a moving average, also known as a rolling average, in Microsoft Excel.

I start with a spreadsheet containing a column of dates and corresponding values. These could represent anything you want to track over time, such as stock prices or sales amounts. The concept of a moving average is straightforward: it calculates the average of a certain number of recent data points, which helps to smooth out short-term variations and highlight longer-term trends. You can choose any interval that makes sense for your data, such as five, seven, thirty, or even one hundred days.

If you want to set up a moving average for the last five days' worth of data, you need at least five data points before you can begin calculating the average. The first moving average value would be based on these first five points. For the next row, you take the average of the previous five values, and so on, continuing this process down your list. This sequential calculation is known as a simple moving average.

There are other approaches too, such as the exponential moving average, which gives more weight to recent data points, but I will cover that topic separately in another lesson.

To calculate the simple moving average in Excel, use the AVERAGE function. Enter it in the relevant cell and select the current and previous four values to cover a five-day window. After calculating the average in one cell, you can use Excel's autofill feature to copy the formula down the column for the remaining rows. Errors may appear for the first few rows, as you need at least as many data points as the size of your moving average, but these can safely be ignored since they're a result of insufficient data.

This quick method is the easiest way to set up a moving average: just use the AVERAGE formula over your chosen interval, and drag it down.

If you want a slightly more automated and advanced approach, Excel includes a tool called the Analysis ToolPak. This is an add-in that is not installed by default, but you can set it up easily. To use it, go to the Data tab. If you do not see the Data Analysis button, you will need to install the ToolPak. This can be done by going to File, choosing Options, then Add-ins. Find Analysis ToolPak in the list of inactive add-ins, click to activate it, and confirm with OK. Once installed, the Data Analysis button will appear on the Data tab.

Be careful not to confuse the Data Analysis button on the Data tab with the Analyze Data feature on the Home tab. Despite the similar names, they are very different tools.

With the ToolPak installed, click Data Analysis, which opens a window full of advanced tools, including regression analysis, sampling, and t-tests. Look for the Moving Average option and select it. Excel will ask for your input range, which should be the list of values you want to analyze. Enter the interval for your moving average, and specify where you want the output to appear. You can also choose to have a chart generated automatically.

Once you apply the moving average analysis, Excel will calculate the new values for you and give you a helpful chart that visually shows how the moving average smooths out fluctuations in your data. This graphical display makes it easier to observe trends without being distracted by short-term peaks and valleys.

To learn even more about Excel, visit my website where you will find many free tutorials, including a comprehensive Excel beginners course that lasts a full hour. Most of my content focuses on Microsoft Access, but I also produce Excel tutorials. If you are interested in more Excel lessons, let me know by sending an email.

You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.

Topic List

Calculating a moving average using the AVERAGE function

Setting up data for a rolling average calculation

Applying relative references for moving average formulas

Using the autofill handle to extend the moving average

Installing and enabling the Analysis ToolPak add-in

Accessing the Data Analysis tools in Excel

Using the Moving Average tool from Data Analysis

Configuring input and output ranges for the Moving Average tool

Setting intervals for the moving average calculation

Generating a moving average chart automatically

Understanding how moving averages smooth out data fluctuations

Links

Learn More!

Questions?

Keywords