But then, I decided to write one myself (the fact that I was somewhat of a statistics nerd in college also played a minor role). Now, before I tell you how to calculate moving average in Excel, let me quickly give you an overview of what moving average mean and what types of moving averages are there. In case you want to jump to the part where I show how to calculate moving average in Excel, click here.

What is a Moving Average?

I am sure you know what’s an average value. If I have three days of daily temperature data, you can easily tell me the average of the last three days (hint: you can use the AVERAGE function in Excel to do this). A Moving Average (also called as the rolling average or running average) is when you keep the time period of the average the same, but keeps moving as new data is added. For example, on Day 3, if I ask you the 3-day moving average temperature, you will give me the average temperature value of Day 1, 2  and 3. And if on Day 4 I ask you the 3-day moving average temperature, you will give me the average of Day 2, 3, and 4. As new data is added, you keep the time period (3 days) the same but use the latest data to calculate the moving average. Moving average is heavily used for technical analysis and a lot of banks and stock-market analysts use it on a daily basis (below is an example I got from the Market Realist site).

Types of Moving Averages

There are three types of moving averages:

Simple moving average (SMA) Weighted moving average (WMA) Exponential moving average (EMA)

Simple Moving Average (SMA)

This is the simple average of the data points in the given duration. In our daily temperature example, when you simply take an average of the past 10 days, it gives the 10-day simple moving average. This can be achieved by averaging the data points in the given duration. In Excel, you can do this easily using the AVERAGE function (this is covered later in this tutorial).

Weighted Moving Average (WMA)

Let’s say that the weather is getting cooler with every passing day and you are using a 10-day moving average to get the temperature trend. Day-10 temperature is more likely to be a better indicator of the trend as compared to Day-1 (since the temperature is dropping with every passing day). So, we are better off if we rely more on the value of Day 10. To make this reflect in our moving average, you can give more weight to the latest data and less to past data. This way, you still get the trend, but with more influence of the latest data. This is called the weighted moving average.

Exponential Moving Average (EMA)

The exponential moving average is a type of weighted moving average where more weight is given to the latest data and it decreases exponentially for the older data points. It is also called the Exponential Weighted Moving Average (EWMA) The difference between WMA and EMA is that with WMA, you can assign weights based on any criteria. For example, in a 3-point moving average, you may assign a 60% weight age to the latest data point, 30% to the middle data point and 10% to the oldest data point. In EMA, a higher weight is given to the latest value and the weight keeps getting exponentially lower for earlier values. Enough of statistics lecture. Now let’s dive in and see how to calculate moving averages in Excel.

Calculating Simple Moving Average (SMA) using Data Analysis Toolpak in Excel

Microsoft Excel already has an in-built tool to calculate the simple moving averages. It’s called the Data Analysis Toolpak. Before you can use the Data Analysis toolpak, you first need to check whether you have it in the Excel ribbon or not. There is a good chance you need to take a few steps to first enable it. Click on the Data tab and check whether you see the Data Analysis option or not. If you don’t see it, follow the below steps to make it available in the ribbon. The above steps would enable the Data Analysis Toolpack and you will see this option in the Data tab now.

Suppose you have the dataset as shown below and you want to calculate the moving average of the last three intervals.

Below are the steps to use Data Analysis to calculate a simple moving average: The above steps would give you the moving average result as shown below. Note that the first two cells in column C have the result as #N/A error. This is because it’s a three-point moving average and needs at least three data points to give the first result. So the actual moving average values start after the third data point onwards. You will also notice that all this Data Analysis toolpak has done is applied an AVERAGE formula to the cells. So if you want to do this manually without the Data Analysis toolpack, you can certainly do that. There are, however, a few things that are easier to do with the data analysis toolpak. For example, if you want to get the standard error value as well as the chart of the moving average, all you need to do is check a box and it will be a part of the output.

Calculating Moving Averages (SMA, WMA, EMA) using Formulas in Excel

