How to Calculate Moving Average in Excel: A Step-by-Step Guide

As an Excel user performing data analysis, you’ll often need to analyze trends and smooth out fluctuations in time series data.

That’s where calculating the moving average comes in handy!

The moving average is a simple yet powerful analytical tool that helps identify trends cycles and standalone outliers in time series data.

By smoothing the data points, it filters out short-term noise and highlights the bigger picture.

In this comprehensive guide. I’ll walk you through everything you need to know to calculate moving averages in Excel with simple step-by-step instructions

What is a Moving Average?

A moving average is a statistic used to analyze data points in a time series by averaging a fixed number of adjacent data points

It’s called a moving average because the average moves along the time series as you apply it to consecutive subsets of the data.

For example, you can calculate a 5-period moving average of a monthly data series. The first data point would be the average of months 1-5, the second would cover months 2-6, and so on.

Moving averages are a key trend analysis indicator used in fields like finance, meteorology, manufacturing, and more.

Common types include simple, weighted, and exponential moving averages. The simple moving average weighs data points equally while the others assign variable weights.

Why Use a Moving Average?

Here are some key uses of moving averages in analyzing time series data:

Smoothing out fluctuations: Moving averages remove noise and outliers to reveal the underlying pattern.
Identifying trends: Changes in moving average direction signify trend reversals.
Determining support/resistance levels: In finance, moving averages act as dynamic support and resistance for security prices.
Trading crossover signals: Golden/death crosses with price crossing moving average generate buy/sell signals.
Forecasting: Moving averages are used in time series methods like ARIMA for forecasting.
Monitoring production: They track periodic changes in manufacturing cycle times.
Weather analysis: Meteorologists use moving averages to monitor temperature/precipitation changes.

Clearly, the moving average is indispensable for time series analysis across domains. Let’s see how to calculate it in Excel.

Moving Average in Excel Using Data Analysis Tool

The easiest way to calculate moving averages in Excel is using the Moving Average option in the Data Analysis tool:

Click Data > Data Analysis to open the Data Analysis dialog.
Select Moving Average and click OK.
In the Moving Average dialog:
- Input range: Select your time series data
- Interval: Specify period for moving average
- Output range: Select output cell
Click OK. The moving averages get calculated in the output range.
Optional: Create chart with moving average line to visualize the smoothing effect.

The Data Analysis tool computes moving averages with a single click. But it doesn’t allow further analysis. So let’s also see formulas.

Calculate Simple Moving Average in Excel

The formula for simple moving average is:

SMA = (Sum of data values in period) / Number of periods

So for a 5-period moving average, you sum the first 5 values and divide by 5. Then move the average period one position ahead.

Here are the steps to calculate simple moving average in Excel using the formula:

In cell C2, enter the formula =AVERAGE(B2:B6)
- Here we calculate 5-period SMA for first value
Copy the formula down column C to get SMA values for the entire time series

You can compute moving averages over any period by changing the range in AVERAGE.

Let’s say you want 10-period SMA. Use =AVERAGE(B2:B11) for cell C2 and copy down.

The simple moving average gives equal weight to all data points. But for weighted averages as per our need, we can use:

Calculate Weighted Moving Average in Excel

The weighted moving average assigns unequal weights to data points. The most common weighting scheme is linear weighting.

The formula is:

WMA = (W1P1 + W2P2 + … Wn*Pn) / (W1 + W2 + … Wn)

Where W is the weight assigned to each data point P.

Here are the steps:

In D2, enter formula =SUMPRODUCT(B2:B6,ROW(B2:B6))/SUM(ROW(B2:B6))
- ROW numbers are used as linear weights
Copy down column D to calculate weighted moving average across the dataset

This gives higher weight to recent data points compared to simple average.

Calculate Exponential Moving Average in Excel

Exponential moving average (EMA) applies weighting factors which decay exponentially. The EMA gives higher weight to recent observations.

The formula is:

EMA = (ClosingPrice – EMAprev) x Smoothing/Period + EMAprev

Where:

EMAprev = Exponential MA for previous period
Smoothing = Smoothing factor between 0-1
Period = Number of time periods

Let’s calculate 10-period EMA with smoothing of 0.5:

In cell E2, enter =(B2-B2)*0.5/10+B2
- First EMA is always equal to first data point
In E3, enter =(B3-E2)*0.5/10+E2
Copy the formula down column E

Adjust the smoothing factor and period as needed. Higher smoothing gives more weight to recent data.

Moving Average Excel Formula for Any Period

Instead of fixed ranges, we can use INDEX and COUNTA functions to make a dynamic moving average formula that calculates SMA for any period:

=AVERAGE(INDEX(data_range,COUNTA(data_range)-(period-1)):INDEX(data_range,COUNTA(data_range)))

Where:

data_range = Time series data
period = Number of periods for moving average calculation

Let’s break this down:

INDEX returns a value based on row number from the data range
COUNTA gives the total rows in data range
By taking COUNTA minus period + 1, we get the start row for moving average period
Together, INDEX and COUNTA give us flexibility to specify any period

This formula works for simple, weighted and exponential averages. Just combine it with the respective formula.

Moving Average Chart in Excel

Visualizing the moving average smoothing effect on the time series through a chart is very useful.

Here are the steps to create a moving average chart in Excel:

Select the moving average and original time series data
Go to Insert tab and click Line Chart
The chart will display both series – original volatile data and smoothed trend

Format and customize the chart as needed. The moving average line makes the underlying patterns and trends far more apparent at a glance.

Things to Keep in Mind

Here are some important considerations when using moving averages:

Balance between smoothing and responsiveness by choosing the right period
Use centered moving average for alignment when using even timespans
Exponential moving average needs sufficient historical data to stabilize
Plot moving averages for multiple periods together for comparison
Consider moving averages alongside other technical indicators for robust signals
Assess performance regularly and tweak periods and smoothing factors accordingly

Calculating moving averages is indispensable for gaining meaningful insights from time series data across diverse domains.

This step-by-step Excel guide showed you how to:

Compute simple, weighted and exponential moving averages using formulas
Make dynamic averaging formulas flexible for any period
Leverage Excel’s inbuilt Data Analysis tool
Create moving average charts for visual analysis

So now you have all the knowledge to skillfully use moving averages in analyzing trends, forecasting future values, smoothing noisy data and more!

Armed with these techniques, you can make your time series data analysis more efficient, accurate and impactful.

how to calculate moving average in excel

Variable periods with OFFSET

A more flexible way to calculate a moving average is with the OFFSET function. OFFSET can create a dynamic range, which means we can set up a formula where the number of periods is variable. The general form is:

where n is the number of periods to include in each average. As above, OFFSET returns a range that is passed into the AVERAGE function. Below you can see this formula in action, where n is the named range E2. Starting at cell C5, OFFSET constructs a range that extends back to previous rows. This is accomplished by using a height equal to negative n. When E5 is changed to another number, the moving average recalculates on all rows:

The formula in E5, copied down, is:

Like the original formula above, the version with OFFSET will also have the problem of insufficient data in the first few rows, depending on how many periods are given in E5.

In the example shown, the averages calculate successfully because the AVERAGE function automatically ignores text values and blank cells, and there are no other numeric values above C5. So, while the range passed into AVERAGE in E5 is C1:C5, there is only one value to average, 100. However, as periods increase, OFFSET will continue to create a range that extends above the start of the data, eventually running into the top of the worksheet and returning a #REF error.

One solution is to “cap” the size of the range to the number of data points available. This can be done by using the MIN function to restrict the number used for height as seen below:

This looks pretty scary but is actually quite simple. We are limiting the height passed into OFFSET with the MIN function:

Inside MIN, the first value is a relative row number, calculated with:

The second value given to MIN is the number of periods, n. When the relative row number is less than n, MIN returns the current row number to OFFSET for height. When the row number is greater than n, MIN returns n. In other words, MIN simply returns the smaller of the two values.

A nice feature of the OFFSET option is that n can be easily changed. If we change n to 7 and plot the results, we get a chart like this:

Note: A quirk with the OFFSET formulas above is that they wont work in Google Sheets, because the OFFSET function in Sheets wont allow a negative value for height or width. The attached spreadsheet has workaround formulas for Google Sheets.

Author

Hi – Im Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.

Download 100+ Important Excel Functions

Learn Excel with high quality video training. Our videos are quick, clean, and to the point, so you can learn Excel in less time, and easily review key topics when needed. Each video comes with its own practice worksheet.

How to Create a Moving Average in Excel

How to calculate a moving or rolling average?

To calculate a moving or rolling average, you can use a simple formula based on the AVERAGE function with relative references. In the example shown, the formula in E7 is: As the formula is copied down, it calculates a 3-day moving average based on the sales value for the current day and the two previous days.

How to plot a moving average in Excel?

4. Click in the Input Range box and select the range B2:M2. 5. Click in the Interval box and type 6. 6. Click in the Output Range box and select cell B3. 7. Click OK. 8. Plot a graph of these values. Explanation: because we set the interval to 6, the moving average is the average of the previous 5 data points and the current data point.

How do I use a moving average?

A moving average is used to smooth out irregularities (peaks and valleys) to easily recognize trends. 1. First, let’s take a look at our time series. 2. On the Data tab, in the Analysis group, click Data Analysis. Note: can’t find the Data Analysis button? Click here to load the Analysis ToolPak add-in. 3. Select Moving Average and click OK. 4.

How to calculate moving average for weight 2 in Excel?

But first, we must calculate the moving average for weight 2. Step 1: Go to the Data tab and select the Data Analysis option in the Analysis group. Step 2: You get a data analysis pop-up window. Select the option “Moving Average.” Step 3: Now, you get a pop-up window. Enter the input range, which is the range of the data set.