You can also calculate the moving averages using the AVERAGE formula. In fact, if all you need is the moving average value (and not the standard error or chart), using a formula can be a better (and faster) option than using the Data Analysis Toolpak. Also, Data analysis Toolpak only gives the Simple Moving Average (SMA), but if you want to calculate WMA or EMA, you need to rely on formulas only.

Calculating Simple Moving Average using Formulas

Suppose you have the dataset as shown below and you want to calculate the 3-point SMA:

In the cell C4, enter the following formula: Copy this formula for all the cells and it will give you the SMA for each day.

Remember: When calculating SMA using formulas, you need to make sure the references on the formula are relative. This means that the formula can be =AVERAGE(B2:B4) or =AVERAGE($B2:$B4), but it can not be =AVERAGE($B$2:$B$4) or =AVERAGE(B$2:B$4). The row number part of the reference needs to be without the dollar sign. You can read more about absolute and relative references here. Since we are calculating a 3-point Simple Moving Average (SMA), the first two cells (for the first two days) are empty and we start using the formula from the third day onwards. If you want, you can use the first two values as is, and use the SMA value from the third one onwards.

Calculating Weighted Moving Average using Formulas

For WMA, you need to know the weights that would be assigned to the values. For example, suppose you need to calculate the 3 point WMA for the below dataset, where 60% weight is given to the latest value, 30% to the one before it and 10% of the one before it.

To do this, enter the following formula in cell C4 and copy for all cells.

Since we are calculating a 3-point Weighted Moving Average (WMA), the first two cells (for the first two days) are empty and we start using the formula from the third day onwards. If you want, you can use the first two values as is, and use the WMA value from the third one onwards.

Calculating Exponential Moving Average using Formulas

Exponential Moving Average (EMA) gives higher weight to the latest value and the weights keep on getting lower exponentially for earlier values. Below is the formula to calculate the EMA for a three-point moving average: …where N would be 3 in this example (as we are calculating a three-point EMA) Suppose you have the below data set and you want to calculate the three-period EMA:

In cell C2, enter the same value as in B2. This is because there is no previous value to calculate EMA. In cell C3, enter the below formula and copy for all cells:

In this example, I have kept it simple and used the latest value and previous EMA value to calculate the current EMA. Another popular way of doing this is by first calculating the Simple Moving Average and then using it instead of the actual latest value.

Adding Moving Average Trend Line to a Column Chart

If you have a dataset and you’re creating a bar chart using it, you can also add the moving average trend line with a few clicks. Suppose you have a dataset as shown below: Below are the steps to create a bar chart using this data and adding a three-part moving average trendline to this chart: That’s it! The above steps would add a moving trendline to your column chart.

In case you want to insert more than one moving average trendline (for example one for 2 periods and one for 3 periods), repeat the steps from 5 to 8). You can use the same steps to insert a moving average trend line to a line chart as well.

Formatting the Moving Average Trend Line

Unlike a regular line chart, a moving average trend line doesn’t allow a lot of formatting. For example, if you want to highlight a specific data point on the trend line, you won’t be able to do that. A few things you can format in the trendline include:

Color of the line. You can use this to highlight one of the trendlines by making everything in the chart light in color and making the trendline pop-out with a bright color The thickness of the line  The transparency of the line

To format the moving average trendline, right-click on it and then select the Format Trendline option.

This will open the Format Trendline pane on the right. This pane as all the formatting options (in different sections – Fill & Line, Effects, and Trendline Options).

You may also like the following Excel tutorials:

What is Standard Deviation and How to Calculate it in Excel? How to Calculate Square Root in Excel Calculating Weighted Average in Excel How to Calculate Compound Annual Growth Rate (CAGR) in Excel How to Make a Bell Curve in Excel How to Add a TrendLine in Excel Charts How to Calculate Average Annual Growth Rate (AAGR) in Excel 5 Easy Ways to Calculate Running Total in Excel (Cumulative Sum)

EMA = [Latest Value – Previous EMA Value] * (2 / N+1) + Previous EMA be EMA = [Latest Value – Previous EMA Value] * (2 / (N+1)) + Previous EMA ?